Select All Combo

[hi there]

hi everyone,

i'm trying to add an "All" criteria to my combo box that i use as a parameter for a query. i've added the "All" selection using a union query. now i can't figure out what SQL to use in my parameter query. i need some kind of conditional statement that basically says if the value of the combo box is "All" then SELECT * otherwise use the selected item of the combo box. here's my recordsource for my combo box:

SELECT DISTINCT tblUser.ID, tblUser.Name FROM tblUser UNION SELECT " ",' All' FROM tblUser;

could someone please help me with this.

many thanks

 

[dcx693]

The basic structure is:

If Me.cboComboBox=" All" Then
     'Do this
Else
     'Do this instead
End If

So if your SQL is something like "SELECT * FROM table" you basically don't need any criteria if the user has selected all from the combo box. However, if they've made some other selection, then you need to add a WHERE clause to the SQL.

You can do something like this:

Dim strWhere as String
Dim strSQL as String

If Me.cboComboBox=" All" Then
     strWhere=""
Else
     strWhere="Where [Name]='" & Me.cboComboBox & "'"
End If

strSQL="SELECT * FROM table" & " " & strWhere

You should be careful about having a field in your database called "Name" though. It's a reserved word in VBA.

 

[hi there]

hi dcx,

thanks for the response. where should i place this code. am i basically creating a dynamic query using VBA and binding it to a report's recordsource? is there anyway to do this with just SQL in a stored query? i guess my end goal is the produce a report using this query as the recordsource. could you explain to me how i'd go about doing this.

thanks a lot

 

[dcx693]

Check out this link. It might answer some of your questions. Post back if you have more. Adding "All" to a listbox or combobox .