By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Comparison operator Syntax

P: 48
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

Expand|Select|Wrap|Line Numbers
  1.  "AND laptops.hard_drive >=" & harddrive & _
  2.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub CmdSubmit_Click()
  3. Dim db As DAO.Database
  4. Dim qdf As DAO.QueryDef
  5. Dim strSQL As String
  6. Dim operatingsystem As String
  7. Dim make As String
  8. Dim computertype As String
  9. Dim bluetooth As String
  10. Dim harddrive As Integer
  11.  
  12.  
  13.  
  14. Set db = CurrentDb
  15. Set qdf = db.QueryDefs("Admin_query")
  16.  
  17. If (Me.CboOS.Value = "All") Then
  18.      operatingsystem = " Like '*' "
  19. Else
  20.  operatingsystem = "='" & Me.CboOS.Value & "' "
  21.  
  22. End If
  23.  
  24. If (Me.CboMake.Value = "All") Then
  25. make = " Like '*' "
  26. Else
  27. make = "='" & Me.CboMake.Value & "' "
  28.  
  29. End If
  30.  
  31. If (Me.CboComputerType.Value = "All") Then
  32. computertype = " Like '*' "
  33. Else
  34. computertype = "='" & Me.CboComputerType.Value & "' "
  35.  
  36. End If
  37.  
  38. If (Me.CboBluetooth.Value = "All") Then
  39. bluetooth = " Like '*' "
  40. Else
  41. bluetooth = "='" & Me.CboBluetooth.Value & "' "
  42. End If
  43.  
  44.  
  45. If (Me.CboStorage.Value = "Low Spec") Then
  46.  
  47.     harddrive = 120
  48.  
  49. ElseIf (Me.CboStorage.Value = "Normal Spec") Then
  50.  harddrive = 180
  51. Else
  52. harddrive = 300
  53.  
  54. End If
  55.  
  56.  
  57.          strSQL = "SELECT laptops.* " & _
  58.          "FROM laptops " & _
  59.          "WHERE laptops.operating_sysytem" & operatingsystem & _
  60.          "AND laptops.manufacturer" & make & _
  61.          "AND laptops.bluetooth" & bluetooth & _
  62.          "AND laptops.ComputerType" & computertype & _
  63.          "AND laptops.hard_drive >=" & harddrive & _
  64.          "ORDER BY laptops.model;"
  65.          qdf.SQL = strSQL
  66.          Dim msg As String
  67.          msg = "Sorry there are no models in stock with that specification"
  68.  
  69.  
  70.        If IsNull(DLookup("model", "admin_query", "product_id")) Then
  71.          MsgBox msg
  72.  
  73.         Set qdf = Nothing
  74.         Set db = Nothing
  75.  
  76.         Exit Sub
  77.  
  78.         Else
  79.  
  80.  
  81.          DoCmd.Close acForm, Me.Name
  82.  
  83.          Set qdf = Nothing
  84.          Set db = Nothing
  85.          DoCmd.OpenForm "laptop_specs", , "Admin_query"
  86.  
  87.         End If
  88.  
  89. End Sub
  90.  
regards panteraboy
May 13 '08 #1
Share this Question
Share on Google+
12 Replies


patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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;
  3.  
  4.  
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
May 13 '08 #2

P: 48
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
May 13 '08 #3

patjones
Expert 100+
P: 931
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
May 13 '08 #4

P: 48
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)
May 13 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 14 '08 #6

P: 48
OK Neopa

sql code of click event
Expand|Select|Wrap|Line Numbers
  1. .
  2.     strSQL = "SELECT laptops.* " & _
  3.          "FROM laptops " & _
  4.          "WHERE laptops.operating_sysytem" & operatingsystem & _
  5.          "AND laptops.manufacturer" & make & _
  6.          "AND laptops.bluetooth" & bluetooth & _
  7.          "AND laptops.ComputerType" & computertype & _
  8.          "AND laptops.hard_drive<=" & harddrive & _
  9.          " ORDER BY laptops.model;"   ' NOTICE SPACE BEFORE ORDER
  10.          qdf.SQL = strSQL
  11.          Dim msg As String
  12.          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
May 14 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 14 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
...
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 :)
May 14 '08 #9

P: 48
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
May 14 '08 #10

P: 48
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
May 14 '08 #11

P: 48
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
May 14 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 14 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.