convert date field to month/year

  • Follow


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
0
Reply Utf 2/21/2008 2:56:01 PM

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


"Cam" <Cam@discussions.microsoft.com> wrote in message 
news:E02711C3-6031-47AE-A0C9-31C52DE0CB14@microsoft.com...
> 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 


0
Reply Michel 2/21/2008 3:04:54 PM

1 Replies
5172 Views

(page loaded in 0.002 seconds)


Reply: