Question Reading a mm/yy date input properly instead of mm/dd

[redbe4rd]

Hi all,

I have a quick (hopefully easy) problem for you today. I have an options screen for a report in my current Access project that asks the user for a starting date and a duration. My starting date field is to be inputted as "mm/yy" (ex. 11/12) for ease of use.

When reading this as a date, however, I found it was transferring the data in "mm/dd" format, not "mm/yy". To work around this, I was formatting the string to retrieve the month ("mm") and year ("dd") and "adding" a day value of "/01/" to it.

ex: newDate = Format(oldDate, "mm") & "/01/" & Format(oldDate, "dd")

Though this was an extremely clunky solution, all was well until I tried to run a report for previous years. "11/00 (November 2000)" reads as "11/01" in my current scheme, because "00" is obviously an invalid day.

Long story short, I need to know if there is a way of reading an input of "mm/yy" AS "mm/yy" instead of "mm/dd".

Thanks in advance.

 

[boblarson]

1. Dates are stored with the day.
2. If you have month/year as strings you should probably store them as separate fields.
3. You can't use Format to pull month and year from a mm/yy format. So you could use DateSerial

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

if you wanted.