NULLS Affecting Embedded Query Calculations

[LoopyNZ]

I'm sure this has something to do with NULLS, but I just can't get my
head around this behaviour. More importantly, I can't SOLVE this
behaviour, and until I do I can't have any confidence in my data!

- Specs: Access 2007, Windows XP, data in linked (mySQL) table.

- I have a text (varchar) field called [HighestEducation] that
contains NULL values.

- I have a calculated field [HighestEducation_Valid] to check the
quality of the contents of the text field. The formula is:

IIf(IsNull([HighestEducation]),"No
Response",IIf([HighestEducation]="","Invalid/Incomplete","Complete"))

In my query results (datasheet), the contents of
[HighestEducation_Valid] are:
--- 287 COMPLETE
--- 1 INVALID/INCOMPLETE
--- 301 NO RESPONSE.

My problem is that when I use this query in a *subsequent* query (e.g.
SELECT [HighestEducation_Valid], COUNT(myID) FROM myQuery GROUP BY
[HighestEducation_Valid], I get:
--- 287 COMPLETE
--- 302 INVALID/INCOMPLETE.

(Other notes:
- I've also tried IIf([HighestEducation] Is Null... but the behaviour
was unchanged.
- This is all happening in vanilla queries, no VBA involved at all.)

Any ideas?

Many thanks,
- Linda Patterson
- Dunedin, New Zealand

 

[Dale Fye]

Loopy,

Nested IIFs can be a problem, especially with data linked from other ODBC
data sources. I'm not familiar with MySQL so this will just be a guess.

Have you tried inverting the first two IIFs?

IIF([HighestEducation] = "", "Invalid/Incomplete", IIF([HighestEducation] IS
NULL, "No Response", "Complete"))

Other than that, I don't have a clue.

HTH
Dale

 

[LoopyNZ]

Loopy,

Nested IIFs can be a problem, especially with data linked from other ODBC
data sources. I'm not familiar with MySQL so this will just be a guess.

Have you tried inverting the first two IIFs?

IIF([HighestEducation] = "", "Invalid/Incomplete", IIF([HighestEducation] IS
NULL, "No Response", "Complete"))

Other than that, I don't have a clue.

HTH
Dale

I'm sure this has something to do with NULLS, but I just can't get my
head around this behaviour. More importantly, I can't SOLVE this
behaviour, and until I do I can't have any confidence in my data!

- Specs: Access 2007, Windows XP, data in linked (mySQL) table.

- I have a text (varchar) field called [HighestEducation] that
contains NULL values.

- I have a calculated field [HighestEducation_Valid] to check the
quality of the contents of the text field. The formula is:

IIf(IsNull([HighestEducation]),"No
Response",IIf([HighestEducation]="","Invalid/Incomplete","Complete"))

In my query results (datasheet), the contents of
[HighestEducation_Valid] are:
--- 287 COMPLETE
--- 1 INVALID/INCOMPLETE
--- 301 NO RESPONSE.

My problem is that when I use this query in a *subsequent* query (e.g.
SELECT [HighestEducation_Valid], COUNT(myID) FROM myQuery GROUP BY
[HighestEducation_Valid], I get:
--- 287 COMPLETE
--- 302 INVALID/INCOMPLETE.

(Other notes:
- I've also tried IIf([HighestEducation] Is Null... but the behaviour
was unchanged.
- This is all happening in vanilla queries, no VBA involved at all.)

Any ideas?

Many thanks,
- Linda Patterson
- Dunedin, New Zealand

Thanks for the suggestion, Dale. I've just tried it out and it doesn't
seem to have helped.

- Linda