SQL Statement in Access

Question

Ive been trying to get a query I ran in Access to run in VBA but I keep getting errors due to the number of exclamation marks I've been using. The statement I am using is

 SQLstat = "SELECT tbl_Date_Check.DateofChecklist, tbl_Tasks.QuestionNumber,tbl_Tasks.Frequency, tbl_Tasks.Questions " _ 
& "FROM tbl_Tasks, tbl_Date_Check " _
& "WHERE (((tbl_Date_Check.DateofChecklist)=""" & [Forms]![Daily_Checker]![TxtDate] & """) And ((tbl_Tasks.Frequency) = """ & [Forms]![Daily_Checker]![ComFreq]"""))"

Any help would be great thanks

what error does this give you? – Joseph Duty Sep 24 '15 at 17:55 — Joseph Duty, Sep 24 '15 at 17:55

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

This can possibly be explained by the following SO question: What is the difference between single and double quotes in SQL?

This explains that you need to utilize single quotes '' to surround text in SQL in almost every instance. The fact that you are using double quotes "" may be what is causing the error.

I hope this helps.

-C§

edited May 23 '17 at 11:51

Community

1
1

answered Sep 24 '15 at 18:06

CSS

412
5
17

1

The question you reference does not apply to Microsoft Access SQL. The answer in that question refers more to Microsoft SQL Server (TSQL). For Microsoft Access, either single quotes or double quotes can be used to delimit strings without any problems. His error is that he is missing a concatenation symbol at the end of the statement, as pointed out by @HansUp in his comment on the OP. He may also have the need to use the `#` date delimiter as pointed out by @Gustav if he is indeed comparing two date fields (`DateofChecklist` and `TxtDate`), but that is not known for sure based on the post. – Jericho Johnson Sep 25 '15 at 15:59

score 0 · Answer 2 · answered Sep 24 '15 at 20:18

It must read like this for dates:

SQLstat = "SELECT tbl_Date_Check.DateofChecklist, tbl_Tasks.QuestionNumber,tbl_Tasks.Frequency, tbl_Tasks.Questions " _ 
& "FROM tbl_Tasks, tbl_Date_Check " _
& "WHERE ((tbl_Date_Check.DateofChecklist = #" & Format([Forms]![Daily_Checker]![TxtDate], "yyyy\/mm\/dd") & "#) And (tbl_Tasks.Frequency = " & [Forms]![Daily_Checker]![ComFreq] & "))"

SQL Statement in Access

2 Answers2