Passing criteria to a query (1 Viewer)

[AndyS48UK]

Is it possible to pass criteria to a query?

I have two forms which extract different data from the same query.

The query needs to have criteria from whichever form is open.

If I use the "OR" field int he criteria then I'm asked for the paramatere from the form which is NOT currently open so I thought I could maybe pass the criteria using an AfterUpdate event in VBA?

 

[raskew]

One way is to rebuild the query-SQL to prompt for desired criteria.

Suppose you had query27, based on Northwind's Orders table, as shown below:

PARAMETERS [enter order date] DateTime, [enter ship country] Text;
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShipCountry
FROM Orders
WHERE (((Orders.OrderDate)<[enter order date]) AND ((Orders.ShipCountry)=[enter ship country]));

This will prompt both for an Order Date and the Ship Country.

The following function, called from a command button on each form:
For the date prompt: Call splitquery("query27", "date")
For the country prompt: Call splitquery("query27", "country")

Will create and open query "TempFilter", based upon your calling choice (date or country).

Function SplitQuery(myQuery As String, mytype As String)
Dim db As Database
Dim qd As QueryDef
Dim tName As String, test As String
Dim xleft, xmid, xright, strSQL, intType

Set db = CurrentDb
Set qd = db.QueryDefs(myQuery)
strSQL = qd.SQL

On Error Resume Next
tName = "TempFilter"
'Does query "TempFilter" exist? If true, delete it;
test = db.QueryDefs(tName).Name
If Err <> 3265 Then
DoCmd.DeleteObject acQuery, "TempFilter"
End If

intType = IIf(mytype = "Date", 1, 2)
'break query into sections
xleft = Left(strSQL, InStr(strSQL, ";"))
xmid = Mid(strSQL, InStr(strSQL, ";") + 1)
xmid = Left(xmid, InStr(xmid, "WHERE") - 1)
xright = Mid(strSQL, InStr(strSQL, "WHERE"))

xleft = "PARAMETERS " & IIf(intType = 1, "[enter order date] DateTime;", "[enter ship country] Text;")
xright = "WHERE " & IIf(intType = 1, "(Orders.OrderDate)<[enter order date];", "(Orders.ShipCountry)=[enter ship country];")
strSQL = xleft & xmid & xright

Set qd = db.CreateQueryDef("TempFilter", strSQL)
db.QueryDefs.Refresh
DoCmd.OpenQuery "TempFilter", acViewNormal

qd.Close
db.Close
Set db = Nothing
End Function

 

[Pat Hartman]

If you open a form based on the query rather than the query directly, you can use the where argument of the OpenForm Method to supply the criteria. Using this method, the query does not need to refer to a form and the selection criteria can be entirely different.

If you simply want all the records with value "A" when you open the query from formA and all the records with value "B" when you open the query from formB, you can create a user-defined function to pass the variable.

Global gYourParm As String

Public Function GetMyValue()
GetMyValue = gYourParm
End Function

In FormA -
gYourParm = "A"

In FormB -
gYourParm = "B"

In the query:

Where SomeField = GetMyValue()

 

[AndyS48UK]

Thanks

Raskew & Pat

Thanks for these.

I took a while to read them as I worked around it in the meantime by creating a copy of the query each with a different parameter. But, trying to normalise everything, I'll go back and use these.

Thanks again

Andy