Results 1 to 7 of 7
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    adding an iff statement as criteria in dcount


    I have:
    DCount("[Role]","NBOI","[Paragraph Description]='" & [Paragraph Description] & "' AND [TOE Paragraph Num]='" & [TOE Paragraph Num] & "' AND [BN (Operating)]='" & [BN (Operating)] & "' AND [base]='" & [base] & "' AND [Platform]='" & [Platform] & "' AND [Role]='" & [Role] & "' AND [NBOI Sort Order (int)]<" & [NBOI Sort Order (int)]+1)

    I want to add: IIF[Platform]='Soldier", [Platform]='" & [Platform], '')

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    DCount("[Role]","NBOI","[Paragraph Description]='" & [Paragraph Description] & "' AND [TOE Paragraph Num]='" & [TOE Paragraph Num] & "' AND [BN (Operating)]='" & [BN (Operating)] & "' AND [base]='" & [base] & "'" & IIf([Platform]="Soldier", " AND [Platform]='" & [Platform] & "'", "") & " AND [Role]='" & [Role] & "' AND [NBOI Sort Order (int)]<" & [NBOI Sort Order (int)]+1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't understand your logic - to me it says

    if platform='soldier' then use platform='soldier' otherwise don't include platform in the criteria

    ergo
    if platform = 'soldier', include the record (subject to the other criteria)
    if platform<>'soldier', the record will be included because it is not being excluded (subject to the other criteria)

    so all records are returned

    so what is the point?

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax, because soldier is a consistent entity, the other platforms are multiple types of trucks. I want to look at the trucks as a single type.

    Thanks June7, these Dcount '" " screw with me.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Good point.

    For the records that have "Soldier" the count will only consider records that have "Soldier".

    For the records that do not have "Soldier" the count will consider all records, including "Soldier" records.

    Perhaps this is what you want instead:

    DCount("[Role]","NBOI","[Paragraph Description]='" & [Paragraph Description] & "' AND [TOE Paragraph Num]='" & [TOE Paragraph Num] & "' AND [BN (Operating)]='" & [BN (Operating)] & "' AND [base]='" & [base] & "' AND [Platform]" & IIf([Platform]="Soldier", "='Soldier'", "<>'Soldier'") & " AND [Role]='" & [Role] & "' AND [NBOI Sort Order (int)]<" & [NBOI Sort Order (int)]+1)
    Last edited by June7; 02-24-2018 at 12:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Ajax, because soldier is a consistent entity, the other platforms are multiple types of trucks. I want to look at the trucks as a single type.
    so soldiers are a type of truck so you are either looking a soldiers or everything, including soldiers.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thompyt,
    Do you have a clear statement in simple, plain English of what you are trying to count?

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

Similar Threads

  1. Adding decimals in Dcount
    By MTSPEER in forum Queries
    Replies: 2
    Last Post: 09-22-2017, 08:25 AM
  2. Replies: 7
    Last Post: 11-15-2013, 10:28 AM
  3. Expected End Of Statement Dcount, Access 2003
    By DetrieZ in forum Queries
    Replies: 1
    Last Post: 07-09-2013, 04:50 PM
  4. Can't get DCount statement to work
    By jhko in forum Programming
    Replies: 8
    Last Post: 02-10-2013, 05:50 AM
  5. DCOUNT function with IN statement
    By dwilson in forum Access
    Replies: 2
    Last Post: 08-12-2011, 09:54 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