Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Expression Problem

    I am having a problem with an expression I'm attemtping to place in a query on my Access 97 database. What it is supposed to do is calculate the price we are charged for molasses if a certain condition exisits, i.e., brix falls below 72. Prior to April 2, the price per ton was 82.68 but if the brix was 72, we felt the price should go down the same way. The on April2, the price per ton went to $60 so the calculation had to change. This is what I've done:

    Price if Below 72 Brix: IIf([Vendor]="Sunkist" And [DateShipped]<#4/2/01# And [Brix]<72,[brix]*82.68/72) Or (IIf([Vendor]="Sunkist" And [DateShipped]>#4/2/01# And [Brix]<72,[brix]*60/72))

    This was working perfectly before the price dropped to $60 per ton. When I added the part of the expression to account for the change in price, it starting giving me back either no answer at all or -1 for each item where the price should be adjusted. Can anyone help?

    Thanks


    "CD"

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression Problem

    The IIf expression requires a field, a true part, and a false part. I don't see the false part to complete your expression. In the IIf statement there should be a value returned when the true part isn't met. I hope I'm reading the post correctly!

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Expression Problem

    Hi, CD. Haven't heard from you for a while. Is it the "or" condition you added? Because the way you've expressed it, you converted your statement to a true false result--one expression or the other. I've also seen really odd behavior with immediate if statements when you didn't include the else portion of the IIf expression, so I would watch out for that if I were you.

    Here's the statement as I understand it in a standard If EndIf.

    <pre>If Vendor = "Sunkist" And Brix < 72 Then
    If DateShipped <#4/2/01# Then
    result = Brix * 82.68 / 72
    Else 'DateShipped >=#4/2/01#
    result= Brix * 60 / 72
    End If 'DateShipped <#4/2/01#
    Else 'Vendor <> "Sunkist" or Brix > 72
    result = 0
    End If 'Vendor = "Sunkist" And Brix < 72 </pre>


    If the above is correct, then the immediate if equivalent would be this:

    Price If Below 72 Brix: IIf([Vendor]="Sunkist" And [Brix]<72, IIf([DateShipped]<#4/2/01#, [Brix]*82.68/72,[Brix]*60/72),0)
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Expression Problem

    Oh, Charlotte! One more time you have come to the rescue! Thanks so much. You understood exactly what I needed to do with this. As always, I very much appreciate the help.

    You are correct, I have not been in the Lounge a whole lot lately. I have been busy developing Access databases and making a little bit of money at it but most important, took a buy-out from my job - wonderful severance package - full pay and benefits for 1.5 years - then saw a part-time job in the paper, sent a resume, got a phone call and offered a job after a ten minute interview. A lot of new work - payables via Quick Books, learning about distilling orange molasses and will become controller of this privately held company in January. Not a lot of time for the Lounge but I do take a look when I can.

    Thanks again.


    "CD" (The Other One)

Posting Permissions

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