Extract Date

[sislman]

Hi,

If I have a date that is formatted like this 01/01/2006, however do I write a query that shows the date as follows Jan 2006.

Thanks

Regards

 

[KenHigg]

Maybe:

Format([myDate],"mmm yyyy")

 

[sislman]

Ok thanks,

I managed to do this fine, however, when I come to sort by the new date column it will not sort in the correct order. From some reason Apr 06 is coming before Dec 05 and Jan 06?

Any ideas?

Regards

 

[KeithG]

Is the data type of the field Date/Time or Text? From the way it is sorting it seems to be a text field.

 

[KenHigg]

I think I would have a hidden column on the original date fld to sort on.

 

[sislman]

Hi,

It doesnt seem to mater what format it's put in I tried putting it in Date/Time but it still sorts in this order : Apr 06, Feb 06, Jan 06, Jul 06, Jun 06.

Regards

 

[Bodisathva]

Access is sorting the data in alpha numeric fashion in accordance with your selected mask. i.e. "Aug" comes before "Jun", NOT 8 comes before 6. The format function returns a string, so you are sorting strings, not dates. Use the Format statement to control the display of data, not to store the data.