Runtime Error 3075

[Sezzy]

I have an SQL statement embedded in VB in Access. When I try to run it it comes up with Runtime error 3075 and says syntax error (missing operator) in query expression.

I can't see what is wrong with and have tried numerous ways of writing it. Any ideas?

Thanks

Code:

Private Sub ValidAdd_Click()

Dim AddCount As Integer
Dim qdf As QueryDef

Set db = CurrentDb

Me.VAddress.Visible = False

DoCmd.Close acQuery, "Z*AutoQry - Valid Address"
DoCmd.DeleteObject acQuery, "Z*AutoQry - Valid Address"

strSQL = "SELECT [ADD 1], [ADD 2]" & _
"FROM [TEST]" & _
"WHERE [ADD 1] Not Like '*#*'" & _
"AND [ADD 1] Like '*Road'" & _
"AND [ADD 1] Not Like '*Cottage*'" & _
"AND [ADD 1] Not Like '*,*'" & _
"AND [ADD 1] Not Like '**'" & _
"AND [ADD 1] Not Like '* *'" & _
"AND [ADD 1] Not Like '*House*'" & _
"AND [ADD 1] Not Like '* * Road'" & _
"AND [ADD 2] Not Like '*#*'" & _
"OR [ADD 1] Not Like '*#*'" & _
"AND [ADD 1] Like '*Street'" & _
"AND [ADD 1] Not Like '*Cottage*' AND [ADD 1] Not Like '*,*'" & _
"AND [ADD 1] Not Like '*'*' AND [ADD 1] Not Like '* *'" & _
"AND [ADD 1] Not Like '*House*'" & _
"AND [ADD 1] Not Like '* * Street' AND [ADD 2] Not Like '*#*'" & _
"OR [ADD 1] Not Like '*#*' AND [ADD 1] LIKE '*Avenue'" & _
"AND [ADD 1] Not Like '*Cottage*' AND [ADD 1] Not Like '*,*'" & _
"AND [ADD 1] Not Like '*'*' AND [ADD 1] Not Like '* *' AND [ADD 1] Not Like '*House*'" & _
"AND [ADD 1] Not Like '* * Avenue' AND [ADD 2] Not Like '*#*' OR [ADD 1] Not Like '*#*'" & _
"AND [ADD 1] Like '*Lane' AND [ADD 1] Not Like '*Cottage*' AND [ADD 1] Not Like '*,*'" & _
"AND [ADD 1] Not Like '*'*' AND [ADD 1] Not Like '* *' AND [ADD 1] Not Like '*House*'" & _
"AND [ADD 1] Not Like '* * Lane' AND [ADD 2] Not Like '*#*';"


Set qdf = db.CreateQueryDef("Z*AutoQry - Valid Address", strSQL)

AddCount = DCount("*", "Z*AutoQry - Valid Address")

If AddCount = 0 Then
Me.Address = "All Addresses are valid"
Else
Me.Address = "Some Addresses are invalid"
Me.VAddress.Visible = True
End If

End Sub

 

[KenHigg]

Sezzy, Without having looked real close at the sql statement, I would suggest you start removing all the and/or lines one at a time and re-run it each time untill it works. Then you'll be at the offending pc of your code.

kh

 

[Fizzio]

Apart from being a longwinded query, this line (and the 2 others like it) look a bit fishy to me. I have highlighted the bit I am suspicious about:

"AND [ADD 1] Not Like '*'*' AND [ADD 1] Not Like '* *'" & _

Counting up your ', There is an odd number (I think) - obviously these must be paired. Why do you have this double wildcard separated by a ' ?

If you are not sure, debug by commenting out

Set qdf = db.CreateQueryDef("Z*AutoQry - Valid Address", strSQL)

AddCount = DCount("*", "Z*AutoQry - Valid Address")

If AddCount = 0 Then
Me.Address = "All Addresses are valid"
Else
Me.Address = "Some Addresses are invalid"
Me.VAddress.Visible = True
End If

and adding

msgbox strSQL
to see what is actually being produced as your SQL (but I suspect this may create an error as well

 

[Sezzy]

I went through each And Or line and found the one that threw the problem. It didnt like the '*'*'. So I took the middle single out. I'll just have to live without it looking for addresses with that in!

Thanks for the help

 

[KenHigg]

There should be a way to do that, just can't think of it at the moment...

Maybe someone else will post up a solution...

kh

 

[FLabrecque]

The best thing to debug it, is to use the immediate window (CTL-G) and type ?strSQL. You can then take the output... and try it in a query.

This line of code:
strSQL = "SELECT [ADD 1], [ADD 2]" & _
"FROM [TEST]" & _

Shoud be:
strSQL = "SELECT [ADD 1], [ADD 2]" & _
" FROM [TEST]" & _

Because [ADD 2]FROM doesn't mean anything to Access.