Syntax Error - Unknown cause, can anyone help?

[rosdancer]

When I run the following code I get this error Syntax error (missing operator) in query expression 'validation='countryCode' AND'.

Code:
strSql = "SELECT * FROM ValidationList WHERE validation=" & SQLText("countryCode&quot & " AND order=" & SQLNumber("1&quot
countryCodes.Open strSql, gConnection, adOpenStatic, adLockOptimistic


I'm not sure what the problem is and therefore I'm not sure how to change this code.

Thanks in advance for the help.

Roslyn

 

[xlbo]

Think this is about " & maybe '

Probably need more than 1 set of brackets to enclose your string - """ should create a " within the string itself

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[xlbo]

must be late - by brackets I meant quotation marks (easily confused ;-) )

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[rosdancer]

I tried that also tried single quotes, both still came up withthe same error message.

Any other suggestions.

Roslyn

 

[Zathras]

What is "countryCodes"?

What version of Access are you using?

It might help if you posted more of your code.

 

[rosdancer]

countryCodes is the value in the field validation.

I am running access2000

I'm not at my computer right now but I'll post more tonight if you could take a look then, i would really appricate it.

Thanks Roslyn

 

[Zathras]

...countryCodes is the value in the field validation...

Then I don't understand how you can apply the .Open method to it. On the other hand, if you are talking about "countryCode" then perhaps you need to remove the quotes from

 ...SQLText("countryCode")...

(And my question remains, what is "countryCodes"?)

I don't have 2000 here, only 97. I can try things tonight at home where I do have 2000.

Meanwhile, it would help if you could publish a little more info on what the data looks like.

Another question might be, why do you pick names that are so similar as to make the code hard to read?

 

[rosdancer]

Hi Again

Here is my code, countryCode is a the text in a field in access now called fldvalidSection

The code has changed a bit from before, trying to fix bug.

Thanks
roslyn

Private Sub Form_Load()
lngCurrentID = 1
strSql = "SELECT * FROM ValidationList WHERE fldvalidSection=" & SQLText("countryCode&quot & " AND order=" & SQLNumber("1&quot
countryCodes.Open strSql, gConnection, adOpenStatic, adLockOptimistic
lockCountryCode
If countryCodes.BOF Or countryCodes.EOF Then
MsgBox "There are no countries please add one.", vbOKOnly, "Country Maintenance - VisualToPowership"
txtCountry = ""
txtDescription = ""
countryCodes.Close
unlockCountryCode
Else
countryCodes.Close
displayRecord
End If
End Sub

 

[TonyJollans]

Hi roslyn,

Quotes, quotes, quotes!! They cause no end of problems

The SQL string you are building must have names (Tables, Columns, Functions) UNquoted and literal strings QUOTED. At the same time you must use quotes to tell VBA what to do. If you are struggling, I suggest you experiment and put a Msgbox after building the string to see what you have built.

To get a quote inside a string you must, as Geoff said in the first place, put two of them in the string build, so """" gives a string, length 1, value ".

Currently you are building the following SQL (where the values in Red are whatever is returned from your VBA functions SQLText and SQLNumber ..

SELECT * FROM ValidationList WHERE fldvalidSection=

CountryCode

 AND order=

1

.. and that isn't what you want. It is unclear exactly what you do want so, as I say, experiment and if you're totally stuck can you post what you do want the resulting SQL to be and someone here will give you VBA to generate it.

After all this, of course, we still don't have an answer to Zathras' question - what is Countrycodes? It may not be an issue but we don't know that because we have no information about it.

Enjoy,
Tony



Enjoy,
Tony