Microsoft access: Is null criteria gives Invalid use of null

Question

Hi,

Im starting to lose hair from my head from this error. We have a query that should pull info from two databases and then put in some values if one field is null. It has been working fine until now when we updated a program that had one of the databases that is now a BigInt instead of Int.

The Query looks like this and both OOF and kundres_local are bigint on the design view. But at first the kundres_local was just Int and we now changed to bigint

INSERT INTO kundres_local ( invoice_number, Reminder )
SELECT OOF.DOKNR, -1 AS Uttryck1
FROM OOF LEFT JOIN kundres_local ON OOF.DOKNR = kundres_local.invoice_number
WHERE (((OOF.SALDO_KR)>0) AND ((Date()-CDate([Datum2]))>0) AND ((kundres_local.invoice_number) Is Null));

Answer 1

If the query was working before but not now then I would wager a schema change broke things. You mentioned OOF and kundres_local are bigint but these are tables so I assume you mean that OOF.DOKNR and kundres_local.invoice_number are BIGINTs. That shouldn't change the query itself as you are doing a left join so you are basically just getting all the rows in OOF that aren't also in kundres_local plus some extra conditions.

Can you provide the subset of the table definitions for the above tables?

Answer 2

Try this:

INSERT INTO kundres_local ( invoice_number, Reminder )
SELECT OOF.DOKNR, -1 AS Uttryck1
FROM OOF LEFT JOIN kundres_local ON OOF.DOKNR = kundres_local.invoice_number
WHERE (((OOF.SALDO_KR)>0) AND (DATEDIFF(DAY, [Datum2], GETDATE())>0) AND ((kundres_local.invoice_number) Is Null));