Results 1 to 9 of 9
  1. #1
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55

    Angry Nz() function not working in Access 2010 on Text field

    I have a text field that has NULL values and I am trying to make the query return N/A when the value is NULL. Here is the expression I am using in the query:

    AttName: Nz([NameField],"N/A")



    It is not returning N/A. All I see is a blank and I am trying to compare it to another field where the value is N/A and the query is failing. What could be causing this? I also tried IIf(IsNull([NameField],"N/A",[NameField]) and that fails too which leads me to believe that it is something with the field type itself.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    chances are you field is not null but a zero length string try

    AttName: iif(Nz([NameField],"")="","N/A",[NameField])

  3. #3
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    Yes, that worked. Is there something I can do to fix the field?

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in the table design for that field, change the Allow Zero Length property to No and ensure nothing is specified in the default value- you'll probably need to run a query to remove any zero length strings before applying this change.

    Note that having null strings can introduce other issues when manipulating data so you may want to consider using the format property - see this link

    https://support.office.com/en-us/art...rs=en-US&ad=US

    put

    @;"N/A"

    in the format property in either your table or query and see if this works for you

    I use this on forms quite a bit

    @;[Red]"This field must be completed"

    as a visual reminder to users - you still need to have suitable routines to ensure users cannot ignore it however - but they have been warned!

  5. #5
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    The data is coming in from an Excel file and I tried to change Allow Zero Length to No, but that didn't fix it. How can I run a query to remove the zero length strings?

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    update mytable set myfield=null

    If your table is linked to excel then this won't work

  7. #7
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    It's not linked. I import the data from the file. Isn't that query missing something? That will set all values to NULL, correct? I think it needs to look like this:

    UPDATE mytable SET myfield= "N/A"
    WHERE (((myfield)=" "));

  8. #8
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    whatever rocks your boat!

  9. #9
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76

    Returning Null Values

    Quote Originally Posted by Ajax View Post
    whatever rocks your boat!
    I have just spent an hour trying to solve the same issue, your solution worked great

    Dave

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

Similar Threads

  1. Replies: 1
    Last Post: 12-27-2014, 12:38 PM
  2. Replies: 6
    Last Post: 08-13-2014, 10:32 PM
  3. Replies: 2
    Last Post: 02-11-2014, 11:05 PM
  4. Replies: 5
    Last Post: 05-22-2013, 12:20 PM
  5. Replies: 2
    Last Post: 03-30-2012, 07:39 AM

Tags for this Thread

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