Access 2003 treats embedded functions in nested queries as a reference rather than a value - Access

[jgfaughnan]

I've run into a curious behavior with Access 2003. I would like to
know if Access 2007 handles things differently. I have to provide a
bit of background before I can describe the problem. In this context
I'm using Access 2003 as a tool to analyze and manipulate string sets.

BACKGROUND:
Microsoft Access 2003 allows one to embed "expressions" in the query
design form. I believe these "expressions" are drawn from Access Basic
(VBA Access).

I often make use of the "Built-In Functions" "Program Flow" "IIf" and
"Switch" functions. For example:

IIf([my_name] is Null and [my_birthday] is not
Null,"IS_BAD","IS_GOOD")

I also make extensive use of queries that are built from queries,
sometimes working with five to six layers of queries.

PROBLEM:

Typically the IIf and Switch functions I use return strings. Access
displays these strings in the query result, but it turns out that it
does not store the results as strings. I can see this when I embed a
query that uses these functions in another query. The strings that now
appears seem nonsensical, but it turns out that Access is simply
executing the original IIf/Switch function in the new (Container)
query.

This produces some undesirable behaviors for my work. I'd much rather
Access was passing a string to the container query rather than a
function. I wonder if this is a side-effect of embedding VBA behavior
into a relational database environment.

QUESTIONS:

Has this behavior changed in Microsoft Access 2007? Are there any
known workarounds?

Thanks! (I'm not sure anyone will have an answer for this, but when I
get a chance to test in 2007 I'll post my results as a response to
this query.)

(e-mail address removed)

tags: jfaughnan, jgfaughnan, 070605, microsoft access 2003, query,
embedded function, reference, return, value

 

[Michel Walsh]

The behavior did not change.

Make a make table query (or an update query) out of the query that COMPUTES
these expression, that will force the computation to be somehow written in
the table you created (or updating a column).

I assume you have computed expression, NOT AUTOMATIC lookup. If you have
automatic lookup, that is a matter to add it where it does not displayed the
'lookup' strings, or to use a join to explicitly make the 'lookup'. But from
what you describe, that is unlikely the case. I just mention it 'in case'.

Hoping it may help,
Vanderghast, Access MVP

 

[jgfaughnan]

Thanks for letting me know the behavior didn't change. I don't can't
embed an update query in my densely nested queries so I'd have to
split the work into that which creates the intermediate table then
those that use the intermediate table. In fact, in some of my work,
I'd have to create multiple instances of intermediate tables.

Alas, I'm probably one of 10 people in the universe that uses Access
in this way. If there were more of us there might be a case for
Microsoft changing this behavior to 'include by value' in a future
release. I suspect the current behavior has no advantages, though it
works if one doesn't nest the query and thus recalculate the result
out of context.

john faughnan
(e-mail address removed)
tags: jfaughnan, jgfaughnan, 070605, microsoft access 2003, query,
embedded function, reference, return, value

The behavior did not change.

Make a make table query (or an update query) out of the query that COMPUTES
these expression, that will force the computation to be somehow written in
the table you created (or updating a column).

I assume you have computed expression, NOT AUTOMATIC lookup. If you have
automatic lookup, that is a matter to add it where it does not displayed the
'lookup' strings, or to use a join to explicitly make the 'lookup'. But from
what you describe, that is unlikely the case. I just mention it 'in case'.

Hoping it may help,
Vanderghast, Access MVP

I've run into a curious behavior with Access 2003. I would like to
know if Access 2007 handles things differently. I have to provide a
bit of background before I can describe the problem. In this context
I'm using Access 2003 as a tool to analyze and manipulate string sets.

BACKGROUND:
Microsoft Access 2003 allows one to embed "expressions" in the query
design form. I believe these "expressions" are drawn from Access Basic
(VBA Access).

I often make use of the "Built-In Functions" "Program Flow" "IIf" and
"Switch" functions. For example:

IIf([my_name] is Null and [my_birthday] is not
Null,"IS_BAD","IS_GOOD")

I also make extensive use of queries that are built from queries,
sometimes working with five to six layers of queries.

Typically the IIf and Switch functions I use return strings. Access
displays these strings in the query result, but it turns out that it
does not store the results as strings. I can see this when I embed a
query that uses these functions in another query. The strings that now
appears seem nonsensical, but it turns out that Access is simply
executing the original IIf/Switch function in the new (Container)
query.

This produces some undesirable behaviors for my work. I'd much rather
Access was passing a string to the container query rather than a
function. I wonder if this is a side-effect of embedding VBA behavior
into a relational database environment.

Has this behavior changed in Microsoft Access 2007? Are there any
known workarounds?

Thanks! (I'm not sure anyone will have an answer for this, but when I
get a chance to test in 2007 I'll post my results as a response to
this query.)

tags:jfaughnan, jgfaughnan, 070605, microsoft access 2003, query,
embedded function, reference, return, value