Author Topic: ADO, Excel, VBA & Missing operator...  (Read 1318 times)

0 Members and 1 Guest are viewing this topic.

Tuoni

  • Gator
  • Posts: 3032
  • I do stuff, and things!
ADO, Excel, VBA & Missing operator...
« 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"

Code: [Select]
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?

Tuoni

  • Gator
  • Posts: 3032
  • I do stuff, and things!
Re: ADO, Excel, VBA & Missing operator...
« Reply #1 on: January 23, 2007, 09:52:17 AM »
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.

Atook

  • Swamp Rat
  • Posts: 941
Re: ADO, Excel, VBA & Missing operator...
« Reply #2 on: January 23, 2007, 10:46:43 AM »
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?

Tuoni

  • Gator
  • Posts: 3032
  • I do stuff, and things!
Re: ADO, Excel, VBA & Missing operator...
« Reply #3 on: January 23, 2007, 11:31:47 AM »
Code: [Select]
"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:

Code: [Select]
"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

Code: [Select]
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: