Results 1 to 6 of 6
  1. #1
    ice673 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    3

    need help, expression is too complex?


    I tried to add this field to my query but didn't work. I also tried to use switch but the result is the same. I tried using dlookup and created another table for the values but when i run the query, the fields are repeated by the number of fields in the new table!
    can anyone help please!
    IIf([GrossPay] Between 4000 And 4999.99,50),
    IIf([GrossPay] Between 5000 And 5999.99,62.5),
    IIf([GrossPay] Between 6000 And 6999.99,75),
    IIf([GrossPay] Between 7000 And 7999.99,87.5),
    IIf([GrossPay] Between 8000 And 8999.99,100),
    IIf([GrossPay] Between 9000 And 9999.99,112.5),
    IIf([GrossPay] Between 10000 And 10999.125),
    IIf([GrossPay] Between 11000 And 11999.99,137.5),
    IIf([GrossPay] Between 12000 And 12999.99,150),
    IIf([GrossPay] Between 13000 And 13999.99,162.5),
    IIf([GrossPay] Between 14000 And 14999.99,175),
    IIf([GrossPay] Between 15000 And 15999.99,187.5),
    IIf([GrossPay] Between 16000 And 16999.99,200),
    IIf([GrossPay] Between 17000 And 17999.99,212.5),
    IIf([GrossPay] Between 18000 And 18999.99,225),
    IIf([GrossPay] Between 19000 And 19999.99,237.5),
    IIf([GrossPay] Between 20000 And 20999.99,250),
    IIf([GrossPay] Between 21000 And 21999.99,262.5),
    IIf([GrossPay] Between 22000 And 22999.99,275),
    IIf([GrossPay] Between 23000 And 23999.99,287.5),
    IIf([GrossPay] Between 24000 And 24999.99,300),
    IIf([GrossPay] Between 25000 And 25999.99,312.5),
    IIf([GrossPay] Between 26000 And 26999.99,325),
    IIf([GrossPay] Between 27000 And 27999.99,337.5),
    IIf([GrossPay] Between 28000 And 28999.99,350),
    IIf([GrossPay] Between 29000 And 29999.99,362.5),
    IIf([GrossPay] >=30000,375)))))))))))))))))))))

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Create a Public Function is a Standard Module that will determine and return the value for you and then pass it the [GrossPay].
    Much easier to read and understand and maintain later.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Or try:

    Code:
    IIf([GrossPay] Between 4000 And 30000,((([GrossPay]-4000)/1000)*12.5)+50,IIf([GrossPay]<4000,0,375))
    Cheers,

  4. #4
    ice673 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by RuralGuy View Post
    Create a Public Function is a Standard Module that will determine and return the value for you and then pass it the [GrossPay].
    Much easier to read and understand and maintain later.
    can you explain more? thanks!

  5. #5
    ice673 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by ConneXionLost View Post
    Or try:

    Code:
    IIf([GrossPay] Between 4000 And 30000,((([GrossPay]-4000)/1000)*12.5)+50,IIf([GrossPay]<4000,0,375))
    Cheers,
    it didn't work. thanks anyway!

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If you create a Standard Module named basFunctions, then any Public Function you put in that module is available to any form or quary. You would invole it in your query with YourFunction([GrossPay]) and have it return the value you want.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Complex Query/Queries for a Report
    By Rawb in forum Queries
    Replies: 3
    Last Post: 02-04-2010, 07:44 AM
  2. complex calculations like in Excel
    By jacko311 in forum Database Design
    Replies: 2
    Last Post: 11-11-2009, 05:51 PM
  3. Complex Lookup?
    By murphyB in forum Database Design
    Replies: 1
    Last Post: 09-17-2009, 12:43 PM
  4. Complex Update query
    By niihla10 in forum Queries
    Replies: 1
    Last Post: 08-28-2009, 01:02 PM
  5. Complex Date Comm/Amt Formula
    By JLongo in forum Programming
    Replies: 0
    Last Post: 11-21-2008, 10:24 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