Query Criteria question

[AUGuy]

I'm trying to use this if-statement in a query:

=IIf([Forms]![frmReportCollateral]![cbAge]="60-119",>59<120,"")

It doesn't work, though. I can do >59<120 in the criteria box and it will work, but if i put it in this if statement format then it doesn't return any records. Anyone have a hint as to what might be the problem?

Thanks!
G

 

[MSAccessRookie]

I'm trying to use this if-statement in a query:

=IIf([Forms]![frmReportCollateral]![cbAge]="60-119",>59<120,"")

It doesn't work, though. I can do >59<120 in the criteria box and it will work, but if i put it in this if statement format then it doesn't return any records. Anyone have a hint as to what might be the problem?

Thanks!
G

Although I am not quite sure what your intentions are here, I think your IIf() statement could have a syntax error. Try using the following instead and see if it is what you want.

=IIf([Forms]![frmReportCollateral]![cbAge]="60-119",[COLOR=red][B]"[/B][/COLOR]>59<120[COLOR=red][B]"[/B][/COLOR],"")

-- Rookie

 

[vbaInet]

Perhaps he's trying to set a different criteria, i.e. greater than 59 AND less than 120 if cbAge is equal to "60-129"?

 

[AUGuy]

Perhaps he's trying to set a different criteria, i.e. greater than 59 AND less than 120 if cbAge is equal to "60-129"?

Yes, the criteria is on a numerical field. So based on the option in the combo box (in this case "60-119") I want it to only show the records where this field is > 59 < 120. The fix suggested above doesn't work (I'm guessing because the quotes interpret it as a string criteria rather than a logical expression).

Based on this, is it possible to set the criteria in this way?

thanks!
G

 

[rosegmeyer]

Have you tried a BETWEEN statement?

 

[AUGuy]

Have you tried a BETWEEN statement?

On it's own, it works. Embedded into the IF statement, it does not

 

[boblarson]

On it's own, it works. Embedded into the IF statement, it does not

that's because you can't have an operator (>, <, >=, <=) added like that because it sees it as a text string and not the literal operator. You will likely need to change the query using VBA and a querydef.

 

[MSAccessRookie]

that's because you can't have an operator (>, <, >=, <=) added like that because it sees it as a text string and not the literal operator. You will likely need to change the query using VBA and a querydef.

Thanks for explaining it better than I could have. One question: Could a DLookup be used to get what the OP wants? I am still not comfortable with the ins and outs of DLookups

 

[boblarson]

Thanks for explaining it better than I could have. One question: Could a DLookup be used to get what the OP wants? I am still not comfortable with the ins and outs of DLookups

No, as he is trying to get criteria for one field based on a control in a form. Eval() might work but isn't likely. The most likely scenario is to open the query in code and modify the querydef to add the criteria.

 

[vbaInet]

The Eval() function will return a string so it won't read the operator as an operator.

What about:

BETWEEN Val([Forms]![frmReportCollateral]![cbAge]) AND Val(Mid([Forms]![frmReportCollateral]![cbAge], Instr(1, [Forms]![frmReportCollateral]![cbAge], "-") + 1))

 

[boblarson]

The Eval() function will return a string so it won't read the operator as an operator.

What about:

BETWEEN Val([Forms]![frmReportCollateral]![cbAge]) AND Val(Mid([Forms]![frmReportCollateral]![cbAge], Instr(1, [Forms]![frmReportCollateral]![cbAge], "-") + 1))

If the actual input being typed into the text box includes the operators, then it needs to be done up in code. If a change is made to make the input just the starting and ending number then between will work. But if they want to be able to select the operator involved then code is the way it will need to be done.

 

[vbaInet]

Righto Bob!

@AUGuy: Also note that the BETWEEN code will fail if any selection is not in this format --> "60-119".