Query not correctly reading value of control on form

[Guest]

I am having intermittent problems with a set of queries picking up values
correctly from the value of a control on the form from which the query is
being run. I retrieve the value of the unbound control via Query #1, store it
in the control so that it can be used repeatedly for Query #2 without having
to re-run Query #1. The field entry in Query #2 looks like this:

FieldName:[Forms!][FormName]![ControlName]

Something is causing Query #2 to pick up garbage (a little square or a
non-English character), which is wreaking havoc because Query #2 is an append
query, and the field in question is the primary key. If I insert the dlookup
from Query #1 instead of the reference to the control, like this:

FieldNameFirst("[FieldName]","[TableName]")

it works correctly, but it adds a lot of processing overhead to re-run Query
#1 each time.

I thought it might be a timing issue, but even with a For...Next loop
inserted after Query #1 runs, I get the same result. Is there a better way to
accomplish this than storing the value in an unbound control?

 

[Allen Browne]

See:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

The article suggests ways ot help JET understand the data type, including
setting the Format property of the unbound control, and typecasting within
the query.