VB Syntax Errors 1

[jt643]

I have a table with a field that is a checkbox control - 'Order'. When the user finishes their selections, the master selections table will be queried in VB to extract all records that have been checked to order and will compile them into a 'FinalSelections' table.

I can not get past the code I am trying to pull back the ordered selections with:

Set SelTotalTableDb = CurrentDb
Set SelTotalTableRst = SelTotalTableDb.OpenRecordset("select * from COSelTotalTable WHERE Subdivision = '" & TempRst!Subdivision & "' AND ORDER = TRUE")

The CAPITALIZED code is what is causing me headaches.

This is the error message I get:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Subdivision = 'Test-71205' AND'.

Thanks so much for any help you can provide as this thing has got me stymied!

 

[alvechurchdata]

Debugging would be easier if you split this line into two:

[TT]
strSQL="select * from COSelTotalTable WHERE Subdivision = '" & TempRst!Subdivision & "' AND ORDER = TRUE"
Set SelTotalTableRst = SelTotalTableDb.OpenRecordset(strSQL)
[/TT]

You can check the value of the SQL statement before trying to execute it.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[jt643]

Thanks Geoff.

I inserted your suggested code and got the same results.

This does not make any sense to me, but I think it may be something simple.

If I leave out the 'Order = True' part, everything works like a charm, but when I put it back in there [which is the crucial part of my criteria], I get the Run-Time error.

 

[earthandfire]

Is Order a Boolean (Yes/No) field?

You could also try using brackets:


strSQL="select * from COSelTotalTable WHERE (Subdivision = '" & TempRst!Subdivision & ")' AND (ORDER = TRUE)"


Hope this helps.

 

[PHV]

ORDER is reserved word.
& "' AND [ORDER] = TRUE"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

Slight mistake -

should have been:

strSQL="select * from COSelTotalTable WHERE (Subdivision = '" & TempRst!Subdivision & "') AND (ORDER = TRUE)"

 

[jt643]

Thanks earthandfire.

I tried your suggested code.

I actually got a different error on that one:

Run-time error '3075':
Missing ),], or Item in query expression '(Subdivision = '" & TempRst!Subdivision & ")' AND (ORDER = TRUE)'.

What I find interesting is that VB doesn't seem to even recognize the 'Order = True' as being a valid argument. Why I say that is because it single quotes the 'Subdivision' argument but after the 'AND', it doesn't recognize that argument, which is very similar to what I ran in to with the previous way I coded it.

 

[PHV]

jt643, have you tried my suggestion dated 1 Sep 05 13:37 ?

 

[jt643]

Thanks to both of you. What a nightmare this has been.

I bracketed [] Order, and it worked like a charm.

Thanks for taking the time to help me out!