Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    22

    Unanswered: Conditional VBA Calculation

    Hi all,

    I have a field on a report I'd like to have access calculate for me based on the text value of another field.

    Logic is this:

    If Product_Type = "Roll Towel" Then Core_Weight = Roll_Length/10.2
    If Product_Type = "Jumbo Toilet" Then Core_Weight = Roll_Length/3.8
    If Product_Type = "Centrefeed" Then Core_Weight = Roll_Length/6.2

    Any ideas how to actually code this.

    I'm fairly new to VBA

    Cheers,
    Dan

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could use the Switch() function, but if there are a lot of them, I would consider including a field in a product type table for roll length, and getting it from there with your query.
    Paul

  3. #3
    Join Date
    Mar 2010
    Posts
    22
    Thanks for the lightning reply!

    My understanding of the Switch() function is that it will return the first true value from the expression. Which doesn't really help me.

    What I'm trying to do is calculate the CoreWeight field based on the product type. They are different because of the thickness of the material used to make the core for each product. Hence dividing the Roll Length by the stated values.

    So as an example:
    If the product type is Roll Towel then the calculation for the core weight is the roll length divided by 10.2
    e.g. 184/10.2=18

    As stated this calculation will be different if the product type is different.

    I hope my explanation is clear enough.
    Any more ideas.
    Last edited by danerida; 03-23-10 at 23:20.

  4. #4
    Join Date
    Mar 2010
    Posts
    22
    This:
    Code:
    =IIf([Product_Type]="Roll Towel",[Roll_Width]/10.2,IIf([Product_Type]="Jumbo Toilet",[Roll_Width]/3.8,IIf([Product_Type]="Centrefeed Towel",[Roll_Width]/6.2)))
    Isn't pretty and isn't even VBA, but it does exactly what I'm after.

    Thanks for trying to help

    Dan

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I guess you didn't understand:

    = [Roll_Width]/Switch(Product_Type = "Roll Towel", 10.2, Product_Type = "Jumbo Toilet", 3.8, Product_Type = "Centrefeed", 6.2)

    The more options there are, the greater the simplicity/advantage to the Switch() function over IIf().
    Paul

  6. #6
    Join Date
    Mar 2010
    Posts
    22
    Now I see what you mean.

    As I've said I'm fairly new to VBA so please excuse my ignorance.

    Thanks again for your help.
    Dan

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    All of the above offerings provide hard-wired solutions to your problem. You might like to consider creating a lookup table with two fields, ProductType and CoreWeight. Your formula can use the DLookup function to retrieve the core weight corresponding to the product type. There are two advantages to this method. One is that you can add new product types without messing about with the formulas. The other is that if you change the core weight at any time, you can access either the old or new values (for historic reporting) by adding a date range to the lookup table.

Posting Permissions

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