Results 1 to 8 of 8

Thread: Formula

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a calculated field, ExtraKP, in a qryRentCalc0

    I am trying to make a tax calculation when two conditions are met:
    1. When [PState] = “SC”
    2. When [RentPaidDate] is after 06/30/2007.

    My formula is as follows
    ExtraKP: IIf([RentPaidDate] > #06/30/2007# and [PState]= “SC”,[NonRoomTaxable]*0.01,0)

    I have a situation where
    RentPaidDate = 06/01/07
    PState= “SC”
    NonRoomTaxable = $40.00

    The formula is returning $0.40

    Since the RentPaidDate is earlier than 06/30/07, it should return 0

    What am I doing wrong?

    Tom

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is the RentPaidDate a Date/Time field?
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes.

    Tom

    [quote name='SteveH' post='790856' date='26-Aug-2009 16:41']Is the RentPaidDate a Date/Time field?[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps RentPaidDate has been entered as 06/01/2107 by accident?

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I verified that the date in the formula is #06/30/2007# and RentPaidDate is in fact 06/01/2007.

    RentPaidDate is a field in tblRental
    Date/Time
    Short Date
    99/99/00;0;_

    Tom


    [quote name='HansV' post='790860' date='26-Aug-2009 16:47']Perhaps RentPaidDate has been entered as 06/01/2107 by accident?[/quote]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think we can solve this without seeing (a stripped down copy of) the actual database.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many tanks for the assistance. The formula turned out to be working correctly. The problem was in another field which was adding the tax amount in which made it look like the formula was not working.


    Tom



    [quote name='HansV' post='790864' date='26-Aug-2009 17:04']I don't think we can solve this without seeing (a stripped down copy of) the actual database.[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Glad you found it (this would have been impossible to discover without seeing the database).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •