Results 1 to 15 of 15
  1. #1
    rahayes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    9

    Excluding certain values from an addition query

    I wonder if anyone can help.



    I am running a simple query that adds a number of numerical fields together.

    The Expression Builder looks similar to this:

    qu96-98: [tblMSCL]![MSCL_Q096]+[tblMSCL]![MSCL_Q097]+[tblMSCL]![MSCL_Q098]

    The kind of data would look like this in real terms:

    2+2+99

    What I want to do is get the experession to ignore any values that are a certain number (in this case 99). So I want the answer displayed to be 4 NOT 103

    The problem I'm having is that if [tblMSCL]![MSCL_Q096] contains a value I don't want included I can't work out how to say 'just ignore that one value, don't ignore the whole row'.

    Any suggestions??

    Rachel

  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
    Try

    qu96-98: IIf([tblMSCL]![MSCL_Q096]=99,0,[tblMSCL]![MSCL_Q096])+[tblMSCL]![MSCL_Q097]+[tblMSCL]![MSCL_Q098]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rahayes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    9
    It seems to have worked - thanks! But I have no idea why??!! What does the 'IIf' do?

    Thanks for such a quick repsonse

    Rachel

  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
    From help, the IIf() function:

    Returns one of two parts, depending on the evaluation of an expression.


    So in your case, the expression tests the value of the field, and the function returns zero or the value of the field based on that test.


    Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rahayes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    9
    Thank you so much - after some complicated code writing (I had 110 questions that I needed to do this with!) it all works.

    Thank you for the welcome - I think I may be on here quite a bit!!!

  6. #6
    pradeepgp is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    5

    Excluding certain values from an addition query: MS Access 2010

    Hi,
    I am trying to sum up data from 11 columns,

    Total score: [Personal Hygiene]+[Bathing self]+[Feeding]+[Toilet]+[Stair climbing]+[Dressing]+[Bowel control]+[Bladder control]+[Ambulation]+[Wheel chair]+[Chair/Bed TF]

    I want to remove the values "99" and "98" from these columns which represent "not applicable" and "missing data" respectively.

    I read through the thread and found a similar query, but not able to understand it. Kindly help me with this. Thank you.

    Best regards,
    pradeep

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried the IIf() function that was suggested? You'd have 2 conditions instead of one, otherwise it's the same.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pradeepgp is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    5

    Excluding certain values from an addition query: MS Access 2010

    Quote Originally Posted by pbaldy View Post
    Have you tried the IIf() function that was suggested? You'd have 2 conditions instead of one, otherwise it's the same.

    I am trying to sum up data from 11 columns,

    Total score: [Personal Hygiene]+[Bathing self]+[Feeding]+[Toilet]+[Stair climbing]+[Dressing]+[Bowel control]+[Bladder control]+[Ambulation]+[Wheel chair]+[Chair/Bed TF]

    I want to remove the values "99" and "98" from these columns which represent "not applicable" and "missing data" respectively.

    I read through the thread and found a similar query, but not able to understand it. Kindly help me with this. Thank you.

    Best regards,
    pradeep


    This is the syntax that I tried, but getting some error, kindly help.


    Totalscore: IF(Personal Hygiene And Bathing self And Feeding And Toilet And Stair climbing And Dressing And Bowel contro And Bladder contro And Ambulation And Wheel chair And Chair/Bed TF Not Eqv 98 Or 99),SUM(Personal Hygiene,Bathing self,Feeding,Toilet,Stair climbing,Dressing,Bowel control,Bladder control,Ambulation,Wheel chair,Chair/Bed TF)null)

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Look at post 2, which does a single field. You'd have to do each.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    pradeepgp is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    5
    Hi, Thanks for your reply, I am a beginer in MS access. As far as i could understand i have created this syntax, please check if this is right.

    Totalscore: IIF(Personal Hygiene And Bathing self And Feeding And Toilet And Stair climbing And Dressing And Bowel contro And Bladder contro And Ambulation And Wheel chair And Chair/Bed TF Not Eqv 98 Or 99),SUM(Personal Hygiene,Bathing self,Feeding,Toilet,Stair climbing,Dressing,Bowel control,Bladder control,Ambulation,Wheel chair,Chair/Bed TF)null)

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does it look anything like the example?

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

  12. #12
    pradeepgp is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    5
    Sorry for my ignorance. Is this how it should be?

    Totalscore: IIF([Personal Hygiene] =99,0,[Personal Hygiene]+ [Bathing self] = 99,0,[Bathing self]....

    Request your help.

    Thank you.

  13. #13
    pradeepgp is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    5
    Hi,
    This is the syntax that I used for excluding columns with the value 99, I am getting an error

    IIF([tbl MBI]![Personal Hygiene]=99,0,[tbl MBI]![Personal Hygiene])+([tbl MBI]![Bathing self] = 99,0,[tbl MBI]![Bathing self])+([tbl MBI]![Feeding]= 99,0,[tbl MBI]![Feeding])+([tbl MBI]![Toilet]= 99,0,[tbl MBI]![Toilet])+([tbl MBI]![Stair climbing]= 99,0,[tbl MBI]![Stair climbing])+([tbl MBI]![Dressing]= 99,0,[tbl MBI]![Dressing])+([tbl MBI]![Bowel control]= 99,0,[tbl MBI]![Bowel control])+([tbl MBI]![Bladder control]= 99,0,[tbl MBI]![Bladder control])+([tbl MBI]![Ambulation]= 99,0,[tbl MBI]![Ambulation])+([tbl MBI]![Wheel chair]= 99,0,[tbl MBI]![Wheel chair])+([tbl MBI]![Chair/Bed TF]= 99,0,[tbl MBI]![Chair/Bed TF])

    Kindly let me know if my syntax is right.

    Thank you,

    Best regards,
    Pradeep

  14. #14
    rahayes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    9
    How are you getting on? I posted the original question and it now works well for me. I've taken a look at your syntax and can't see any errors, but I'm not a syntax expert at all. Are you actually asking to sum all these. The syntax I use to do the same thing is;

    Total: IIf([tblMSCL]![MSCL_Q001]=99,0,[tblMSCL]![MSCL_Q001])+IIf([tblMSCL]![MSCL_Q002]=99,0,[tblMSCL]![MSCL_Q002])+IIf([tblMSCL]![MSCL_Q003]=99,0,[tblMSCL]![MSCL_Q003])+IIf([tblMSCL]![MSCL_Q004]=99,0,[tblMSCL]![MSCL_Q004])+IIf([tblMSCL]![MSCL_Q005]=99,0,[tblMSCL]![MSCL_Q005])+IIf([tblMSCL]![MSCL_Q006]=99,0,[tblMSCL]![MSCL_Q006])+IIf([tblMSCL]![MSCL_Q007]=99,0,[tblMSCL]![MSCL_Q007])+IIf([tblMSCL]![MSCL_Q008]=99,0,[tblMSCL]![MSCL_Q008])+IIf([tblMSCL]![MSCL_Q009]=99,0,[tblMSCL]![MSCL_Q009])+IIf([tblMSCL]![MSCL_Q010]=99,0,[tblMSCL]![MSCL_Q010])

    I'm interested to know if you found a way to exclude both 99 and 98 as I can see a need for this in the future for me.

    Best
    Rachel

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    IIf([tblMSCL]![MSCL_Q001]=98 OR [tblMSCL]![MSCL_Q001]=99,0,[tblMSCL]![MSCL_Q001])
    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. Excluding null values on SQL table query
    By DB2010MN26 in forum Queries
    Replies: 1
    Last Post: 09-03-2010, 12:54 PM
  2. Query DateDiff calculation excluding weekends
    By Masterfinn in forum Queries
    Replies: 3
    Last Post: 04-01-2010, 09:46 AM
  3. Replies: 4
    Last Post: 02-08-2010, 11:17 AM
  4. Excluding data in a query
    By lpdds in forum Queries
    Replies: 1
    Last Post: 12-16-2009, 08:06 PM
  5. addition with 0
    By Peljo in forum Access
    Replies: 0
    Last Post: 02-28-2008, 08:12 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