Multiple parameters for query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've seen similar postiings but can't find the right answer...

I've got a form with 4 combo boxes (location, start date, end date,
customer). I want the user to be able to run a query based on this
information. For example, if the user only selects a location, I want to see
every entry for the location. If he also enters a start date, I want to see
every entry for that location from the start date on.

When I use [Forms]![fltrInventoryReport]![Combo0] as a parameter I only see
exact matches and when I add OR Like "*" I see the unfiltered lot.

How can I set this up so a user can opt to use a parameter to limit returns?

BONUS QUESTION: How can make the Shipper box work as a multiselect? I've
made the necessary property changes but don't know how to properly pass along
the full string of selections (or as mentioned above, lack of selections).

Any guidance is greatly appreciated.
 
Hi,

one of the possible way, for the first part, is to define a criteria,
preferably in SQL view, like:


.... WHERE ... AND ( FORMS!formName!ComboBoxName IS NULL OR
FORMS!formName!ComboBoxName = someFieldName ) AND ...


if the combobox is left empty, the left part of the OR is true, making the
OR evaluates to true, unconditionally of its right part. If the combo box is
not left empty, then the right part of the OR is governing the result of the
OR. So, this construction behaves as wanted.





Hoping it may help,
Vanderghast, Access MVP
 
AB said:
I've seen similar postiings but can't find the right answer...

I've got a form with 4 combo boxes (location, start date, end date,
customer). I want the user to be able to run a query based on this
information. For example, if the user only selects a location, I want to see
every entry for the location. If he also enters a start date, I want to see
every entry for that location from the start date on.

When I use [Forms]![fltrInventoryReport]![Combo0] as a parameter I only see
exact matches and when I add OR Like "*" I see the unfiltered lot.

How can I set this up so a user can opt to use a parameter to limit returns?

BONUS QUESTION: How can make the Shipper box work as a multiselect? I've
made the necessary property changes but don't know how to properly pass along
the full string of selections (or as mentioned above, lack of selections).
Click to expand...


Try a criteria like this for the location and customer
fields:

Forms!fltrInventoryReport!Combo0 OR
(Forms!fltrInventoryReport!Combo0 Is Null)

For the date field, try creating a calculated field with
this kind of expression:

