Results 1 to 4 of 4
  1. #1
    Christopher_ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    2

    Microsoft access: Is null criteria gives Invalid use of null

    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));

    Any ideas?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Do you need a BigInt ? Normal number value in Access is a Long that can handle a range of -2^31 to 2^31-1

    Anyway to your problem, if Access isn't set to use Big Int values it will see the number as a text field which will give you an error as that can't be null.

    Look at the settings for using large numbers
    https://support.microsoft.com/en-us/office/using-the-large-number-data-type-5b623f6e-641d-4e97-8bdf-b77bae076f70
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Christopher_ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    2
    The issue is now solved, found null values in datum2 and that was giving the issue not the invoicenumber part. Correct and working is now
    WHERE (OOF.SALDO_KR >0) AND IIf(Datum2IsNull,True,(Date()-CDate(Datum2)>0)) AND (kundres_local.invoice_number IsNull)

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Glad you have solved it!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-07-2019, 10:46 AM
  2. Replies: 4
    Last Post: 04-09-2019, 02:05 PM
  3. Access dlookup invalid use of null
    By cuddihy1990 in forum Programming
    Replies: 4
    Last Post: 06-25-2014, 03:38 PM
  4. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  5. Replies: 1
    Last Post: 02-23-2012, 02:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums