Dynamic Query from Cascading Combo Boxes

[Guest]

Would someone please help me? I have a form that has to date picker boxes and four cascading combo boxes. What I would like to do is have the user to select a date range and the retrieve the records based on the date range

Then based on the users selection for each of the four cascading combo boxes of if certain ones are left blank, I want to retrieve records from the original set of records based on the criteria provided

I would then like to send the final records to a report I have created and then reset the date picker and combo boxes to their original state

What is the best way to accomplish this

Sincerely
Glenn E. White

 

[Graham Mandeno]

Hi Glenn

You should build up a filter string in code, depending on what selections
have been made, and then use that filter string when you open the report:

Dim sFltr as String
sFltr = "(DateField between " & Format(dtpStartDate, "\#mm/dd/yyyy\#") _
" and " & Format(dtpStartDate, "\#mm/dd/yyyy\#") & ")"
If Not IsNull(cboSelectWidgetType) then
sFltr = sFltr & " and (WidgetType=" & cboSelectWidgetType & ")"
End If
' repeat for other combo boxes
DoCmd.OpenReport "...", WhereCondition:=sFltr

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Would someone please help me? I have a form that has to date picker boxes

and four cascading combo boxes. What I would like to do is have the user to
select a date range and the retrieve the records based on the date range.

Then based on the users selection for each of the four cascading combo

boxes of if certain ones are left blank, I want to retrieve records from the
original set of records based on the criteria provided.

I would then like to send the final records to a report I have created and

then reset the date picker and combo boxes to their original state.