((datefield >= Forms!fltrInventoryReport!startdate) OR
(Forms!fltrInventoryReport!startdate Is Null) AND
((datefield <= Forms!fltrInventoryReport!enddate) OR
(Forms!fltrInventoryReport!enddate Is Null)

and set its criteria to True
 
AB,

When I have this situation, I generally write a function to define the WHERE
clause of my query and run that function on the click of a command button or
in the afterupdate of each of the text boxes, combo boxes, or the click
event of a multi-select list box.

You didn't indicate what you are going to do with the query. If it is the
record source for the form that these controls are on, you can just define
the WHERE clause and use the Forms "Filter" property to restrict the rows.
You could do the same thing if you are using the query as the source for a
report, and pass the WHERE clause as an argument to the OpenReport method.

Some may think this is a bit overboard, but I get confused when I try to
read code that is trying to encapsulate the quotation mark or a single quote
(apostrophe), so I have a little function I use to wrap values between a
character (in this case I use it to wrap text and pass it the # to wrap date
values. Put this code in a code module.

Public Function Wrap(TextToWrap As Variant, Optional Wrapper As String =
"""") As String

Wrap = Wrapper & TextToWrap & Wrapper

End Function

At any rate, here is an example of how I might create this filter in the
code behind a command button (cmd_Filter) which filters the current form.

Private Sub FilterForm

Dim varFilter as Variant

varFilter = NULL

'Add to the filter if the location field has something in it
if not Len(me.cbo_Location & "") > 0 then
varFilter = "[Location_CD] = " & Wrap(me.cbo_Location)
endif

'Add to the filter if txt_Start_Date has something in it
if LEN(me.txt_Start_Date & "") > 0 AND isdate(me.txt_Start_Date) THEN
varFilter = (varFilter + " AND ") _
& "[Date_Field] > " & Wrap(me.txt_Start_Date, "#")
endif

'Add to the filter if txt_End_Date has something in it
if LEN(me.txt_End_Date & "") > 0 and isdate(me.txt_End_Date) THEN
varFilter = (varFilter + " AND ") _
& "[Date_Field] < " & Wrap(DateAdd("d", 1,
me.txt_End_Date), "#")
endif

'Add to the filter if txt_Customer_ID has something in it
'(this is usually numeric, so it would not need to be wrapped
if LEN(me.txt_Customer_ID & "") > 0 THEN
varFilter = (varFilter + " AND ") _
& "[CUSTOMER_ID] = " & me.txt_Customer_ID
endif

me.Filter = IIF(LEN(varFilter & "") > 0, varFilter, "")
me.FilterOn = IIF(LEN(varFilter & "") > 0, True, False)

End Sub

Hope this Helps
Dale


AB said:
I've seen similar postiings but can't find the right answer...

I've got a form with 4 combo boxes (location, start date, end date,
customer). I want the user to be able to run a query based on this
information. For example, if the user only selects a location, I want to
see
every entry for the location. If he also enters a start date, I want to
see
every entry for that location from the start date on.

When I use [Forms]![fltrInventoryReport]![Combo0] as a parameter I only
see
exact matches and when I add OR Like "*" I see the unfiltered lot.

How can I set this up so a user can opt to use a parameter to limit
returns?

BONUS QUESTION: How can make the Shipper box work as a multiselect? I've
made the necessary property changes but don't know how to properly pass
along
the full string of selections (or as mentioned above, lack of selections).

Any guidance is greatly appreciated.
Click to expand...
 
Michel, Marshall, & Dale,

I just wanted to say thanks for the help. In the end, I worked off of
Dale's concept. The script is doing exactly what I was hoping it would do.
I'll post it below.

Again, thanks to everyone for the help and guidance.

---------------------------------------------------------------
Dim varFilter As Variant
Dim blnPrecursor As Boolean
Dim stDocName As String

stDocName = "Current_Inventory"

' Clearing filter
varFilter = Null

' Precursor will let us know if SQL statement needs AND for append
blnPrecursor = False

' Filter for records with Location = Me.cmbLocation
If IsNull(Me.cmbLocation) = False Then
' Writing filter for scan
varFilter = "[tblScans.Location] = '" & Me.cmbLocation & "' "
' Flagging filter as written
blnPrecursor = True
End If

' Filter for records with scan date within start & end dates
If IsNull(Me.txtStartDate) = False And IsDate(Me.txtStartDate) = True Then
If IsNull(Me.txtEndDate) = False And IsDate(Me.txtEndDate) = True Then
' If Start and End dates exist
If blnPrecursor = True Then
' Write filter with AND
varFilter = varFilter & "AND [tblScans.Scan] Between #" &
Me.txtStartDate & _
"# And #" & Me.txtEndDate & "# "
Else
' Flagging filter as written
blnPrecursor = True
' Writing filter for date range
varFilter = varFilter & "[tblScans.Scan] Between #" &
Me.txtStartDate & _
"# And #" & Me.txtEndDate & "# "
End If
Else
' If start exists but end doesn't
If blnPrecursor = True Then
' Writing with AND
varFilter = varFilter & "AND [tblScans.Scan] > #" &
Me.txtStartDate & "# "
Else
' Flagging filter as written
blnPrecursor = True
varFilter = varFilter & "[tblScans.Scan] > #" &
Me.txtStartDate & "# "
End If
End If
ElseIf IsNull(Me.txtEndDate) = False And IsDate(Me.txtEndDate) = True Then
' If only End date exists
If blnPrecursor = True Then
' Writing with AND
varFilter = varFilter & "AND [tblScans.Scan] < #" &
Me.txtEndDate & "# "
Else
' Flagging filter as written
blnPrecursor = True
varFilter = varFilter & "[tblScans.Scan] < #" & Me.txtEndDate &
"# "
End If
End If

' Filter for records with Shipper = Me.cmbShipper
If IsNull(Me.cmbShipper) = False Then
If blnPrecursor = True Then
' Writing with AND
varFilter = varFilter & "AND [tblInventory.Shipper] = '" &
Me.cmbShipper & "'"
Else
varFilter = varFilter & "[tblInventory.Shipper] = '" &
Me.cmbShipper & "'"
End If
End If

DoCmd.OpenReport stDocName, acPreview, , varFilter
--------------------------------------------------------------------------------------
 
AB,

Glad I could help.

You would have saved yourself a lot of code if you had used:

varFilter = (varFilter + " AND ") & ........

But if it makes it more readable for you, go for it!

Dale
 
Back
Top