how to correct Invalid use of null query

R

RSW1127

We recently upgraded to Access 2002-2003, and this query used to work. It now
returns 'Invalid use of null"

INSERT INTO [Agency Payee List] ( ACCOUNT, LOCATION, NAME, CODE9997cur )
SELECT DISTINCT payout.CKPAYEE, payout.CKPAYEELOC, payout.CKAGYNAME,
payout.CKFEDLOC
FROM payout LEFT JOIN [Agency Payee List] ON (payout.CKPAYEELOC=clng([Agency
Payee List].LOCATION)) AND (payout.CKPAYEE=clng([Agency Payee List].ACCOUNT))
WHERE (((payout.CKAGYTYP1)=100) And (([Agency Payee List].LOCATION) Is Null));
 
A

Allen Browne

What's with the CLng() ?

That function converts a numeric value to a Long Integer data type. It does
not cope with Null. Your outer join will give nulls where the [Agency Payee
List] table does not have a record matching the payout table. Therefore this
query is guaranteed to fail with some data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with append query, please 1
Duplicating Main Form And Its Subform Detail Records in Access 97 3
Invalid use of null 3
Invalid use of null 4
Invalid Use of Null 1
Invalid use of Null 4
UnMatched Query 2
Size limit to Query statement 1

Top