TheSwamp
Code Red => VB(A) => Topic started by: Tuoni on January 23, 2007, 07:49:53 AM
-
OK, before I start I am sure this is something stupid which I just can't see because I have been staring at this code for too long :lol:
Running the following code gives me the following error: "Syntax error (missing operator) in query expression"
FencingRecordSet.Open "SELECT * FROM [Fencing$] WHERE 12mmInfillBars=" & frmSettings.opt12mmInfill.Value & _
" AND 16mmInfillBars=" & frmSettings.opt16mmInfill.Value & " AND 1mHeight=" & _
frmSettings.opt1mHigh.Value & " AND 1-2mHeight=" & frmSettings.opt12mHigh.Value & _
" AND Galvanised=" & frmSettings.optGalvanised.Value, ExcelSpreadsheetADODB, adOpenKeyset, adLockOptimistic
The query is evaluating correctly (as in... it's giving TRUE and FALSE as boolean values)... the Jet front-end is reporting the columns as a boolean...
I have tried encasing the references to the radio buttons in single quotes, without single quotes, as words... I am stumped.
It's got to be something so simple yet I just can't see it. Any ideas, guys?
-
I've given up on using a filter, instead I am just going to trawl through the recordset until I find something which fulfils all the criteria.
Not as elegant as I would have liked, but needs must.
-
When I run complex queries, I like to format them more like this:
strSQL="SELECT * FROM [Fencing$] WHERE 12mmInfillBars=" & frmSettings.opt12mmInfill.Value & _" AND 16mmInfillBars=" & frmSettings.opt16mmInfill.Value & " AND 1mHeight=" & _
frmSettings.opt1mHigh.Value & " AND 1-2mHeight=" & frmSettings.opt12mHigh.Value & _
" AND Galvanised=" & frmSettings.optGalvanised.Value
FencingRecordSet.Open strSQL, ExcelSpreadsheetADODB, adOpenKeyset, adLockOptimistic
then I can evaluate strSQL on it's own for problems. So many times theres a quirky error in concatenation, or quotes as you mentioned. Could you post the actual string(strSQL) for us to look at?
-
"SELECT * FROM [Fencing$] WHERE " & _
"12mmInfillBars=" & frmSettings.opt12mmInfill.Value & _
" AND 16mmInfillBars=" & frmSettings.opt16mmInfill.Value & _
" AND 1mHeight=" & frmSettings.opt1mHigh.Value & _
" AND 1-2mHeight=" & frmSettings.opt12mHigh.Value & _
" AND Galvanised=" & frmSettings.optGalvanised.Value
Was the SQL query I was giving the ADODB connection.
The radio buttons (opt*) obviously return a boolean of TRUE or FALSE, and the fields in my db are populated with either TRUE or FALSE.
If surrounding the radiobuttons with a single quote like so:
"12mmInfillBars='" & frmSettings.opt12mmInfill.Value & "'" & _
an error was still returned, also if I used string values, either within or without single quotes.
Like I said in the previous post I have buckled and used a
recordset.movefirst
for foo=0 to recordcount -1
if etc etc
endif
recordset.movenext
next foo
On the entire table's contents. In the forseeable future (and beyond) I cannot see the rows in this table exceeding 8, so speed is not a problem here. It's just annoying :realmad: