1. Registered User
Join Date
Mar 2010
Posts
22

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. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

3. Registered User
Join Date
Mar 2010
Posts
22

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. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888
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().

6. Registered User
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.

Dan

7. Registered User
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
•