Query with Function for criteria

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I have a query with a Status for a user, as well as more data. This query is
exported to Excel for processing. On the form that kicks off this export, I
give the user the chance to specify which type of status they want to export
(Active, Inactive and/or Pending).

So I want the query to return only those records with any one, or all, of
those values for a status.

To do this, I created a public function which returns the appropriate string
to put into the criteria (or at least I thought that's what it was doing).
It does return something what I would think is the proper syntax for a
criteria. It returns things like the following:
- 'Active'
- 'Active' or 'Pending'
- 'Active' or 'Inactive' or 'Pending'

(and any combination thereof)

This function is the only criteria and is specified in the design view in
the Status column. If only one of these is selected, then the query works.
But if any combination is selected, nothing is returned.

Any ideas what I am doing wrong?
 
To do this, I created a public function which returns the appropriate string
to put into the criteria (or at least I thought that's what it was doing).
It does return something what I would think is the proper syntax for a
criteria. It returns things like the following:
- 'Active'
- 'Active' or 'Pending'
- 'Active' or 'Inactive' or 'Pending'

(and any combination thereof)

This function is the only criteria and is specified in the design view in
the Status column. If only one of these is selected, then the query works.
But if any combination is selected, nothing is returned.
Click to expand...

Care to post your code?

The syntax is in fact incorrect. The OR operator connects *logical
expressions* not values. The correct SQL query would have either

[Status] = 'Active' OR [Status] = 'Pending' OR [Status] = 'Inactive'

or (probably better)

[Status] IN ('Active', 'Inactive', 'Pending')

In either case, the function should not be constructing a criterion to
put on the criteria line; it should be constructing the actual entire
SQL of the Query. The reason is that you cannot pass operators such as
OR or IN or the commas in the IN() clause as parameters.
 
Back
Top