combo select changes SQL
Posted on 2002-03-05
i'm having trouble structuring an sql statement
I want the statement to be different depending on what combo item is selected. If the combos are blank the sql statement should select all records.
These are the 3 combos which should let the user narrow down the search...
These must be able to be clicked in any order to change the sql statement.
So, picking an option from cboDescription should return all parts matching that description. Then picking an option from cboPower should narrow it down even more...
This is how it looks so far -
Private Function StrSql() As String
'(sql to select all parts)
sql = "SELECT Electrical.[Part Number], Electrical.Voltage, Electrical.Power, " & _
"Part.[FSL Part Number], Part.[Made By], Part.Description, Part.image " & _
"FROM Part INNER JOIN Electrical ON Part.[Part Number] = Electrical.[Part Number]"
'sql to narrow search - (works if all combos have values, but if one combo is blank sql statement is wrong.)
sql = sql & " where " & sqlDescription
sql = sql & " and " & sqlVoltage
sql = sql & " and " & sqlPower
Private Function sqlDescription() As String
sqlDescription = "((part.description)= " & "'" & CboDescription.Text & "'" & ")"
Private Function sqlVoltage() As String
sqlVoltage = "((Electrical.Voltage)= " & "'" & CboVoltage.Text & "'" & ")"
Private Function sqlPower() As String
sqlPower = "((Electrical.power)= " & "'" & CboPower.Text & "'" & ")"
any help much appreciated
let me know if you need more info..