Results 1 to 7 of 7
  1. #1
    AKQTS is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2010
    Posts
    56

    Setting Blank Fields to "N/A"

    In the report I am creating I would like to set the Blank or Null fields for a text box to display "N/A". I cannot seem to find some sort of a default setting and I would not like to do this in the Form for certain reasons. Thanks

  2. #2
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105
    IIF([field] is null, "N/A", [field])

    where field is the column that would have a null value

  3. #3
    AKQTS is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2010
    Posts
    56
    Thanks, but I keep getting an invalid control property error and a circular reference error. My exact code is below:
    =IIf([Note] Is Null,"N/A",[Note])

    Anymore suggestions?

  4. #4
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105
    I don't know...that's exactly what I have in the text box and it works for me just fine

  5. #5
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Note is a reserved word in Access. Try changing it's name and see if that works.

  6. #6
    AKQTS is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2010
    Posts
    56
    Thanks for the responses, but I solved it. Instead of doing this in my Control Source, I moved the function to the underlying report query. Works great and I adapted it for other fields.

  7. #7
    l3111 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    38
    Hi I noticed this thread was marked as solved, but i've tried your solution and it hasn't worked

    I have a database where customers join a programme of home care. The programme is intended to start on the same date as the referral, however for many reasons the programme can be be delayed. Under these circumstances, a referral data AND a start date is entered. A reason from delayed programme start date is meant to be entered as well.

    I have created a query which lists all the programme start dates and the reasons, and thn a report which groups by reasons. however there are a large proportion of entries where no reason has been chosen. In my report, this section has no heading, and is just a floating number.

    I entered your formula in the underlying query but all it did was remove the blank entries and only counted the entries which had a reason entered therefore providing a false representation of all delays.

    here is the formula I entered in my query:
    IIf([ReasonForDelayedProgrammeStart] Is Null,"No Reason Given",[ReasonForDelayedProgrammeStart])

    Any suggestions please?

    Many thanks
    l3111, Manchester, England

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

Similar Threads

  1. Replies: 4
    Last Post: 06-14-2010, 02:31 PM
  2. Replies: 8
    Last Post: 02-24-2010, 01:49 PM
  3. Fields do not show in "Form View"
    By hawzmolly in forum Forms
    Replies: 4
    Last Post: 01-09-2010, 06:27 PM
  4. Link tables on "special" fields
    By Gargen in forum Access
    Replies: 0
    Last Post: 12-18-2008, 12:02 PM
  5. Replies: 1
    Last Post: 09-05-2008, 12:07 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