Syntax Error

[houseofturner]

I am obviously being really stupid by the query below compiles ok but when I run it I get a syntax error. Its a long query so it is split up with " & _

Any help would be appreciated....

DoCmd.RunSQL ("SELECT [Bounce Backs To Process - Imported].ID, [Bounce Backs To Process - Imported].Contents AS Notes, Right(Left([Bounce Backs To Process - Imported]![Contents],InStr(1,[Bounce Backs To Process - Imported]![Contents],""@"",10)),Len(Left([Bounce Backs To Process - Imported]![Contents],InStr(1,[Bounce Backs To Process - Imported]![Contents],""@"",10)))-InStrRev(Left([Bounce Backs To Process - Imported]![Contents],InStr(1,[Bounce Backs To Process - Imported]![Contents],""@"",10)),"" "")) & Left(Mid([Bounce Backs To Process - Imported]![Contents],InStr(1,[Bounce Backs To Process - Imported]![Contents],""@"",100)+1),InStr(Mid([Bounce Backs To Process - Imported]![Contents],InStr(1,[Bounce Backs To Process - Imported]![Contents],""@"",100)+1),"" "")) AS Email " & _
"FROM [Bounce Backs To Process - Imported] " & _
"WHERE (((Bounce Backs To Process - Imported.Contents) Like ""*not able to deliver*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*not been delivered*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*permanent error*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*could not be delivered*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*failed*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*Will not retry*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*not recognized*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*unrecoverable error*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*undeliverable*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*no user by that*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*I have now left*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*incorrectly addressed*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*Sorry it didn't work out*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*permanent fatal*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*did not reach*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*doesn't exist*"" " & _
"Or (Bounce Backs To Process - Imported.Contents) Like ""*no such user*""));")

 

[Brianwarnock]

You shouldn't double post ity annoys the helpers.

I don't think you need both a continuation _ and a concatenation &, I'd try just the _ then the & if that fails.

Brian

 

[houseofturner]

Many thanks and my apologies for the breach in protocol

 

[namliam]

You do need the & and the _, there is just a limit on how many continations you can have.

Try making your code more maintainable by building the query thusly:
Dim MySQL as string
mySQL = ""
mySQL = mySQL & " SELECT .. "
mySQL = mySQL & " .. "
mySQL = mySQL & " From .. "
mySQL = mySQL & " where .. "
etc

Also notice the table name in your where your lacking []
"FROM [Bounce Backs To Process - Imported] " & _
"WHERE ((([Bounce Backs To Process - Imported].Contents) Like ""*not able to deliver*"" " & _
Your lacking this everywhere in your where...

Also... I will spare you the speach of to long convoluted table names as well as the one about not using spaces in object names and the over use of Like statements

 

[Brianwarnock]

You do need the & and the _, there is just a limit on how many continations you can have.

Try making your code more maintainable by building the query thusly:
Dim MySQL as string
mySQL = ""
mySQL = mySQL & " SELECT .. "
mySQL = mySQL & " .. "
mySQL = mySQL & " From .. "
mySQL = mySQL & " where .. "
etc

Also notice the table name in your where your lacking []
"FROM [Bounce Backs To Process - Imported] " & _
"WHERE ((([Bounce Backs To Process - Imported].Contents) Like ""*not able to deliver*"" " & _
Your lacking this everywhere in your where...

Also... I will spare you the speach of to long convoluted table names as well as the one about not using spaces in object names and the over use of Like statements

OK I believe you Namliam, I always built mine per your suggestion but thought that a straight continuation wouldn't need the &, didn't do much of this so not really my thing, should have kept stum.

Brian

 

[raskew]

Hi -

I'd be curious if a version of:

strSql = strSQL & " " & "Where Instr(""could not be delivered,not able to deliver,not been delivered,permanent error,failed,Will not retry, not recognized,unrecoverable error,undeliverable,no user by that,I have now left,incorrectly addressed,Sorry it didn't work out,permanent fatal,did not reach,doesn't exist,no such user"", [Bounce Backs To Process - Imported].Contents)>0"

...would work. It eliminates the constant repetition of the table/field/'like' statements. I use this frequently where the choices are far fewer.

Best wishes - Bob

 

[RossWindows]

Also, I believe you are missing some closing parentheses. Don't have time to find exactly where, but by my count, you're either missing some closing parentheses or you just have them in the wrong place.

 

[namliam]

Hi -

I'd be curious if a version of:
...would work. It eliminates the constant repetition of the table/field/'like' statements. I use this frequently where the choices are far fewer.

Best wishes - Bob

No it wouldnt, but maybe somthing like:

strSql = strSQL & " " & "Where Instr(""could not be delivered"", ...) + Instr(""not able to deliver"", ...) + Instr(...,...) + Instr...
+.... >0"

Would work but that his just as bad as all the like's, perfomance wize.

if this is like a one-off thing, performance is not really an issue...
If your repeating this process with the same records over and over, then makeing a related table 1:n with the types of responses would considerably increase performance.

p.s. sorry for the late reply been on holidays

 

[DCrake]

You cannot run a DoCmd RunSQL on a Select query

You do not need to repeat the table name continually if the query is only using one source.

You may have to change the double quotes to Chr(34) to contend with the single quotes in the arguments.

David