Almost identical queries, one says invalid use of null

[jrg24]

Both are pulling from the same table, but from different columns. Can you help me find the issue?



This is working fine:

SELECT [BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 1] AS Carrier,
CCur([BusinessActivity].[Proj Amt 1]) AS Amount, 
[BusinessActivity].[Type 1] As Type, 
Exp1 AS [Exp #]
 FROM [BusinessActivity] 
WHERE [Proj amt 1] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 2] AS Carrier,
CCur([BusinessActivity].[Proj Amt 2]) AS Amount, 
[BusinessActivity].[Type 2] As Type, 
Exp2 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 2] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 3] AS Carrier, 
CCur([BusinessActivity].[Proj Amt 3]) AS Amount, 
[BusinessActivity].[Type 3] As Type, 
Exp3 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 3] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."




UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 4] AS Carrier, 
CCur([BusinessActivity].[Proj Amt 4]) AS Amount, 
[BusinessActivity].[Type 4] As Type, 
Exp4 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 4] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."




UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 5] AS Carrier, 
CCur([BusinessActivity].[Proj Amt 5]) AS Amount, 
[BusinessActivity].[Type 5] As Type, 
Exp5 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 5] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 6] AS Carrier, 
CCur([BusinessActivity].[Proj Amt 6]) AS Amount, 
[BusinessActivity].[Type 6] As Type, 
Exp6 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 6] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 7] AS Carrier,
CCur([BusinessActivity].[Proj Amt 7]) AS Amount,
 [BusinessActivity].[Type 7] As Type, 
Exp7 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 7] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Proj Exp 8] AS Carrier, 
CCur([BusinessActivity].[Proj Amt 8]) AS Amount, 
[BusinessActivity].[Type 8] As Type, 
Exp8 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Proj amt 8] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO.";

This is telling me invalid use of null:

SELECT [BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 1] AS Type,
CCur([BusinessActivity].[Acc Charge 1]) AS Amount, 
Exp1 AS [Exp #]
 FROM [BusinessActivity] 
WHERE [Acc Charge 1] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 2] AS Type,
CCur([BusinessActivity].[Acc Charge 2]) AS Amount, 
 "Exp2" AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 2] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 3] AS Type, 
CCur([BusinessActivity].[Acc Charge 3]) AS Amount, 
 "Exp3" AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 3] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."




UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 4] AS Type, 
CCur([BusinessActivity].[Acc Charge 4]) AS Amount, 
Exp4 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 4] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."




UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 5] AS Type, 
CCur([BusinessActivity].[Acc Charge 5]) AS Amount, 
 "Exp5" AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 5] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 6] AS Type, 
CCur([BusinessActivity].[Acc Charge 6]) AS Amount, 
Exp6 AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 6] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 7] AS Type,
CCur([BusinessActivity].[Acc Charge 7]) AS Amount,
 "Exp7" AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 7] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO."


UNION ALL 


SELECT 
[BusinessActivity].[Pro #] AS PRO, 
[BusinessActivity].[Acc 8] AS Type, 
CCur([BusinessActivity].[Acc Charge 8]) AS Amount, 
 "Exp8" AS [Exp #]
FROM [BusinessActivity] 
WHERE [Acc Charge 8] IS NOT Null 
AND BusinessActivity.[Customer Name]="Daily Direct" OR BusinessActivity.[Customer Name]="Walter Bavison - WS LTL" OR BusinessActivity.[Customer Name]="Walter-Bavison" OR BusinessActivity.[Customer Name]="Walter-Bavison / ABCD GLOBAL" OR BusinessActivity.[Customer Name]="Walter-Bavison BOAT CO.";

[June7]

All number conversion functions will error if they encounter Null. Handle possible null:

CCur(Nz([Acc Charge 3],0) AS Amount

Or find the records with null and input a value.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[jrg24]

Thanks. I agree about the special characters and thought I had renamed most of the fields to not contain them (My source data is a linked table that comes from our sales system). I will correct that. I thought that

WHERE [Acc Charge 5] is not null

would have excluded all null? The same conversion happens on both tables but the first one works. In fact, the one that does not work was just a copy and paste job with some field names changed for the most part. I will try out the NZ function. Thanks again.

[June7]

Didn't notice the WHERE clauses. Yes, would think that would eliminate the nulls.

Why use CCur in query? Is this query used as report RecordSource? Can use textbox formatting.

[lfpm062010]

Not sure if this is your issue.

The only difference I saw is the use of
--> Exp1 AS [Exp #]
--> "Exp2" AS [Exp #]
--> "Exp3" AS [Exp #]
--> Exp4 AS [Exp #]
--> "Exp5" AS [Exp #]
--> Exp6 AS [Exp #]
--> "Exp7" AS [Exp #]
--> "Exp8" AS [Exp #]

In the first query, they are not quoted. In the second query, they are mixed. Not sure if that is cause of your issue.

[June7]

Good eye, lfpm. That certainly would be an issue.

If Exp1, Exp2, etc is intended to be descriptive text under constructed field [Exp #] (special character!), then certainly need quote marks and then I don't see how the first query works.