Date formatting criteria

[0nyx175]

Hi guys,

I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:

>=#06/04/2014# And <=#05/04/2015#

The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following

Format(

.[Open Date],"yyyy-mm-dd")

The query then returns no data. Can anyone shed any light on this?

 

[BlueIshDan]

show the entire query please.

 

[plog]

Where's the criteria? under [Open Date] or under that new field you created? Hint, it should be under [Open Date]

 

[0nyx175]

SELECT Format(Table.[Open Date],"yyyy-mm-dd"), 
TABLE.Account
FROM TABLE
GROUP BY Format([Table].[Open Date],"yyyy-mm-dd"), TABLE.Account
HAVING (((Format([Table].[Open Date],"yyyy-mm-dd"))>=#4/6/2014# And (Format([Table].[Open Date],"yyyy-mm-dd"))<=#4/5/2015#));

 

[0nyx175]

I've narrowed it down. If i run criteria as

>=#06/04/2014#

it runs fine but if i run

>=#06/04/2014# And <=#05/04/2015#

as criteria it doesnt return anything. This is only when it's formatted

 

[plog]

I tried to nudge you, now here's a shove:

Don't format the date in the criteria.

 

[BlueIshDan]

Try this out.

SELECT Format([Open Date],"yyyy-mm-dd") As Open_Date, Account
FROM Table
GROUP BY [Open Date], Account
HAVING [Open Date] >= #4/6/2014# And [Open Date] <= #4/5/2015#;