Results 1 to 12 of 12
  1. #1
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6

    How to display a value from another field using an IIF function


    Hello,

    In short, I'm trying to write an IFF function that will look at my filed titled RelCaseNum and display a zero in a new column if RelCaseNum's value is zero, and if it's not zero, then I want the value stored within the field titled "CountVehiclesInRelCaseNum" to be displayed.

    Below were my feeble attempts that have failed:


    DesiredEnd-State:IIf([RelCaseNu]="0","0",[RelCasesVehicles])
    DesiredEnd-State: IIf([RelCaseNu]="0","0","[RelCasesVehicles]")
    DesiredEnd-State: IIf([RelCaseNu]="0","0",[nameofquery]![RelCasesVehicles])


    If this helps, this is a sample of what the data currently looks like:
    IncNo RelCaseNu RelCasesVehicles
    2122415 0 1
    2333515 215442200 0
    21555515 0 1
    6222915 69454583215 0
    65111115 0 1
    60099915 99666655 1
    6666665 0 1
    955555515 86543200 3
    91111115 0 1



    This is what I want it to look like:
    IncNo RelCaseNu RelCasesVehicles DesiredEnd-State
    2122415 0 1 0
    2333515 215442200 0 0
    21555515 0 1 0
    6222915 69454583215 0 0
    65111115 0 1 0
    60099915 99666655 1 1
    6666665 0 1 0
    955555515 86543200 3 3
    91111115 0 1 0

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's a numeric field you don't want the quotes:

    IIf([RelCaseNu]=0,0,[RelCasesVehicles])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6
    Unfortunately, when I run the query as proposed I get a Enter Parameter Value prompt box for the [RelCasesVehicles]. So that doesn't work for me.

    As a side note, the field RelCasesVehicles is a numeric field. It actually isn't but I made it so by using the Val function in a prior query. But still, when I remove the quote, it thinks it's a parameter and not the field I'm trying to reference.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you're getting a parameter prompt, it doesn't recognize that as a field name. Is the spelling correct? Is it an aliased field in the same query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6
    Sorry or the delayed reply.

    So when I design the query as advised:
    DesiredEnd-State: IIf([RelCaseNu]=0,0,[RelCasesVehicles])
    The following occurs: first, I get a pop up titled Enter Parameter Value and text that says RelCasesVehicles and an empty field. When I leave it blank and press ok, I get the following type of data:
    IncNo RelCaseNu RelCasesVehicles DesiredEnd-State
    343234415 0 1
    35004325 350643225 0
    3510522233 0 1
    3699233222 0 1
    373981114434 373443225 1 1
    37656345312 0 1
    37936266443 3745432215 2 2
    3872523445 0 1
    3884143212 0 1

    However, I’d like the Enter Parameter Value dialog box not to appear and I’d like zeros instead of NULL values to appear.
    So by making one additional modification to your recommendation which now appears as so:

    DesiredEnd-State: IIf([RelCaseNu]=0,”0”,[RelCasesVehicles])

    I unfortunately still get the pop up (any advice on that would be appreciated also), but after leaving it blank, I get the desired results:
    IncNo RelCaseNu RelCasesVehicles DesiredEnd-State
    343234415 0 1 0
    35004325 350643225 0 0
    3510522233 0 1 0
    3699233222 0 1 0
    373981114434 373443225 1 1
    37656345312 0 1 0
    37936266443 3745432215 2 2
    3872523445 0 1 0
    3884143212 0 1 0

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The popup is Access telling you it can't find something. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6
    Unfortunately I can't. It's our work's SQL db which I tap into using MS Access. I though I understood why it's giving me this parameter query pop up because whenever I use brackets, it can think it's a parameter. But since I'm using brackets as a part of an expression, I'm puzzled. Is there any other way I can try to help share information with you?

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you step back, and pretend we know nothing about your application ( in fact--we don't know what any of these things mean nor how they fit together)? Treat us like someone who doesn't know you, doesn't know database and has never heard of Access. Now, in simple English what is the subject matter you are dealing with, and what is the current problem (no jargon).

    Good luck.

  9. #9
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6
    Hello,

    pbaldydid a great job answering my main problem (i.e. helping me write a query that returns a value from another field if a specific condition is met). Actually, I had previously tried his approach but stopped ever time I got a popup window from access saying I needed to enter a parameter. Turns out ifI would have just hit ok, I'll get the results I want. So this problem isresolved.

    My new problem is the annoyance of this pop-up window that says "Enter ParameterValue" and then the only words it says are:

    RelCasesVehicles

    This term [RelCasesVehicles] is the alias name of a field that's actually titled[VehIncNo]. So in my access query, it's really RelCasesVehicles:VehIncNo.

    Attachedis a picture.

    Click image for larger version. 

Name:	Interface.jpg 
Views:	10 
Size:	103.9 KB 
ID:	23561

  10. #10
    JHWard is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    6
    It is an alias field in the same query. Is that a problem? pbaldydid had asked about that earlier and I didn't mention that it was. Also, this query does have the Group button selected because I'm doing a count on one of the fields. Hope this helps solve it.

  11. #11
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Modify your RelCasesVehicles calculation to:

    Field: "RelCasesVehicles: iif(VehIncNo is null, 0, 1)"
    Total: "Count" changes to "Sum"

    Remove your DesiredEnd-State field. You can't group on an aggregated field that is being defined within the same query. Group By values are calculated first. Aggregates like Sum and Count are calculated after Group Bys are established--so you're Group By fields cannot include an alias to your aggregates.

    Hope this makes sense. Best of luck!


    Jeff

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could certainly try using the actual field name instead of the alias. I'm not sure about grouping by a field that you're counting with an expression though. You might need to create a base query that includes the expression.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 10-28-2015, 12:38 PM
  2. Using Max Function to display latest record
    By jamarogers in forum Forms
    Replies: 4
    Last Post: 09-29-2013, 03:13 PM
  3. Replies: 10
    Last Post: 02-09-2012, 04:42 PM
  4. Replies: 1
    Last Post: 08-18-2010, 02:05 PM
  5. Report control of a field display/no display
    By systems013 in forum Reports
    Replies: 5
    Last Post: 02-01-2010, 09:44 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