Query result wrong when field is 0 (1 Viewer)

[slhangen]

Have query:
ESSA On Track: IIf([PASSAGE#]<5 Or [FAILURE#- ESSA]>1,"No","Yes")

But when [PASSAGE#] is 0, I get Yes.

What am I missing?

Thx Team!

 

[plog]

When [PASSAGE#] is 0 and the result is "Yes" it means [FAILURE#- ESSA] is greater than 1.


A OR B:
A is true and B is true then the result is true
A is true and B is False then the result is true
A is false and B is true then the result is true
A is false and B is false then the result is false

A AND B:
A is true and B is true then the result is true
A is true and B is False then the result is false
A is false and B is true then the result is false
A is false and B is false then the result is false

Also, you should name your fields better. Only use alphanumeric characters and underscores. Otherwise you will always have to surround them with brackets. Just makes coding and querying easier.

 

[slhangen]

Thank you for your reply. This query works every time except when Passage# is 0.

If Passage# is 4,3,2, or 1 I get correct result.
I think the 0 is throwing the formula off. Is there something I need to do for the 0 to get the correct result?

 

[plog]

What is the [FAILURE#- ESSA] in those instances?

Also is [PASSAGE#] a numeric or string datatype in its table?

 

[CJ_London]

No example data with outcome required so just guessing your OR should be an AND

or perhaps [FAILURE#- ESSA] should be [FAILURE#] - [ESSA]

 

[Gasman]

Thank you for your reply. This query works every time except when Passage# is 0.

If Passage# is 4,3,2, or 1 I get correct result.
I think the 0 is throwing the formula off. Is there something I need to do for the 0 to get the correct result?

So on those occasions essa is not > 1 ?

 

[ebs17]

There are also visibly 0, not empty fields (NULL content), which you interpret as 0?

 

[slhangen]

SELECT [00STUDENT INFORMATION].[Other ID], [00STUDENT INFORMATION].[Student Last Name], [00STUDENT INFORMATION].[Student First Name], IIf([PASSAGE# ] Is Null,0,[PASSAGE# ]) AS [COURSE PASSAGE#], IIf([FAILURE#- ESSA] Is Null,0,[FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA], IIf([PASSAGE#]<5 Or [FAILURE#- ESSA]>1,"No","Yes") AS [ESSA On Track]
FROM ([00STUDENT INFORMATION] LEFT JOIN [FAILURE COUNT- ESSA] ON [00STUDENT INFORMATION].[Other ID] = [FAILURE COUNT- ESSA].[Other ID]) LEFT JOIN [PASSAGE COUNT- TOTAL] ON [00STUDENT INFORMATION].[Other ID] = [PASSAGE COUNT- TOTAL].[Other ID]
ORDER BY [00STUDENT INFORMATION].[Student Last Name], [00STUDENT INFORMATION].[Student First Name];

 

[slhangen]

There are also visibly 0, not empty fields (NULL content), which you interpret as 0?

Please see below for code and screenshot. Thx

 

[ebs17]

Your query presented legibly:

SELECT
   [00STUDENT INFORMATION].[Other ID],
   [00STUDENT INFORMATION].[Student Last Name],
   [00STUDENT INFORMATION].[Student First Name],
   IIf([PASSAGE# ] Is Null, 0, [PASSAGE# ]) AS [COURSE PASSAGE#],
   IIf([FAILURE#- ESSA] Is Null, 0, [FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA],
   IIf([PASSAGE#] < 5
   OR [FAILURE#- ESSA] > 1, "No", "Yes") AS [ESSA On Track]
FROM
   ([00STUDENT INFORMATION]
      LEFT JOIN [FAILURE COUNT- ESSA]
      ON [00STUDENT INFORMATION].[Other ID] = [FAILURE COUNT- ESSA].[Other ID]
   )
   LEFT JOIN [PASSAGE COUNT- TOTAL]
   ON [00STUDENT INFORMATION].[Other ID] = [PASSAGE COUNT- TOTAL].[Other ID]
ORDER BY
   [00STUDENT INFORMATION].[Student Last Name],
   [00STUDENT INFORMATION].[Student First Name]

There is massive NULL treatment in the fourth and fifth columns. The sixth column is independent, why do you think you can do without handling NULL there.
The content used probably comes from those tables that are connected with LEFT JOIN, where if there is no content in these query fields, it will automatically be NULL.

Use something like Nz([PASSAGE# ], 0). Then the replacement value 0 is delivered immediately at NULL.

 

[Pat Hartman]

Do you have a format on passage that shows 0 when it is null? That is the only explanation.

 

[slhangen]

Still not working,

 

[slhangen]

Your query presented legibly:

SELECT
   [00STUDENT INFORMATION].[Other ID],
   [00STUDENT INFORMATION].[Student Last Name],
   [00STUDENT INFORMATION].[Student First Name],
   IIf([PASSAGE# ] Is Null, 0, [PASSAGE# ]) AS [COURSE PASSAGE#],
   IIf([FAILURE#- ESSA] Is Null, 0, [FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA],
   IIf([PASSAGE#] < 5
   OR [FAILURE#- ESSA] > 1, "No", "Yes") AS [ESSA On Track]
FROM
   ([00STUDENT INFORMATION]
      LEFT JOIN [FAILURE COUNT- ESSA]
      ON [00STUDENT INFORMATION].[Other ID] = [FAILURE COUNT- ESSA].[Other ID]
   )
   LEFT JOIN [PASSAGE COUNT- TOTAL]
   ON [00STUDENT INFORMATION].[Other ID] = [PASSAGE COUNT- TOTAL].[Other ID]
ORDER BY
   [00STUDENT INFORMATION].[Student Last Name],
   [00STUDENT INFORMATION].[Student First Name]

There is massive NULL treatment in the fourth and fifth columns. The sixth column is independent, why do you think you can do without handling NULL there.
The content used probably comes from those tables that are connected with LEFT JOIN, where if there is no content in these query fields, it will automatically be NULL.

Use something like Nz([PASSAGE# ], 0). Then the replacement value 0 is delivered immediately at NULL.

Used the Nz formula and still getting wrong result. Scratching my head over this.

 

[ebs17]

Used the Nz formula and still getting wrong result. Scratching my head over this.

Instead of quoting in full, you should show your specific attempt.

 

[The_Doc_Man]

Your query doesn't tell us where PASSAGE# and FAILURE# - ESSA and FAILURE# originate. When dealing with a LEFT JOIN (vs. INNER JOIN) it is possible for you to legitimately get back NULLS that will poison ANY EXPRESSION that encounters them. But it gets worse. In a couple of places you test whether something is null, but you don't test for null every place you use those items. In a query, particularly within a SELECT clause, EVERY USE of any field must be properly qualified EVERY TIME YOU USE IT! If a NULL has snuck in via PASSAGE#, FAILURE#, or FAILURE# - ESSA (because of the LEFT JOINs), your expressions will fail to work as expected. EBS17 was trying to tell you that in post #10 but I can be a bit more direct (seen by some as being blunt) sometimes. Since we don't know your table structure for this query, we have no way of knowing where each element originates.

I don't recall seeing another discussion on this, but you are making a LOT of work for yourself by using field names with special characters in them, as well as names with embedded spaces. I'm not going to swear to this, but I think when you use the [] construct because of a field name with special characters and spaces, the placement and number of spaces is also important. In fact, since space is a carriage control character but not a visible character, it would be hard to count spaces by eye in every use where you have a dashed name.

Which leads me to the next question. Do you have error reporting turned off for this? Because there would be a gazillion places where I would not be surprised to see that you have a missing name or some other defect in the query with those names. When developing code, ALWAYS have error reporting turned on. Turn it off for production - though actually, if you get it debugged, you wouldn't NEED to turn it off.

 

[CJ_London]

Used the Nz formula and still getting wrong result. Scratching my head over this.

Rather than simply quoting what someone else has done to make your code more legible it would be much more helpful to show the code you are actually using since ‘wrong result’ could mean anything

you make it very difficult for others to help you. This will turn off many who do not want to waste their time constantly seeking clarification. Up to you but we all give our time freely but would rather help someone who helps us to help them

 

[slhangen]

Rather than simply quoting what someone else has done to make your code more legible it would be much more helpful to show the code you are actually using since ‘wrong result’ could mean anything

you make it very difficult for others to help you. This will turn off many who do not want to waste their time constantly seeking clarification. Up to you but we all give our time freely but would rather help someone who helps us to help them

 

[slhangen]

My apologies for the confusion. The code was listed in original post. Here it is again:

SELECT [00STUDENT INFORMATION].[Other ID], [00STUDENT INFORMATION].[Student Last Name], [00STUDENT INFORMATION].[Student First Name], IIf([PASSAGE# ] Is Null,0,[PASSAGE# ])

AS [COURSE PASSAGE#], IIf([FAILURE#- ESSA] Is Null,0,[FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA], IIf([PASSAGE#]<5 Or [FAILURE#- ESSA]>1,"No","Yes") AS [ESSA On Track]
FROM ([00STUDENT INFORMATION]

LEFT JOIN [FAILURE COUNT- ESSA] ON [00STUDENT INFORMATION].[Other ID] = [FAILURE COUNT- ESSA].[Other ID]) LEFT JOIN [PASSAGE COUNT- TOTAL] ON [00STUDENT INFORMATION].[Other ID] = [PASSAGE COUNT- TOTAL].[Other ID]

ORDER BY [00STUDENT INFORMATION].[Student Last Name], [00STUDENT INFORMATION].[Student First Name];

 

[cheekybuddha]

Still not working,

SELECT
  si.[Other ID],
  si.[Student Last Name],
  si.[Student First Name],
  Nz([PASSAGE# ], 0) AS [COURSE PASSAGE#],
  Nz([FAILURE#- ESSA], 0) AS [COURES FAILURE#- ESSA],
  IIf(
    Nz([PASSAGE# ], 0) < 5 OR Nz([FAILURE#- ESSA], 0) > 1,
    'No',
    'Yes'
  ) AS [ESSA On Track]
FROM (
  [00STUDENT INFORMATION] si
  LEFT JOIN [FAILURE COUNT- ESSA] fc
         ON si.[Other ID] = fc.[Other ID]
)
LEFT JOIN [PASSAGE COUNT- TOTAL] pc
       ON si.[Other ID] = pc.[Other ID]
ORDER BY
   si.[Student Last Name],
   si.[Student First Name]
;

 

[cheekybuddha]

Do you have a format on passage that shows 0 when it is null? That is the only explanation.

Correct.

SELECT
   -- ...,
   IIf([PASSAGE# ] Is Null, 0, [PASSAGE# ]) AS [COURSE PASSAGE#],
   IIf([FAILURE#- ESSA] Is Null, 0, [FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA],
   -- ...