If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using form for a query
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? |
#2
|
|||
|
|||
Using form for a query
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 -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "AB" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|