Date Format

[Moxioron]

Hello all.

I have a date that I import that is formatted YYYYMMDD. So I created a query with this SQL:

Time Period: Left([Time Period Code],4) & "/" & Mid([Time Period Code],5,2) & "/" & Right([Time Period Code],2)

Then I used Date Formatted: Format(CDate([Time Period]),"mm/dd/yyyy") and the date now appears as MM/DD/YYYY like I want it to.

However, when I use Between[Start Date]AND[End Date] in the criteria I get the message "You tried to execute a query that does not include the specific expression ...... as part of an aggregate function".

I need to be able to query by date range. Any suggestions? Thanks.

 

[SydneyGeek]

That's nothing to do with the date format; sounds like you have a totals query and the date expression is not set up as Group By.

Denis

 

[Moxioron]

Even by changing it to a Group By .... I don't get the desired results.

When you have a date that was obtained from an expression, how does one query between two dates? Thanks.

 

[SydneyGeek]

You will most likely need to use the whole expression again, in the new calculation. Access generally doesn't store those intermediate results.

Denis