Solved: Missing operator

[Trevor]

This query wokes in the Query graphical design , but when I copied it over to my form I get missing operator error. it is supose to delete from PswrdAttempted table were [Date] is > Date() Or [Date] = Date() And [LockedoutTime] < Time()


DoCmd.RunSQL "DELETE PSwrdAttemptTbl.Date, PSwrdAttemptTbl.LockedOutTime" _
& "From PSwrdAttemptTbl WHERE (((PSwrdAttemptTbl.Date)>Date())) OR" _
& "(((PSwrdAttemptTbl.Date)=Date()) AND ((PSwrdAttemptTbl.LockedOutTime)<Time()));"

[akn112]

DoCmd.RunSQL "DELETE PSwrdAttemptTbl.Date,PSwrdAttemptTbl.LockedOutTime " & _


"From PSwrdAttemptTbl WHERE " & _

"PSwrdAttemptTbl.Date > Date() OR " & _
"(PSwrdAttemptTbl.Date = Date() AND PSwrdAttemptTbl.LockedOutTime < Time());"


Try this. copying and pasting directly from the SQL generated from Access often leaves messy code which requires some cleanup. cleaning it up usually reveals the problem. not sure if this will solve it, but there were a number of times you did not have a space at the end of a quote. Example:


msgbox "this is my" & _
"msgbox"
'will produce this is mymsgbox when a space is expected in between my
'and msgbox


Edit:
For some reason there the message keeps adding in line breaks in my code above. Please ignore them as im not sure how to get rid of them:dunno

[Norie]

Trevor

Well for start you are appear to be missing a space or two.

And secondly I would recommend you build the SQL like this and then run it.

Dim strSQL
strSQL = "DELETE PSwrdAttemptTbl.Date, PSwrdAttemptTbl.LockedOutTime "
strSQL = strSQL & "FROM PSwrdAttemptTbl "
strSQL = strSQL & "WHERE(((PSwrdAttemptTbl.Date) > Date)) Or ((PSwrdAttemptTbl.Date)=Date()) AND ((PSwrdAttemptTbl.LockedOutTime)<Time()));"

DoCmd.RunSQL strSQL
PS I haven't tested this and there might be problems with parentheses and/or quotes but hopefully you'll get the idea.

Constructing SQL like this makes it far easier to debug.

[FrymanTCU]

Norie, that is an interesting way of building the SQL. Does the code run faster like that or is it just an organization preference?

Trevor, I really can't tell where your missing the operator, I too have been looking at code all afternoon. I wonder if there is a add-in for VB that highlights the parenthesis like in Excel or even better links the If and Loop statements. Sorry to take the thread in a different direction but a tool like this may prevent these types of errors in the future.

[Norie]

FrymanTCU

Don't know if it's any faster.:huh:

But I do know from experience it makes debugging a lot easier.:)

[FrymanTCU]

Norie, good point. I just changed a Insert Into query I posted to reflect that syntax. I am not having a debugging error but a reference error.

[Trevor]

Thanks Norie, but the compiler didn't like your strsql concationation( kept giving me "an extra" error, or the DoCmd.runsql Strsql (where I put guotes and or perenthises around strsql
So I had to stay with Akn112's post, thanks