Query Criteria with If-Then to return all records

[chuckcoleman]

Hi, I know this has been addressed before but I can't find the solution. I have a form with a ComboBox. The ComboBox drop-down has the name of contractors and the name of "All". "All" has a value of one and each contractor has their own value, like 2, 3, 4 etc. If the value is not equal to 1/All, everything works as expected. I'm looking to have all records returned if the value of the ComboBox is 1. My criteria is:

On the first criteria line: IIf([Forms]![Report Form]![ContractorIDX]<>1,[Forms]![Report Form]![ContractorIDX]) NOTE: there are some other fields with their own criteria, i.e. the [Balance] is <> 0

On the second criteria line I have: IIf([Forms]![Report Form]![ContractorIDX]=1,Like "*") I have played around with various combinations of the Like function.

Suggestions please.

Chuck

 

[Minty]

If you remove the All or make the ID it returns null then you simply need

[Forms]![Report Form]![ContractorIDX] or [Forms]![Report Form]![ContractorIDX] is Null

And it will return all the values. This will also work if you simply don't set anything as a value in the combo(leave it blank)

 

[chuckcoleman]

Minty, I wasn't clear on your response. When you said "remove the All", All is one of the Contractor names. It's the value of the AutoNumber field for the contractor by the name of All. Since it is an Autonumber field, it will never be Null.

 

[Minty]

Ah - okay frequently someone uses a union query to generate the All in a combo, so it's easier to accommodate.

I've not tried this but try

[Forms]![Report Form]![ContractorIDX] or [Forms]![Report Form]![ContractorIDX] = 1

As the second part of the clause will evaluate to true, causing all records to be returned.
( Assuming All = 1 in your combo)

 

[chuckcoleman]

Minty, There is a Contractor table. It has a list of the contractors, each with their own Autonumber field. The contractor who's name is "All" has an Autonumber value of 1. There is a Job Table. In the Job Table you have a list of things like the amount of the job, a description of the job and who is the contractor. The value of the contractor field in the job table will never be a 1. It will always be a value higher than one. In other words, a real contractor. Therefore in the query in question, you will never be able to use as critera from the Job Table a contractor with a autonumber of 1. In the Report Form, if they pick a real contractor from the Combobox, the query runs fine. An option to pick from the Combobox is "ALL". When that's picked, I want it to return all records regardless of who the contractor is. I hope this isn't too confusing.

UPDATE: If I leave the "or" line of the criteria blank AND I pick a contractor that doesn't have a job that meets the other criteria, it returns an invalid record, (the contractor that doesn't have a job), because the query criteria for the other field is met, i.e. the [Balance] isn't zero.

 

[Minty]

It isn't confusing - the logic of the solution is possibly confusing you.

In your query (as a test) put criteria in the contractor ID of 1=1 and see what you get back.
That is what this bit [Forms]![Report Form]![ContractorIDX] = 1 is evaluating to when you select "All"

 

[arnelgp]

just create a Simple criteria to that Field, example:

[fieldName] Like [p1]

then on your form, you set the value of p1 through vba:

If combo = 1 Then
DoCmd.Setparameter "p1", "*"
Else
DoCmd.Setparameter "p1", [combo].value
End If

DoCmd.OpenQuery "YourQuery"
'if report
'DoCmd.OpenReport "yourReport"

 

[chuckcoleman]

arnelgp, thanks. I had to try a few different vba commands to get it to work. Essentially the DoCmd.Setparameter "p1", "*" resulted in a 2431 error. I got around that by replacing it with, Me.p1 = "*" which worked.

Thank you for taking the time to reply. It was helpful.

 

[Minty]

UPDATE: If I leave the "or" line of the criteria blank AND I pick a contractor that doesn't have a job that meets the other criteria, it returns an invalid record, (the contractor that doesn't have a job), because the query criteria for the other field is met, i.e. the [Balance] isn't zero.

Just to revisit this, although @arnelgp solution is an elegant one - you needed to put the criteria in exactly as I described, the other criteria shouldn't affect it if it entered correctly.
If you are using the query designer window put all the other criteria in first then add this one last.
It should add the other criteria against both the parts of the ContractorID criteria

[Forms]![Report Form]![ContractorIDX] OR [Forms]![Report Form]![ContractorIDX] = 1

If your combo selection is All your criteria would then evaluate as

[Forms]![Report Form]![ContractorIDX] OR 1 = 1

If you select a contractor whose ID is say 99 it then evaluates to

99 OR 99 =1

So it would only return contractor 99