Comparison operator Syntax

[panteraboy]

Hi there again folks. Ps thanks for all the help gettin me this far. I get an 3075 syntax error (missing operator) in the following code of the click event. The code worked fine before i added the harddrive criteria. Is there something wrong with this bit of code or should i be taking a different approach. The idea is that if the hardrive is low spec it will return all the records smaller than 120 in the database

 "AND laptops.hard_drive >=" & harddrive & _
 

 
Private Sub CmdSubmit_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim operatingsystem As String
Dim make As String
Dim computertype As String
Dim bluetooth As String
Dim harddrive As Integer
 
 
 
Set db = CurrentDb
Set qdf = db.QueryDefs("Admin_query")
 
If (Me.CboOS.Value = "All") Then
     operatingsystem = " Like '*' "
Else
 operatingsystem = "='" & Me.CboOS.Value & "' "
 
End If
 
If (Me.CboMake.Value = "All") Then
make = " Like '*' "
Else
make = "='" & Me.CboMake.Value & "' "
 
End If
 
If (Me.CboComputerType.Value = "All") Then
computertype = " Like '*' "
Else
computertype = "='" & Me.CboComputerType.Value & "' "
 
End If
 
If (Me.CboBluetooth.Value = "All") Then
bluetooth = " Like '*' "
Else
bluetooth = "='" & Me.CboBluetooth.Value & "' "
End If
 
 
If (Me.CboStorage.Value = "Low Spec") Then
 
    harddrive = 120
 
ElseIf (Me.CboStorage.Value = "Normal Spec") Then
 harddrive = 180
Else
harddrive = 300
 
End If
 
 
         strSQL = "SELECT laptops.* " & _
         "FROM laptops " & _
         "WHERE laptops.operating_sysytem" & operatingsystem & _
         "AND laptops.manufacturer" & make & _
         "AND laptops.bluetooth" & bluetooth & _
         "AND laptops.ComputerType" & computertype & _
         "AND laptops.hard_drive >=" & harddrive & _
         "ORDER BY laptops.model;"
         qdf.SQL = strSQL
         Dim msg As String
         msg = "Sorry there are no models in stock with that specification"
 
 
       If IsNull(DLookup("model", "admin_query", "product_id")) Then
         MsgBox msg
 
        Set qdf = Nothing
        Set db = Nothing
 
        Exit Sub
 
        Else
 
 
         DoCmd.Close acForm, Me.Name
 
         Set qdf = Nothing
         Set db = Nothing
         DoCmd.OpenForm "laptop_specs", , "Admin_query"
 
        End If
 
End Sub
 

regards panteraboy

[patjones]

Hi panteraboy:

Do you get this error message at compile time, or during execution?

I put your code into a module in my db, inserted a Debug.Print strSQL right after you defined the SQL statement, compiled it, and stepped through it (I hardcoded your query values). The SQL statement outputted in the Immediate Window as follows:

 
SELECT laptops.* FROM laptops WHERE laptops.operating_sysytem='Windows XP' AND laptops.manufacturer='Dell' AND laptops.bluetooth='Not a bluetooth!' AND laptops.ComputerType='Optiplex GX220' AND laptops.hard_drive >=120ORDER BY laptops.model;
 
 

This seems to look OK to me, except that there is no spacer in front of the ORDER BY keyword. Not sure if that would make a difference...

Pat

[panteraboy]

Hi Pat, yes it happens at run time right after i click on the submit button.
Its sort of been doin my head in all day lol. Can think what it is im doin wrong. . Its saying missing operator in query expression but surely if there was a missing operator the SQL code would be highlighted in red in the Code window. Its a strange One but thanks for the help anyway
Regards panteraboy

[patjones]

Hi Pat, yes it happens at run time right after i click on the submit button.
Its sort of been doin my head in all day lol. Can think what it is im doin wrong. . Its saying missing operator in query expression but surely if there was a missing operator the SQL code would be highlighted in red in the Code window. Its a strange One but thanks for the help anyway
Regards panteraboy

Well, in my own experience, whenever there's an issue with the syntax of the SQL code itself, it doesn't stop at strSQL (because strSQL is merely a string definition - you can put anything you want in a string). Rather, it will stop at the point where the SQL statement is actually executed.

If putting a space in front of ORDER BY isn't working, so that you have ... >= 120 ORDER BY instead of >=120ORDER BY (I've set harddrive =120), not sure what it could be...

Pat

[panteraboy]

I already put a space in front of it but it dosent filter the hardrives stored in the database correctly so maybe ive more coding to do than i expected. Thanks anyway Pat.
Regards Paul (aka Panteraboy)

[NeoPa]

Paul, if you've fixed the bug Pat has already pointed out, how about you post the SQL you now get when print it before executing it. If you get an error message that would be helpful too.

[panteraboy]

OK Neopa

sql code of click event

