SQL Error

[panteraboy]

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.

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

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

'below is only two of the many SQL variations ive tried

 '  strSQL = "SELECT laptops.* " & _
       '     "FROM laptops " & _
       '     "WHERE laptops.operating_sysytem" & operatingsystem & _
       '"AND laptops.manufacturer" & make  ;"
           
      '   strSQL = "SELECT laptops.* " & _
       '  "FROM laptops " & _
       '  "WHERE laptops.[operating_sysytem] = '" & operatingsystem & "' "& _
       '  "AND laptops.[manufacturer]=  '" & make & "' " ;"


    
         qdf.SQL = strSQL
         DoCmd.OpenQuery "Admin_query"
         DoCmd.Close acForm, Me.Name
        ' MsgBox strSQL
         
        
         Set qdf = Nothing
         Set db = Nothing
         DoCmd.OpenForm "laptop_specs", , "Admin_query

        End Sub

Can anyone please point me in the right direction
Regards Panteraboy

[panteraboy]

Its ok folks I got her sorted. SQL is so fidely for a first timer lol
Here is da code if your Interested

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

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

         strSQL = "SELECT laptops.* " & _
         "FROM laptops " & _
         "WHERE laptops.operating_sysytem" & operatingsystem & _
         "AND laptops.manufacturer" & make & _
         "ORDER BY laptops.model;"

         qdf.SQL = strSQL
         'DoCmd.OpenQuery "Admin_query"
         DoCmd.Close acForm, Me.Name
        ' MsgBox strSQL     
        
         Set qdf = Nothing
         Set db = Nothing
         DoCmd.OpenForm "laptop_specs", , "Admin_query"
End Sub

Regards Panteraboy

[Stewart Ross]

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

Option Explicit
 
Private Sub CmdSubmit_Click()
 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim comparison_for_operatingsystem As String
Dim comparison_for_make As String
Dim computertype As String
 
Set db = CurrentDb
Set qdf = db.QueryDefs("Admin_query")
 
If (Me.CboOS = "All") Then
comparison_for_operatingsystem = " Like '*' "
Else
comparison_for_operatingsystem = "='" & Me.CboOS & "' "
End If
 
If (Me.CboMake.Value = "All") Then
comparison_for_make = " Like '*' "
Else
comparison_for_make = "='" & Me.CboMake & "' "
End If
 
strSQL = "SELECT laptops.* " & _
"FROM laptops " & _
"WHERE laptops.operating_sysytem" & comparison_for_operatingsystem & _
" AND laptops.manufacturer " & comparison_for_make
 
 
 
qdf.SQL = strSQL
DoCmd.OpenQuery "Admin_query"
DoCmd.Close acForm, Me.Name
' MsgBox strSQL
 
 
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenForm "laptop_specs", , "Admin_query
 
End Sub

[panteraboy]

Thanks steward for that option Explicit tip. I was not aware of that. Your version is much prettier too lol.
Regards Panteraboy