Can I vary the comparison operator in a query???

[greg32246]

I have a form that collects criteria for a report (and the underlying query).

One of the criteria is a status. There are times when a user might want to see a specific status, for example, status = 1. No problem. But I want to also include the possibility to see all records where status is less than 3.

I can easily pass either 1 or 3 from a status field on the form to the query using standard [Form]![field] convention, but I can't pass the whole expression like =3 or <3. It won't accept the comparision operator and I can't think of a way to make it variable.

Am I just looking at this totally wrong? How else might I accomplish what I want.

I am trying to avoid calling a whole bunch of different queries because I have a number of criteria on the form and there are a lot of different statuses.

Any help would be appreciated!

Thanks

 

[genomon]

Can you post the SQL for the query?

Illegitimi non carborundum.

 

[greg32246]

Here is a simplified version of the SQL. I only included the join fields and those that have selection criteria. There are actually more than 30 fields pulled from the three tables.

SELECT dbo_Archive.INDEX
FROM (dbo_Archive LEFT JOIN dbo_QCTable ON dbo_Archive.INDEX = dbo_QCTable.IncomingIndex) LEFT JOIN dbo_PKTable ON dbo_Archive.INDEX = dbo_PKTable.IncomingIndex
WHERE (((dbo_Archive.ShipmentType) Like "*" & [Forms]![RptCrit]![ShipType]) AND ((dbo_Archive.Priority) Like "*" & [Forms]![RptCrit]![Priority]) AND ((dbo_Archive.[INVOICE #])=[Forms]![RptCrit]![Invoice]) AND ((dbo_Archive.Year)=[Forms]![RptCrit]![Year]) AND ((dbo_Archive.[Week#])=[Forms]![RptCrit]![Week]) AND ((dbo_Archive.Breakdown) Is Null Or (dbo_Archive.Breakdown)="C") AND ((dbo_Archive.QAPKStatus)=1) AND ((dbo_Archive.AdminStatus)=1));

On the last two fields QAPKStatus and AdminStatus, I have just included the criteria "=1". These are the fields I am trying to make more variable. They can each be a value from 1 to 4 representing different stages, the last stage (4), being "complete". Sometimes I want to return a specific status, like 2, and other times I want to know everything that is NOT complete, so <> 4 or < 4. The user is able to select these from a list box that I have to resolve into a criteria. For example, list box selection 5 is "Not Complete", but since 5 is not an actual value in the field (only values 1-4), I have to resolve 5 to mean "< 4".

The other selection criteria come from the form "RptCrit". Some of them use the Like comparison with the "*" so that they can include ALL records if the field is left blank.

I thought about trying to construct variables for the selection criteria for the two Status fields and then plugging the variable straight into the SQL, but I am bad working with SQL and SQL syntax. I prefer to use the Query Window in Access. In fact, I already made one attempt and just kept getting an "Extra Parentecy )" error.

 

[SkipVought]

What about IN

and AND ((dbo_Archive.QAPKStatus)=dbo_Archive.AdminStatus) AND ((dbo_Archive.AdminStatus) IN (1)));

I have code to make a LIST to substitute in the parentheses, so it could be any individual value or any combinations of values.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I rarely place any dynamic criteria in a query that is used as a Record Source of a report or form. If possible, I will use controls on a form to collect criteria and then build a WHERE CONDITION to use with the DoCmd.OpenReport method. This offers greater functionality and flexibility.

To find some examples of this, search the Reports forum on
[tt][blue]
strWhere = "1=1 "
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[greg32246]

Thanks for all the help.

I have used the Report Where Condition before. That is my usual method.

In this case, the users also want a continuous form based on the same selection criteria, which they can then use to update records as needed. So, it's not just a report.

I decided to go ahead and resolve the variables for the different status fields first and then just manually constructed my SQL query.