I'm querying a table containing a date field that is defined as general date. Table contains records with dates in format dd/mm/yyyy ( which is standard for my location ) for 16/11/2024, 23/11/2024, 7/12/2024 and 14/12/2024. My sql works for 16/11/2024 and 23/11/2024, retrieving records as expected. Select for 7/12/2024 fails on the read with no records found. I think it is treating this as 12 July 2024.

I've researched use of format to explicitly define the format of the date. Built a sql select but keep getting a syntax error when it runs. My variable: Dim vFixtureDate as Date is calculated using general date field from another table.

SQL statement here.

sqlF = "select \* from fixtures where fixture_dt_short = #" & "Format(" & vFixtureDate & ", " & Chr(34) & "dd/mm/yyyy" & Chr(34) & ")#"

SQL looks good - msgbox shows a statement that looks correct with # at either end of the Format parameters. When it runs, dialog box contains "Syntax error in date in query expression. The displayed SQL statement in the dialog box contains ")'. at the end. Closing # is missing.

I'm querying a table containing a date field that is defined as general date. Table contains records with dates in format dd/mm/yyyy ( which is standard for my location ) for 16/11/2024, 23/11/2024, 7/12/2024 and 14/12/2024. My sql works for 16/11/2024 and 23/11/2024, retrieving records as expected. Select for 7/12/2024 fails on the read with no records found. I think it is treating this as 12 July 2024.

I've researched use of format to explicitly define the format of the date. Built a sql select but keep getting a syntax error when it runs. My variable: Dim vFixtureDate as Date is calculated using general date field from another table.

SQL statement here.

sqlF = "select \* from fixtures where fixture_dt_short = #" & "Format(" & vFixtureDate & ", " & Chr(34) & "dd/mm/yyyy" & Chr(34) & ")#"

SQL looks good - msgbox shows a statement that looks correct with # at either end of the Format parameters. When it runs, dialog box contains "Syntax error in date in query expression. The displayed SQL statement in the dialog box contains ")'. at the end. Closing # is missing.

• sql
• vba
• date
• ms-access
• format

Share Improve this question Follow edited Nov 20, 2024 at 15:28 taller 19.1k22 gold badges88 silver badges2323 bronze badges asked Nov 20, 2024 at 13:10 Mick KMick K 111 bronze badge 4

• 2 Please read this article from Allen Browne allenbrowne/ser-36.html and use is SQL date function( in the article). – xShen Commented Nov 20, 2024 at 13:26
• The code you posted won't work at all. Use Debug.Print to see the resulting sqlF, and add this to the question. – Andre Commented Nov 21, 2024 at 0:19
• I had a quick look at Allen's page - will explore this when I'm free from current assignment. thanks – Mick K Commented Nov 21, 2024 at 11:55
• Simple solution in Allen's article. did the job. – Mick K Commented Dec 8, 2024 at 9:43

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Use the ISO sequence and reduce it to:

sqlF = "select * from fixtures where fixture_dt_short = #" & Format(vFixtureDate, "yyyy\/mm\/dd/") & "#"

Make an independent Date format

In MS Access table data stored in date field can be retrieved in any format. The question is how we are using the date format. Some use month in left some use it in middle of the format with two digits numeric value. To make it independent make it 3-Alpha short month name(Jan, Feb, Mar...Dec). for example

Format(vFixtureDate, "Mmm-DD-YYYY")
or
Format(vFixtureDate, "DD-Mmm-YYYY")

Value from above (Nov-20-2024 or 20-Nov-2024) will give you the same resultset in SQL statement. Finally we can write

sqlF = "SELECT * FROM fixtures  WHERE fixture_dt_short = #" & Format(vFixtureDate, "DD-Mmm-YYYY") & "#"
or
sqlF = "SELECT * FROM fixtures  WHERE fixture_dt_short = #" & Format(vFixtureDate, "Mmm-DD-YYYY") & "#"

Implemented this code on MS Access 2016.

Thanks