So on those occasions essa is not > 1 ?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?
Please see below for code and screenshot. ThxThere are also visibly 0, not empty fields (NULL content), which you interpret as 0?
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]
Used the Nz formula and still getting wrong result. Scratching my head over this.Your query presented legibly:
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.SQL: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]
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.
Instead of quoting in full, you should show your specific attempt.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 anythingUsed 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
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]
;
Correct.Do you have a format on passage that shows 0 when it is null? That is the only explanation.
SELECT
-- ...,
IIf([PASSAGE# ] Is Null, 0, [PASSAGE# ]) AS [COURSE PASSAGE#],
IIf([FAILURE#- ESSA] Is Null, 0, [FAILURE#- ESSA]) AS [COURES FAILURE#- ESSA],
-- ...