Results 1 to 5 of 5
  1. #1
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340

    Problem designing Update Query with null field

    UPDATE Tracks SET Tracks.AlbumName = "Mama Tried" WHERE ((Tracks.Cat)=572 AND (Tracks.Track)=3 AND (Tracks.Disk)= );



    The problem is Tracks.Disk is a numeric field and is sometimes a number but other times a null.
    If I use Nz function it becomes a string but then my query crashes if quotes are used.

    I'm sure there must be a solution bur I can't quite work it out.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not clear what you want to happen but a number of ways to handle nulls

    Nz(myfield,0) will return a zero not a zls

    or use isnull(myfield)

    or for sql you can use

    myfield is null

  3. #3
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    Sorry not explaining better. I only want to update where the disk field is the same.
    But if it's null it doesn't have anything to show and the query reads AND (Tracks.Disk)= );
    If not null all is ok AND (Tracks.Disk)=4);
    AND (Tracks.Disk)=""); doesn't work
    Should I make all the Disk fields = 0 instead of null ?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    seems to me you have hard coded your criteria so I don’t understand what ‘the same’ means

  5. #5
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    It's a datasheet Form and I want Update only where the Disk field is the same value as the current record.
    I've since removed all nulls and it's all working as wanted.
    Again, sorry if I didn't explain it too well and thanks for helping.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2022, 06:16 PM
  2. Replies: 8
    Last Post: 09-22-2015, 02:59 PM
  3. Replies: 1
    Last Post: 03-10-2015, 11:08 AM
  4. Update query to set null value lookup field
    By alexjose in forum Access
    Replies: 1
    Last Post: 06-24-2013, 04:26 PM
  5. Update field1 if Null, if Not Null update field2
    By mfirestorm in forum Queries
    Replies: 2
    Last Post: 12-02-2011, 09:51 AM

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