Form "if"? statement

[melanieh]

I have a form that allows employees to enter the width and height for a product on a form.
The width and height are then combined with the type and sizing to make a unique id. That unique id is then looked up in the table to get the price for that specific product.

The product is blinds which can be customized.

So.... if an employee enters a width of say "34" that isn't an actual width of the blind. The blind is actually "36" in width, and it is cut down to the 34 in the shop.
-----------------------------------------------------------------
So....here is my question: (I am trying to figure out the best way to do this as I haven't done this before)

Do I write an "if" statement that says something like:
If the width entered is between 24-35 change the width on the id to 36?

The blinds have a width of 23, then the next width is 36 so anything inbetween 24 would need to convert to the 36 width.

Thanks! I've learned a ton from reading the forums.

 

[RuralGuy]

One way is to avoid code completely and simply put in a separate record for each dimension that has the proper code you wish to use.

 

[melanieh]

That is a possibility. Thanks.

There are too many possibilties for each one I think.
For the 24-35 width range it could have 24 1/8, 24 1/2, 24 3/4, 24 7/8 and so forth..... so there could be just about anything. And to come up with each possibility for every width and height would thousands upon thousands of records.
Anyhow, hopefully there's a way to come up with making it move up to the next width. ???

 

[Pat Hartman]

If you can change the blinds table to include min and max, you will easily find the records you need without coding. RuralGuy's idea would also work but would be limited to whole numbers.

Select UnitPrice from tblBlindsPricing
Where CustomSize >= MinSize and CustomSize <= MaxSize;

Using just the Max size, the query would be:
Select Top 1 UnitPrice from tblBlindsPricing
Where CustomSize <= StockSize
Order By StockSize Desc;

I prefer the first solution since it is clearer.

 

[melanieh]

Thanks for the ideas.
I'll see if I can think it through completely tonight when I have some more time to work through it.
I appreciate the help.
Melanie

 

[melanieh]

Can you give me an example of the table with minimum / maximum idea you have?
My mind isn't totally grasping the whole thing.

Then on the form, I create the Select statement?

Thanks.

 

[RuralGuy]

It is trivial to check for a non integer entry. I would still go with the idea I proposed modified to test form non-integer values so we can bump the value to the next increment. Just my $0.02.

 

[melanieh]

Can you give me an example or anything for what you are talking about?
(Thanks. my mind isn't pulling all this together today.)

melanie

 

[Pat Hartman]

MinSize, MaxSize, UnitPrice
1,10,$5.00
11,100,$4.50
101,500, $4.00

 

[melanieh]

I have a table that has the following fields:
minimum width, maximum width, minimum height, maximum height, price

I created a form where the user can input the width in a text box and a height in a text box. The next box I created is a price box that will pick up the price from the table.

-----------------
I've tried to create a query to use for the price box to no avail. What fields need to be in the query.... then on the price field within the query.....is that where I create the expression with the <= and =>. (I get a mismatch error currently but wanted to make sure I'm heading in the right direction.)

Thanks a bunch!
Melanie

 

[ajetrumpet]

if an employee enters a width of say "34" that isn't an actual width of the blind. The blind is actually "36" in width, and it is cut down to the 34 in the shop.
-----------------------------------------------------------------
So....here is my question:

Do I write an "if" statement that says something like:
If the width entered is between 24-35 change the width on the id to 36?

How about a SELECT CASE statement? How many widths are allowed by the shop? In other words, how many "intervals", or "spans" of numbers do you have?

If you were to write a nested IIF, it would probably work too. Check this out and see if it helps with that idea...

 

[melanieh]

Thanks. I'll study the example.

The widths and heights options are about a thousand. The widths and heights can vary by 1/8" and start at about 14" on both. So lots of options!

Melanie

 

[ajetrumpet]

SELECT CASE won't work then. And neither will a nested IIF.

From this...

So....here is my question: (I am trying to figure out the best way to do this as I haven't done this before)

Do I write an "if" statement that says something like:
If the width entered is between 24-35 change the width on the id to 36?

The blinds have a width of 23, then the next width is 36 so anything inbetween 24 would need to convert to the 36 width.

...you say that this is pretty much a rounding issue. What about the thousand?? Is that the number of manufacturing dimension options for this product? If you want, it may help to see a list of like 5-10 options (numbers) or so, from the beginning of the list...like; the "smallest" options for widths.

 

[Pat Hartman]

You need to extrapolate my example to take two arguments:

Select UnitPrice from tblBlindsPricing
Where CustomWidth >= MinWidth and CustomWidth <= MaxWidth
AND CustomHeight >= MinHeight and CustomHeight <= MaxHeight;

CustomWidth and CustomHeight will be references to your form fields and will be in the format:

Forms!yourformname!yourfieldname