Results 1 to 7 of 7
  1. #1
    VanillaAwesome is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    12

    if checkbox= true subtract 8.75 from A to=B

    I found one other similar topic but it did not help and still remains unsolved. I also apologize that i had to submit this from my phone but here we go.



    The Scenario: i have three columns labeled DonationValue, NetDonation, and PickupRequired. Both donations are text based and pickup is a yes/no checkbox. I know i must edit this information in the field box in design view for the query. I know this as the query it was required that i copy it from has NetDonation: [DonationValue]-8.75 as its field value.

    The Request: It has been asked that if the checkbox is marked true I need to take the amount thats in DonationValues and subtract 8.75 from it to create new NetDonation. At the same time if the box is false NetDonations = to DonationValue.

    I am stumped. We just learned about crosstabs and IsNull but i fail to see where that would help here. We learned about the build tab a chapter ago but i dont know where to begin. If you need clarification please ask. Again, posting from my phone and i apologize.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So tyhe definition of the NetDonation field will be:
    NetDonation: =IIF([PickupRequired] = True, Val([DonationValue]) - 8.75, [DonationValue])

  3. #3
    VanillaAwesome is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    12
    When entered as above i receive an erroe message of: syntax error. You have entered an invalid comma or ommitted quotation marks...

    Any ideas. Also should i enter the spaces as you have them or remove them from mine. Tried it both ways and still got error but want to rule it out as an issue

  4. #4
    VanillaAwesome is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    12
    Okay, played around a little and my script now reads.

    NetDonation: Iff([pickuprequired]=true,Val([donationvalue])-8.75,[donationvalue])

    No spaces except between : Iff. It is now giving me an error when attempting to run stating "undefined function 'Iff' in expression.

    Thanks for the help so far any ideas?

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It stands for Immediate IF so it is IIF and not IFF.

  6. #6
    VanillaAwesome is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    12
    HOLY CRAP!!! Yup, I was just entering the "if" function incorrectly. Thank you so much for your speedy replies and help. Im sure i will have more questions in the future as this class has four weeks left and am making up hw from a vacation lol.

    Thanks again for everything!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Glad you got it sorted.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-11-2012, 05:13 PM
  2. Query: Subtract totals from different tables
    By joannakf in forum Queries
    Replies: 3
    Last Post: 12-22-2011, 01:39 PM
  3. Subtract from Previous Row
    By lambo102 in forum Queries
    Replies: 1
    Last Post: 08-06-2011, 09:39 AM
  4. How to do subtract in Query
    By NoOoN in forum Queries
    Replies: 6
    Last Post: 04-07-2011, 01:15 PM
  5. Subtract from inventory
    By NISMOJim in forum Access
    Replies: 5
    Last Post: 01-30-2011, 01:09 PM

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