Date format expression in table

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.

I don't think you need a date field unless you are putting in the full date
including days, then you'll really confuse the users trying to put in a
month/year. One solution would be using an input mask like 00/00, forcing
the user to enter four digits in the display. You'll need to do some sort of
code at the form entry level to convert it to a date to see if is not expired.

"Yecenia" wrote:


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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:


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.

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?

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:


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.

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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:

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?

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:


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.

Or this for the 'mm/yy' type entry --
DateSerial("20" & Right(ExpiresOn,2),Left(ExpiresOn,2)+1,0)


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:

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?

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Yecenia" wrote:


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.