By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Using Dlookup to generate an IIf statement

P: 2
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'.
Jun 14 '07 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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]").
Jun 15 '07 #2

P: 2
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.
Jun 15 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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]",""))
Jun 15 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
Try it this way:

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

also, try this:

Eval(CStr(SQL_String))
Jun 15 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.