Show Month and Year only

[Guest]

I have a date field in my query. I need to create a new field in the query
that extracts the month and year only. I can create a new column in the
query and change the property format to mmm/yy, which looks good and sorts
correctly, but why when I click on a date in the query results, it shows the
original short date? Is this correct or is there a formula I can use
instead? If I use Format([datefield]) it looks correct but sorts as text.
Thanks for your help.

 

[Ruskin Hardie]

Have you tried using the 'DatePart' function?
eg:
myMonth: DatePart("m",[DateField])

 

[John Spencer]

When you use the format field property of the query grid, then your date
will be DISPLAYED with the specified format, but it is still the entire
date. That is why when you enter the grid cell in the datasheet view it
shows the actual STORED value.

If you need the actual string then you need to use the format function as
you did. In addition, to get the sorting you want, you will need an
additional copy of the DateField that you sort by, but don't display.

 

[Van T. Dinh]

There are VBA inbuilt functions Month() and Year() than returns the month
and the year of a given date input value.

Check Access VB Help on these functions.