Using form for a query

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

Guest

I'm using a form to set parameters for a query, and eventually a report. I
want to query for all shipments going to a specific city, selected from a
combo box. This works fine if I ask the query to look back at the form for
the destination criteria.

However, I want users to be able to select a wildcard-type selection that
would have the query run for all destinations. I've tried using a blank and
* to no avail, so I'm guessing I will need to build a SELECT string to pass
along as part of the DoCmd, but I don't know how to do this.

Can someone please help?
 
Hi AB

You don't build a SELECT string, you build a WHERE string (without the
"WHERE").

For example, suppose the fields you want to filter on are CustomerID and
ShipmentDate, and you have three unbound controls on your form: a combo box
(cboCustomer) to optionally select the customer, and two textboxes
(txtStartDate and txtEndDate) to optionally select a range of dates.

You can construct a string using the values (if any) of these three
controls. For example:

Const cFmtDate = "\#mm\/dd\/yyyy\#
Dim sFltr as string
If not IsNull (cboCustomer) then
sFltr = "[CustomerID]='" & cboCustomer & "'"
End If
If not IsNull (txtStartDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[ShipmentDate]>=" & Format(txtStartDate, cFmtDate)
End If
If not IsNull (txtEndDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[ShipmentDate]<" & Format(txtEndDate+1, cFmtDate)
End If
' Now you can use the filter string to open your report
DoCmd.OpenReport "rptShipmentDetails", , , sFltr
 
Back
Top