.
    strSQL = "SELECT laptops.* " & _
         "FROM laptops " & _
         "WHERE laptops.operating_sysytem" & operatingsystem & _
         "AND laptops.manufacturer" & make & _
         "AND laptops.bluetooth" & bluetooth & _
         "AND laptops.ComputerType" & computertype & _
         "AND laptops.hard_drive<=" & harddrive & _
         " ORDER BY laptops.model;"   ' NOTICE SPACE BEFORE ORDER
         qdf.SQL = strSQL
         Dim msg As String
         msg = "Sorry there are no models in stock with that specification"

this code runs ok but seems to take in all the hard drives in the database. i.e will show 250 GB computer in the low spec criteria.

With the code previously posted the following error msg comes up when I Click the submit button after chosing low spec for hard drive type and "All" to the rest of the combo boxes.

Run -time Error '3075'

Syntax error (missing operator) in query expression
'laptops.operating_sysytem Like '*' AND laptops.manufacturer Like'*'
AND laptops.bluetooth Like '*' AND laptops.ComputerType Like '*' AND

laptops.hard_drive<=120ORDER BY laptops.model'

Ps how do you you insert an image here was going to insert picture of the error message dialogue box but couldnt.

Regards
Paul

[NeoPa]

Paul, if you've fixed the bug Pat has already pointed out, how about you post the SQL you now get when print it before executing it. If you get an error message that would be helpful too.

Paul, I can see the problem with your SQL.

Unless you "post the SQL you now get when [you] print it before executing it", you won't see the problem.

This is a very useful technique to use when debugging SQL that's created in your VBA code. It's much easier to understand when it's sitting there in front of you.

[NeoPa]

...
Ps how do you you insert an image here was going to insert picture of the error message dialogue box but couldnt.
...

You can only attach images via the editing page after submitting your post. In there, click on Manage Attachments and Bob's your uncle :)

[panteraboy]

Im not used to debugging Do you mean debug.print strSQL. then Ctrl +g to the immediate window after running the program. Is there any way to do this without running the main program. I can see that the ORDER BY Clause needs a space in front of it in order to work, but i dont understand why it doent filter properly then in the hard drive .

Sorry for the Annoyance
Paul

[panteraboy]

see the problem now I was that busy looking at my SQL code I didnt see the flaws in the assignment of the values to hard drives. Thanks for all the help again though.
Regards Panteraboy

[panteraboy]

Im such an eejit lol. All it took was a between clause

[code]
Dim make As String
Dim computertype As String
Dim bluetooth As String
Dim harddrive As Integer
Dim h_min As Integer


Set db = CurrentDb
Set qdf = db.QueryDefs("Admin_query")

If (Me.CboOS.Value = "All") Then
operatingsystem = " Like '*' "
Else
operatingsystem = "='" & Me.CboOS.Value & "' "

End If

If (Me.CboMake.Value = "All") Then
make = " Like '*' "
Else
make = "='" & Me.CboMake.Value & "' "

End If

If (Me.CboComputerType.Value = "All") Then
computertype = " Like '*' "
Else
computertype = "='" & Me.CboComputerType.Value & "' "

End If

If (Me.CboBluetooth.Value = "All") Then
bluetooth = " Like '*' "
Else
bluetooth = "='" & Me.CboBluetooth.Value & "' "
End If


If (Me.CboStorage.Value = "Low Spec") Then

harddrive = 100
h_min = 0

ElseIf (Me.CboStorage.Value = "Normal Spec") Then
harddrive = 180
h_min = 101
Else
harddrive = 300
h_min = 181

End If

strSQL = "SELECT laptops.* " & _
" FROM laptops " & _
" WHERE laptops.operating_sysytem" & operatingsystem & _
" AND laptops.manufacturer" & make & _
" AND laptops.bluetooth" & bluetooth & _
" AND laptops.ComputerType" & computertype & _
" AND laptops.hard_drive BETWEEN " & h_min & "AND " & harddrive & _
" ORDER BY laptops.product_id;"

Debug.Print strSQL
qdf.SQL = strSQL
Dim msg As String
msg = "Sorry there are no models in stock with that specification"

[\CODE]

No wonder you didnt tell me the answer NeoPa. It was staring me in da face the whole time he he . Oh Im so happy now (easily amused)
Regards
Paul

[NeoPa]

Im not used to debugging Do you mean debug.print strSQL. then Ctrl +g to the immediate window after running the program.

That is exactly what I do mean yes. This isn't something you should just do for my benefit. This will help you to see for yourself what's going wrong.

It's also helpful to split the SQL up into separate clauses and show them clearly formatted, for the same basic reason. To whit - it's easier to read and understand what it will do. Of course it's easier for someone to help you too on here.

Is there any way to do this without running the main program. I can see that the ORDER BY Clause needs a space in front of it in order to work, but i dont understand why it doent filter properly then in the hard drive.

That's what I've been trying to tell you about. It's no surprise you don't see it as to look properly you need to be looking at the SQL string and not the VBA. When you don't need to do this you won't need to be asking for help to interpret it either.

With experience you can look at the VBA and read it as if it were already in the string. However, to get that experience you need to fall over (or just come across if you're bright) various issues that behave differently from what was intended.

Sorry for the Annoyance
Paul

Rude questionners can be annoying. I don't believe you've ever been rude. This is no annoyance. It's what we volunteer time for - to help and educate where possible.