Using Dlookup to generate an IIf statement

[Mark1978]

Hi All

Apologies if someone has asked this question before, but I have hunted high and low for the solution.

I am relatively new to using access so am not sure if this is possible. I am using MS Access 2003.

I want to use the result of one query as an IIf statement in a calculated field in a new query.

I have tried using dlookup to do this but the result is just the IIf statement posted as a string of text. e.g;

Expr1: Dlookup("[SQL_String]","QRY_CONCAT_SQL_STRING")

Returns

=IIF([PEAK MONTH TOTE CONSUMPTION] BETWEEN 0 AND 1.99, '1A', '1B')

As a text string.

If I pick up the result of SQL_String an use it as an expression in a field it returns what I am after i.e. the result is '1A' or '1b'.

[puppydogbuddy]

Hi All

Apologies if someone has asked this question before, but I have hunted high and low for the solution.

I am relatively new to using access so am not sure if this is possible. I am using MS Access 2003.

I want to use the result of one query as an IIf statement in a calculated field in a new query.

I have tried using dlookup to do this but the result is just the IIf statement posted as a string of text. e.g;

Expr1: Dlookup("[SQL_String]","QRY_CONCAT_SQL_STRING")

Returns

=IIF([PEAK MONTH TOTE CONSUMPTION] BETWEEN 0 AND 1.99, '1A', '1B')

As a text string.

If I pick up the result of SQL_String an use it as an expression in a field it returns what I am after i.e. the result is '1A' or '1b'.

If I understand you correctly, the DLookup on the SQL_String is returning the expression for the SQL string and you are looking for a way to return the result of the expression. Try the Eval function and see if that does what you want.

Eval("[SQL_String]").

[Mark1978]

Good idea it did not occur to me to try that, unfortunately I get the message;

"The expression is typed incorrectly, or is too complex to be evaluated....."

I know its not the former so I am obviously trying to evaluate too much.

Thanks for the advice though.

[puppydogbuddy]

Good idea it did not occur to me to try that, unfortunately I get the message;

"The expression is typed incorrectly, or is too complex to be evaluated....."

I know its not the former so I am obviously trying to evaluate too much.

Thanks for the advice though.

Try it this way:

Eval(nz("[SQL_String]",""))

[puppydogbuddy]

Try it this way:

Eval(nz("[SQL_String]",""))

also, try this:

Eval(CStr(SQL_String))