Date Format Problem

[geoffcodd]

I have a table with the Field Invoice_Date as dd/mm/yyyy, I want this shown in a query as mmyyyy so I can group by the month

Invoice_Period: Format(DateAdd("d",5,[Date_TaxPoint]),"mmyyyy")

It displays it fine but in a text format which I can't sort on as 012001 come before 062000

anyone have any ideas

thanks
geoff

 

[Ally]

What about displaying the field as "yyyymm". Not so "pretty", but it works, or what about adding a year field to split the date just for sorting purposes, which you can have hidden in any query results.

Invoice_Period_Year: Format(DateAdd("d",5,[Date_TaxPoint]),"yyyy")

 

[CJBIRKIN]

Hello

I use this to sort on. It will order the dates correctly
format([date],"yyyy/mm")

Hope this helps

Chris