Query Criteria IIF Staatment with <> DLOOKUP 1

[Speiro1]

Hi All

I am having trouble adding criteria to a query. I need the criteria to change based a drop down box found in a form "fmReportXIRR." If "In Progress" is selected then I want the query to produce a list of all dates greater then the DLOOKUP value, and if not then I want the query to return the value of the DLLOKUP and anything less than it. I hope this make sence. The whole thing works if I take the ">" and "<=" out of the criteria, but obviously it will not produce the desired results. Clearly the symboles are the the porblem and I dont know how to fix it. Any help would be greatly appreaciated.

![fmReportXIRR]![Combo134]="in Progress",>DLookUp("[maxofReportingPeriod]","[qrIRRReportingMaxDate]"),<=DLookUp("[maxofReportingPeriod]","[qrIRRReportingMaxDate]"))]

Thank you

 

[LaurieHamlin]

First I would make the DLookUp it's own field in a previous query, naming it status, if I understand you correctly. Then use that field in your final query criteria.
I hope that helps.

 

[dhookom]

You can't use operators inside the IIf().

IIf([forms![fmReportXIRR]![Combo134]="in Progress", [highlight #FCE94F][COLOR=#EF2929]>[/color][/highlight]DLookUp("[maxofReportingPeriod]","[qrIRRReportingMaxDate]"),[highlight #FCE94F][COLOR=#EF2929]<=[/color][/highlight]DLookUp("[maxofReportingPeriod]","[qrIRRReportingMaxDate]"))

You could use a criteria with Between and expressions based on the control value.
Another solution is to place a couple invisible text boxes on fmReportXIRR for the high and low values and assign their value based on the value of Combo134 (real name?). Then use a simple between.

You could also use code to change the SQL property of a saved query which for me is less complex.

Duane
Hook'D on Access
MS Access MVP

 

[Speiro1]

Thank you both for your help. I was able to achieve my desired results bu creating a second query. One produce the greater than dates and the second produced the qual to and less than dates. I added a second combo box to the form to produce the less than dates and the orignal box contained the greater than dates. Based on the selection made in combo box 134 one or the other box with dates would show up. A text box was set up to pull the section made in either of the combo boxes and all reports were run off of the text box as the criteria. I believe I used some details from both your suggestions.

 

[dhookom]

Consider giving your significant controls significant names. I would never accept "Combo134".

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Some people go with the rule: if the control is not mentioned in the code, it may have default name, like Label1, Combo56. But if the control is mentioned in the code – it has to have a meaningful name, so anybody who looks at the code can tell what this control is.

But I am with dhookom – I name all of my controls, no default or missleading names.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.