Hey everyone,

VBA does not seem to understand the SQL statement regarding the formatting of the date.

DoCmd.RunSQL ("SELECT (Format([date],"Short Date")) AS Expr1, Avg(tblSiteA.temp) AS AvgOftemp ...

The command will not run with the date formatted as shown above ("Short Date")...any suggestions?

Question: What did you want to do with the dataset?

Are you planning to use this date in an exported spreadsheet or an Access report? If so, defer the formatting to the final product and just pull the dates as-is.

Or, try this:

Pre-build your query using dates unformatted. But then, before you actually save the query, switch from SQL view to Design view. Right-click on the column showing the date field you wanted to see. Select properties. On of the properties is Format. Now click in that. You will get a drop-down, one entry of which is Short Date.

Then, instead of opening an SQL string, open the preformatted query, possibly with further criteria. This does the formatting outside of your VBA code.

[This message has been edited by The_Doc_Man (edited 05-29-2002).]

To The_Doc_Man:

Thanks for the input, I used your idea of formatting the date at a different time and now the VBA code works great! Thanks.