Syntax error when including query in code

Question

I think I've got a syntax error with my query I cant find the error. I get the error at the "1/1/1990". Anyone know hat i'm doing wrong here?

  Set rsCanCounter = db.OpenRecordset("Select * from tblActionLog Where groupNum = '" & txtGroupNr.Value & "' And DateClosed < '" & 1/1/1900, dbOpenDynaset)

The syntax highlighting in the question shows mis-matched quotes. Pay close attention to your single-quotes and double-quotes. This query is never even going to reach the database because the code won't compile like this. — David
– David, Commented Jan 29, 2015 at 18:40
Sorry! I was trying to fix it the way David suggested. The quotes are confusing me. — SaladSnake
– SaladSnake, Commented Jan 29, 2015 at 18:49

HansUp · Accepted Answer · 2015-01-29 18:56:53Z

2

Use a string variable to hold your SELECT statement. After you overcome the quoting issues so you can create a valid string in VBA, then use that variable with OpenRecordset.

For example, if the datatypes of your groupNum and DateClosed fields are both text ...

Dim strSelect As String
strSelect = "Select * from tblActionLog Where groupNum ='" & _
    Me.txtGroupNr.Value & "' And DateClosed < '1/1/1900'"
Debug.Print strSelect '<-- Ctrl+g to go to Immediate window and see the statement text
Set rsCanCounter = db.OpenRecordset(strSelect, dbOpenDynaset)

If DateClosed is Date/Time datatype, use # instead of quotes to delimit the date value:

strSelect = "Select * from tblActionLog Where groupNum ='" & _
    Me.txtGroupNr.Value & "' And DateClosed < #1900-1-1#"

edited Jan 29, 2015 at 18:56

answered Jan 29, 2015 at 18:51

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SaladSnake Over a year ago

Thanks for the help! "Select * from tblActionLog Where groupNum ='" & Me.txtGroupNr.Value & "' And DateClosed < #'1-1-1900# And DateClosed is not Null" I had to add the null exception. is this correct. Am I doing the quotations wrong again

HansUp Over a year ago

What? DateClosed can not be both < 1/1/1900 and Null at the same time. Those conditions contradict each other. That WHERE clause should not return any rows.

SaladSnake Over a year ago

Sorry I fix it. Actually the comment got added before I was finished with it : /

HansUp Over a year ago

Aha! :-) Well, with the condition DateClosed < #'1-1-1900#, that makes DateClosed is not Null superfluous. To see if the latest version of the statement is correct, run your code, go to the Immediate window (Ctrl+g), copy the statement text, open a new query in the query designer, switch it to SQL View, paste in the statement text. Does it work when you then try to run it from the query designer?

Collectives™ on Stack Overflow

Syntax error when including query in code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related