mm/yy date format

[shankbanta]

I must have set this up incorrectly. I have a date field that has a format of mm/yy and an input mask of 99/00;0;_ I only want an entry of the month and two digit year. When the information is entered, ex. 05/02, the table is updated with 05/02/2003. How can I fix this?

 

[cheerio]

The input mask is not relevant.

If you have a date/time field you must supply a valid date/time. If you supply something that is short such as 01/02 then Access will try to complete is by adding the current year to give 01/02/2003. So far as I am aware, there is no option to complete by adding a day of say 01 at the start (to give 01/01/02 say).

The only way you could do this would be to collect the month/year from the user in a form text field and then convert that to a date.

 

[BoxOfMoonlight]

That sounds great. How can I convert text of 12/02 [StDate] to recognize the year 2002 or 02 without Access putting the current year on the end, like 2005 [SYear] when I really need 2002.

SELECT StDate, DateSerial(Year([StDate]),Month([StDate]),Day([StDate])) AS SSerDate, Format([StDate],"mm""/1/""yyyy") AS S, Right([StDate],2) AS SY, (Year([SY])) AS SYear;

Thanks,
Amy

 

[earthandfire]

Try

DateSerial(Right([StDate], 2),Left([StDate], 1),1)

where [StDate] simply = "12/02"

Hope this helps

 

[BoxOfMoonlight]

I modified your statement just a bit and it works like a charm!!

DateSerial(Right([StDate],2),Left([StDate],2),1)

Thanks so much,
Amy