convert date field to month/year

[Cam]

Hello,

I have a table with a date field. In query, I would like to convert the date
field like mm/dd/yyyy to just the year and month. what function do I put to
achieve this?

PS. if the format is yy-mmm (08-APR, 08-JAN), when I export the data to
Excel, I need it to recognize the date so when it sort, the 08-JAN goes
before 08-APR. Thanks

 

[Michel Walsh]

Then you need the day, 1, for the day, if you want to SORT on real dates.


DateValue(myDate+1 -DatePart("d", myDate))


transform the date, pushing it to the first day of the same month, same
year.


For your report, you can FORMAT it with yy-mmm:


? Format( now, "yy-mmm" )
08-Feb

? DateValue(now +1 -DatePart("d", now))
2008.02.01 ' if you type the previous expression while in the month
of February 2008
' my default format, for date, is the ISO double
dot format: yyyy.mm.dd




Vanderghast, Access MVP