Select From multiple comoboxes

[sirrahhc]

hey everybody,
i'm developing a database and on several forms I have multiple combo boxes. i want a user to choose from the 5 combo boxes and then pull a report based on those combo boxes. The 5 combo boxes are: cbZip, cbDay, cbSic, cbRegion, & cbType. If a user just wants to see a report by day, then they'll leave all combo boxes empty except cbDay. i have some code in there but it only works if the user selects data from each combo box. here's the code i've got. All help is greatly appreciated.

strSQL = "SELECT * Into Top50 From tblTemp WHERE Zip = cbZip.Value AND Day = cbDay.Value AND Sic = cbSic.Value AND Region = cbRegion.Value AND Type = cbType.Value " & _
"Order by tblTemp.Net Desc;"

 

[johnwm]

Looks like a conditional concatenation:

Private Sub Command1_Click()
Dim strSQL As String
strSQL = "Select * from tblTemp"
If Len(cbZip.Text) + Len(cbSic.Text) + Len(cbRegion.Text) + Len(cbType.Text) > 0 Then 'check for all empty
strSQL = strSQL & " Where " ' add where clause if reqd
If Len(cbZip.Text) > 0 Then ' check each cbo
strSQL = strSQL & " Zip = '" & cbZip.Text & "' AND"
End If
If Len(cbSic.Text) > 0 Then
strSQL = strSQL & " Sic = '" & cbSic.Text & "' AND"
End If
If Len(cbRegion.Text) > 0 Then
strSQL = strSQL & " Region = '" & cbRegion.Text & "' AND"
End If
If Len(cbType.Text) > 0 Then
strSQL = strSQL & " Type = '" & cbType.Text & "' AND"
End If
strSQL = Left$(strSQL, Len(strSQL) - 3) ' remove spare AND
End If
MsgBox strSQL
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[johnwm]

I meant to add, Welcome to Tek-Tips - make sure you read faq222-2244 to get the best from the forums!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[sirrahhc]

hey guys thanks for all ur help. i wanted to ask if i posted this in the wrong forum. i read the FAQ222-2244 but couldn't figure what i did wrong. i put the code in my program but am getting an error: "Cant reference a property or method for a control unless the control has the focus" i put in a line of code to set the focus to the first combobox but was ineffective. heres the code slighlty altered:

strTableName = "top50"
strSQL = "SELECT TOP 50 tblTemp.Account, tblTemp.Name, tblTemp.Day, tblTemp.Draw, tblTemp.Returns, tblTemp.Net, tblTemp.Weekend, tblTemp.Sic, tblTemp.Week, tblTemp.Period, tblTemp.Year, [Returns]/[Draw] As RetPct INTO strTableName FROM tblTemp ORDER BY tblTemp.net Desc"
If Len(cbZip.Text) + Len(cbSic.Text) + Len(cbRegion.Text) + Len(cbType.Text) + Len(cbDay.Text) > 0 Then
strSQL = strSQL & "Where"
If Len(cbZip.Text) > 0 Then
strSQL = strSQL & "Zip ='" & cbZip.Text & "' And "
End If
If Len(cbSic.Text) > 0 Then
strSQL = strSQL & "Sic ='" & cbSic.Text & "' And "
End If
If Len(cbRegion.Text) > 0 Then
strSQL = strSQL & "Region ='" & cbRegion.Text & "' And "
End If
If Len(cbType.Text) > 0 Then
strSQL = strSQL & "Type ='" & cbType.Text & "' And "
End If
If Len(cbDay.Text) > 0 Then
strSQL = strSQL & "Day ='" & cbDay.Text & "' And "
End If
strSQL = Left$(strSQL, Len(strSQL) - 3)
End If
MsgBox strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'DoCmd.OpenTable "top50"
DoCmd.OpenReport "top50", acViewPreview

i can't seem to figure this one. i've been stuck on this problem for about 3 weeks exhausting every resource i can think of including 3 vb books. once agian any help is greatly appreciated and if i'm posting in the wrong place please let me know. thx!

 

[Glasgow]

What line of code gives the error?

Valid use of the Text property depends on the style of the combo I think. It might be safer to use, e.g.,

if cbZip.listindex<>-1 then
  strSQL = strSQL & "Zip ='" & cbZip.list(cbZip.listindex) & "' And "
End If

 

[Glasgow]

I'm sorry - I think it's OK to use the text property irrespective of style. However, would still be interested to know the line on which error is triggered.

 

[Glasgow]

Also, johnwm had code:

strSQL = strSQL & " Where " ' add where clause if reqd

the spaces before and after the WHERE are significant and necessary - it looks like you may have stripped them out?

 

[sirrahhc]

i'm getting the error on:
If Len(cbZip.Text) + Len(cbSic.Text) + Len(cbRegion.Text) + Len(cbType.Text) + Len(cbDay.Text) > 0 Then

i'm about to try the code glasgow suggested, thx, i'll let u know what happens.

 

[sirrahhc]

i tried sticking in the spaces for the WHERE clause and am still getting the same error. i also tried the snippet glasgow submitted and now am getting another error

it is saying that the data member for
cbZip.List(cbZip.ListIndex)
cannot be found, with the emphasis on the cbZip.List

 

[Glasgow]

Well I'm not sure why this would fail but you could try changing the test to

if cbzip.listindex<>-1 and cbsic.listindex<>-1 and...

etc

 

[sirrahhc]

i changed glasgow's code to
cbZip(cbZip.ListIndex)
i just took out the List that it did not like and now i'm getting the same Setfocus error on line:
If Len(cbZip.Text) + Len(cbSic.Text) + Len(cbRegion.Text) + Len(cbType.Text) + Len(cbDay.Text) > 0 Then

 

[jjames]

The 'DoCmd' syntax suggests that you are using VBA in MS Access, not Visual Basic. You might do better to post this in one of the MS Access forums.

 

[Glasgow]

If VBA, please disregard my comments which were pitched for VB6. I don't know the differences in this context.

 

[sirrahhc]

ok, that must be the problem then because i am using vb code within access. sorry bout the confusion, i'll post it in an access forum. thx guys