Results 1 to 4 of 4
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Report Text Box Expression Builder to Count Records with Two (2) Blank Fields


    I have a Microsoft Access Table called Daily Door Access Rev B. This table has two Short Text fields – (1) Door No and (2) User No. Both of these fields contain numeric data only, but this is the table as I received it, as it is data imported from Excel.

    Some of the table records are missing data in the Door No field and some are missing data in the User No field. Other records are missing data in both the Door No and User No field.

    I am trying to create a Text Field in a Report Footer which counts the number of records in all three cases. I have been successful in counting the records for No Door No and No User No.

    However, I have not been successful in writing a Text Box build statement which will count the records with both no Door No AND No User No.

    I have a query which shows what the result of this text box should be, but I would like to verify this with a text box build statement. The query is shown in the attached PDF file.

    Can you tell me what is wrong with my Text Box build statement, right now it is counting all of the records in the table.

    =Count(IIf([Daily Door Access RevB]![Door No] And [Daily Door Access RevB]![User No]=" ",1,0))

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As a guess, try comparing to "" (empty string) rather than " " (space).
    If that is unsuccessful, try IsNull(DoorNo) and IsNull([User No])

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You cannot code like you think or speak. With AND's or OR's or any other comparison that which you are comparing must be included in ALL comparisons. However, you have posted " " which is a single blank space whereas I'm thinking you want a zero length string ( "" ) so that's what I'll post.

    (IIf([Daily Door Access RevB]![Door No] = "" And [Daily Door Access RevB]![User No]=""
    Last edited by Micron; 01-17-2023 at 02:31 PM. Reason: spelin & gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thanks for your help it gave me the correct results to agree with my query.


    Nbr of Records User No AND Door No is Blank

    =Count(IIf([Daily Door Access RevB]![Door No]="" And [Daily Door Access RevB]![User No]="",0))

    Result of Text Box Expression Builder Statement

    Nbr of Records User No AND Door No is Blank 8

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

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2018, 08:42 AM
  2. Expression Builder and Count
    By Daezle in forum Queries
    Replies: 7
    Last Post: 03-31-2016, 08:36 AM
  3. Replies: 1
    Last Post: 07-12-2013, 09:17 AM
  4. Replies: 7
    Last Post: 06-28-2013, 12:15 PM
  5. Replies: 2
    Last Post: 11-20-2012, 03:21 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