Date format expression in table

  • Thread starter Thread starter Yecenia
  • Start date Start date
Y

Yecenia

I need to add credit card data in my table and I need a date field to
collect and show data as "mm"/"yy".

I have tried different approaches and none worked. Please help.

Thanks.
 
All date fields need a value for the day. Credit cards usually expire on the
last day of the month, so you coould force the day to the last day of the
month. Also, date fields are not formatted at the table level. You can use
the Format function to display them how ever you want, but the data will
still be stored as a number.
In this case, it may be better to use a text field to store the data.
 
If I used text I will not be able to apply logic when reporting which credit
cards are due to expire.

It sounds like I will need to change the expression on the form and figure
out the formula to make the day default to the 31st.

I would not know where to begin to figure out a formula like that.

Can anyone help with this expression?
 
Here is how you can turn the text into a date.

DateSerial(Right(ExpiresOn,4),Left(ExpiresOn,2)+1,0)

Where ExipresOn is like "03/2011"
It will return the date as 3/31/2011
It is also important you validate the data entry and be sure the user is
putting it in in the format of mm/yyyy otherwise, the above code will not
work.
 
Or this for the 'mm/yy' type entry --
DateSerial("20" & Right(ExpiresOn,2),Left(ExpiresOn,2)+1,0)
 
Back
Top