Query Doesn't Work, Then Works When Copied To New DB?

P

PeteCresswell

Can anybody think of a reason that a query's IIF statement would
evaluate incorrectly when comparing a quotient to a value on a form?

If I hard-code a number like .05 in place of the ref to the form's
field, it works as expected.

Sample app: ftp://www.wchs59.com/BoolTest.mdb
 
A

Allen Browne

I'm not downloading your database, Pete, but there's a fairly good chance
that this has to do with data types.

Access/JET regularly gets confused about the data type of calculated fields,
and you need to craft your expressions to avoid the errors, or explicitly
typecast the fields. See:
http://allenbrowne.com/ser-45.html

This problem can be intermittent, changing depending on how the data is
sorted, or after data is changed, or affected by Name AutoCorrect errors.
 
P

PeteCresswell

Access/JET regularly gets confused about the data type of calculated fields,
and you need to craft your expressions to avoid the errors, or explicitly
typecast the fields. See:
http://allenbrowne.com/ser-45.html
Click to expand...

Thanks for the typecasting insight - I'll be mindful of that in the
future.

But it didn't seem to help in this case.

One more weirdness:
Can you think of any reason the query's rendition of a referebced
value would change depending on whether the value was .Default for the
field or set explicitly in the Immediate window?

In my little sample DB, the results are still wrong, but when I coerce
the referenced field
(call it "txtLimit") to the same value it was defaulted to, the
query's rendition of it changes from the expected .05 (right
justified) to what looks like three Mandarin characters followed by
something that looks like a square zero...

viz:
http://tinyurl.com/5mvk4w
http://tinyurl.com/6brpdw
 
A

Allen Browne

Yes: this is probably a data type error. A binary value interpreted as
Unicode can appear as foreign characters.

Ask Access how it understands the field.
Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("qryDummy").Fields("Limit").Type

The result will be one of the types listed inthe DAO column here:
http://allenbrowne.com/ser-49.html
For example, 9 represents a Binary field.

You need to compare that to what type you expect the field to be.
 
P

PeteCresswell

Yes: this is probably a data type error. A binary value interpreted as
Unicode can appear as foreign characters.

Ask Access how it understands the field.
Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("qryDummy").Fields("Limit").Type
Click to expand...

Bingo! The query was seeing Type 9 (binary as you suspected).

Wrapped the reference in a cDbl() and the numeric returned.

Weird.

I guess I need to add to my list of SOPs the coercion of any query
references (i.e. Limit: forms!frmDummy!txtLimit) to the desired type.

Would you agree?
 
A

Allen Browne

Yes: data types really are crucial.

BTW, if txtLimit is an unbound text box, there are 2 other ways to help
Access understand the data type:
a) Set the Format property of the text box on the form to General Number or
something numeric. That won't fix the query, but it will stop the user
entering non-numeric values in the form.

b) Declare the parameter in your query (Parameters on Query menu.) In the
Parameters dialog, enter something like:
[forms]![frmDummy]![txtLimit] Double

Always worth adding these tricks to the bag, Pete. :)
 
Top