MS Access Compile Error

[B00592816]

I am a newbie to MS Access and vba coding. When running a module a Microsoft Visual Basic message box appears telling me Compile error: Syntax Error.



Can someone please help me with the following piece of code that I have written and tell me what is wrong with it?

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
& "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND " _
& "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND ApptLocation = "RSU1" & ORDER BY ApptStart") 'fetch appts for selected day

[RuralGuy]

One way to pin it down is to put your SELECT in a string variable first and then display it either with a MSGBOX or just with debug.

[B00592816]

Thanks for your help. I removed the AND ApptLocation = "RSU1" and the code it seems to work however I need this information in my code. Have I written that specific part properly?

[JoeM]

It looks you don't quite have your double-quotes correct at the end of your statement, specifically, this part:

 & "# & AND ApptLocation = "RSU1" & ORDER BY ApptStart"

Note that to get literal double-quotes in the statement itself, you cannot do it that way. People often double/triple up the double-quotes to get them to appear. I find that kind of messy, and choose to use Chr(34) instead, which returns literal double quotes, i.e.

... "# & AND ApptLocation = " & Chr(34) & "RSU1" & Chr(34) & ...

Note, here is a little trick you may want to use. Create your SQL statement as a string. Then you can use a MsgBox to inspect it before you try to run it to easily check for errors, i.e.

Dim strSQL as String
strSQL = "SELECT * FROM ..."

'Temporary message box to inspect building of SQL string
MsgBox strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)

Edit: I see ruralguy posted the same MsgBox suggestion too. That's what happens when one is a real slow typist and people respond in the time it takes me to type!

[lfpm062010]

First guess,

.
.
& "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND ApptLocation = ""RSU1"" & ORDER BY ApptStart") 'fetch appts for selected day

[B00592816]

I tried the above code and I am told there is a syntax error (missing operator) in query expression.

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
& "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND " _
& "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND ApptLocation = ""RSU1"" & ORDER BY ApptStart") 'fetch appts for selected day

[lfpm062010]

I broke the code into manageable piece.

Dim tmpStr As String

tmpStr = "SELECT * FROM tblAppointments WHERE "
tmpStr = tmpStr & "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND "
tmpStr = tmpStr & "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND "
tmpStr = tmpStr & "ApptLocation = ""RSU1""" & " ORDER BY ApptStart"

[JoeM]

Did you try the MsgBox suggestions RuralGuy and I proposed to see if your syntax looks correct after building it?

[B00592816]

Can anyone identify what the missing operator is?

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE " _
& "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND " _
& "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND ApptLocation = ""RSU1"" & ORDER BY ApptStart") 'fetch appts for selected day

[lfpm062010]

"ApptLocation = ""RSU1""" & " ORDER BY ApptStart"

& "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND ApptLocation = ""RSU1""" & " ORDER BY ApptStart") 'fetch appts for selected day

or you can just substitue what I have in post #7

Dim tmpStr As String

tmpStr = "SELECT * FROM tblAppointments WHERE "
tmpStr = tmpStr & "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND "
tmpStr = tmpStr & "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND "
tmpStr = tmpStr & "ApptLocation = ""RSU1""" & " ORDER BY ApptStart"

Set rst = CurrentDb.OpenRecordset(tmpStr)

[B00592816]

I tried substituting what you had in post#7 and I am still being told that there is a syntax error (missing operator) in query expression.

[lfpm062010]

Sorry, my mistake on this string
tmpStr = tmpStr & "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# & AND "

Correction:

tmpStr = "SELECT * FROM tblAppointments WHERE "
tmpStr = tmpStr & "DateValue(ApptStart) <= #" & Format(vDate, "yyyy/m/d") & "# AND "
tmpStr = tmpStr & "DateValue(ApptEnd) >= #" & Format(vDate, "yyyy/m/d") & "# AND "
tmpStr = tmpStr & "ApptLocation = ""RSU1""" & " ORDER BY ApptStart"

If you use MsgBox on the tmpStr, it should return
SELECT * FROM tblAppointments WHERE DateValue(ApptStart) <= #2014/6/28# AND DateValue(ApptEnd) >= #2014/6/28# AND ApptLocation = "RSU1" ORDER BY ApptStart

If the select looks right, then the code should work.

[B00592816]

That did the trick, thank you. I will mark the thread as solved.