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

SQL Error

P: 48
Hi there Ive created some SQL code in the Click event of a submit button. I had it working at the start but when i made a few modifications it keep telling me that and end of statement is required . The strings are retrieved from combo box values and then stored in string variables that are assigned to the SQL string.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdSubmit_Click()
  2.  
  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.  
  10. Set db = CurrentDb
  11. Set qdf = db.QueryDefs("Admin_query")
  12.  
  13. If (Me.CboOS.Value = "All") Then
  14.      operatingsystem = " Like '*' "
  15. Else
  16.  operatingsystem = "='" & Me.CboOS.Value & "' "
  17.  
  18. End If
  19.  
  20. If (Me.CboMake.Value = "All") Then
  21. make = " Like '*' "
  22. Else
  23. make = "='" & Me.CboMake.Value = "='" & "' "
  24.  
  25.  End If
  26.  
  27. 'below is only two of the many SQL variations ive tried
  28.  
  29.  '  strSQL = "SELECT laptops.* " & _
  30.        '     "FROM laptops " & _
  31.        '     "WHERE laptops.operating_sysytem" & operatingsystem & _
  32.        '"AND laptops.manufacturer" & make  ;"
  33.  
  34.       '   strSQL = "SELECT laptops.* " & _
  35.        '  "FROM laptops " & _
  36.        '  "WHERE laptops.[operating_sysytem] = '" & operatingsystem & "' "& _
  37.        '  "AND laptops.[manufacturer]=  '" & make & "' " ;"
  38.  
  39.  
  40.  
  41.          qdf.SQL = strSQL
  42.          DoCmd.OpenQuery "Admin_query"
  43.          DoCmd.Close acForm, Me.Name
  44.         ' MsgBox strSQL
  45.  
  46.  
  47.          Set qdf = Nothing
  48.          Set db = Nothing
  49.          DoCmd.OpenForm "laptop_specs", , "Admin_query
  50.  
  51.         End Sub
  52.  
Can anyone please point me in the right direction
Regards Panteraboy
May 9 '08 #1
Share this Question
Share on Google+
3 Replies


P: 48
Its ok folks I got her sorted. SQL is so fidely for a first timer lol
Here is da code if your Interested
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.  
  10. Set db = CurrentDb
  11. Set qdf = db.QueryDefs("Admin_query")
  12.  
  13. If (Me.CboOS.Value = "All") Then
  14.      operatingsystem = " Like '*' "
  15.  
  16. Else
  17.  operatingsystem = "='" & Me.CboOS.Value & "' "
  18.  
  19.  
  20. End If
  21.  
  22.  
  23. If (Me.CboMake.Value = "All") Then
  24. make = " Like '*' "
  25. Else
  26. make = "='" & Me.CboMake.Value & "' "
  27.  
  28.  End If
  29.  
  30.          strSQL = "SELECT laptops.* " & _
  31.          "FROM laptops " & _
  32.          "WHERE laptops.operating_sysytem" & operatingsystem & _
  33.          "AND laptops.manufacturer" & make & _
  34.          "ORDER BY laptops.model;"
  35.  
  36.          qdf.SQL = strSQL
  37.          'DoCmd.OpenQuery "Admin_query"
  38.          DoCmd.Close acForm, Me.Name
  39.         ' MsgBox strSQL     
  40.  
  41.          Set qdf = Nothing
  42.          Set db = Nothing
  43.          DoCmd.OpenForm "laptop_specs", , "Admin_query"
  44. End Sub
  45.  
Regards Panteraboy
May 9 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi Panteraboy. There is a mistake in line 32 above, which has an extra double quote and semicolon (no doubt from the first version which you altered). Lines 34-37 repeat part of the definition and should be deleted.

]I also think you have misnamed variables make and operatingsystem, which are really the comparison parts of the WHERE clause. Reading the code first time round it looked like you were missing comparison operators at this point, but this is not the case - it is just that the variable names are very misleading

There is also a mistake in the comparison operator itself

You also appear to have no DIM statement for operatingsystem, and if this is the case you will also not have Option Explicit set. This is a crucial setting, as without it you can use undeclared variables without error - including if you simply make a mistake in a variable name. With Option Explicit on, the compiler will not let you use undeclared variables. It is good practice to have this as a default setting.

Another point: whilst it is correct to use the Value property of a control it does not actually add anything to a direct reference to the control itself, which will return the same thing.

A revised version of your code is listed below.

-Stewart

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub CmdSubmit_Click()
  4.  
  5. Dim db As DAO.Database
  6. Dim qdf As DAO.QueryDef
  7. Dim strSQL As String
  8. Dim comparison_for_operatingsystem As String
  9. Dim comparison_for_make As String
  10. Dim computertype As String
  11.  
  12. Set db = CurrentDb
  13. Set qdf = db.QueryDefs("Admin_query")
  14.  
  15. If (Me.CboOS = "All") Then
  16. comparison_for_operatingsystem = " Like '*' "
  17. Else
  18. comparison_for_operatingsystem = "='" & Me.CboOS & "' "
  19. End If
  20.  
  21. If (Me.CboMake.Value = "All") Then
  22. comparison_for_make = " Like '*' "
  23. Else
  24. comparison_for_make = "='" & Me.CboMake & "' "
  25. End If
  26.  
  27. strSQL = "SELECT laptops.* " & _
  28. "FROM laptops " & _
  29. "WHERE laptops.operating_sysytem" & comparison_for_operatingsystem & _
  30. " AND laptops.manufacturer " & comparison_for_make
  31.  
  32.  
  33.  
  34. qdf.SQL = strSQL
  35. DoCmd.OpenQuery "Admin_query"
  36. DoCmd.Close acForm, Me.Name
  37. ' MsgBox strSQL
  38.  
  39.  
  40. Set qdf = Nothing
  41. Set db = Nothing
  42. DoCmd.OpenForm "laptop_specs", , "Admin_query
  43.  
  44. End Sub
  45.  
May 9 '08 #3

P: 48
Thanks steward for that option Explicit tip. I was not aware of that. Your version is much prettier too lol.
Regards Panteraboy
May 9 '08 #4

Post your reply

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