VBA & SQL Error

[psycoperl]

I am getting a Run-time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression
'(((Len(Trim(tblTempSimNetResultData.STUDENTID)))<>9)) or (((tblTempSimNetResultData.STUDENTID) Like "*" && " " && "*"))'.

Here is my SQL Code that I am trying to run. Any suggestions?

strCheckStudentIDSQL = "SELECT tblTempSimNetResultData.STUDENTID, 
tblTempSimNetResultData.SCORE, tblTempSimNetResultData.[EXAM DATE], 
tblTempSimNetResultData.[LAST NAME], tblTempSimNetResultData.[FIRST NAME] 
FROM tblTempSimNetResultData 
WHERE (((Len(Trim(tblTempSimNetResultData.STUDENTID)))<>9)) 
Or (((tblTempSimNetResultData.STUDENTID) Like ""*"" && "" "" && ""*""));"

It works in the Query SQL Editor but not when running it as part of my VBA.

 

[Erick]

I had this same problem the other day. Try renaming your table to something shorter and see if it works. It worked on mine, although not sure why. I wouldn't have thought that I was reaching the limit of the string variable. Give it a try and let us know if it works. Then maybe someone can explain why?

 

[Joe4]

Try renaming your table to something shorter and see if it works.

No need to rename the table, just use a table alias in your query. i.e.

SELECT t.Field1, t.Field2, t.Field3
FROM Table1 as t
WHERE Len(t.Field)<>9;

 

[Erick]

No need to rename the table, just use a table alias in your query. i.e.

SELECT t.Field1, t.Field2, t.Field3
FROM Table1 as t
WHERE Len(t.Field)<>9;

Thanks for that Joe4. But is there a limit to string length that you can use before you hit this problem?

 

[Joe4]

I am not sure. I imagine it may depend on how you have declared the variable you are storing the SQL string in. If it is declared as a String, it may be limited (to 255, maybe)? Off the top of my head, not sure, but it could be confirmed via some testing.

 

[revans]

My first thought was to change this

"Or (((tblTempSimNetResultData.STUDENTID) Like ""*"" && "" "" && ""*""));"

to this

"Or (((tblTempSimNetResultData.STUDENTID) Like '* *'));"

I've had issues with the double quotes resolving properly and have long since went to single quotes, especially where the statement is even somewhat complex.

hth,

Rich

 

[Joe4]

That's a good point, Rich.

When it starts getting to two to three sets of quotes, I often get confused. So I often use CHR(34) in my VBA code when I want to return "literal" quote marks.

 

[xenou]

If you're using ADO in your VBA code try using % instead of * as the wildcard operator - if it's still not working.

 

[Erick]

If you're using ADO in your VBA code try using % instead of * as the wildcard operator - if it's still not working.

At the risk of sounding like an absolute noob that I am, what's the difference between % and * ?

 

[xenou]

At the risk of sounding like an absolute noob that I am, what's the difference between % and * ?

% is the SQL Server wildcard character and, I think, standard for most databases. * is Access-specific and rather unusual. I think that ADO can trip up on this - can't remember for sure...

Access:
Like '*fish*'

SQL Server:
Like '%fish%'

Note that we can use single quotes in the SQL statements too and that spares some of the double quote problems that arise when writing VBA string literals (requiring double quotes)