Dimension dependent price on invoice

[knh2r]

Hey guys. New member here with a question.



I need to make an invoice for a sheet metal business where a standard item on the invoice is input and then a custom size is entered as 25 x 24.5 x 18. etc. That size is put through a price formula and entered.

Is this possible?

Thanks for the help!

[CJ_London]

Is this possible?

yes. Since this is for an invoice you will want to store the calculated price so it doesn't change if you were to later change the calculation. Note this field is not suitable as a calculated field in a table.

You would do this with code referenced in the after update event of each of the fields that go into the calculation (length, width, thickness, material type etc)

What would the code be? can't advise without knowing how the calculation works, what other tables it references etc

[knh2r]

Thanks for the reply!

The formula is:
2W+2H*Girth Factor

The Girth Factor comes from a chart based on Length. 12"=.24, 18"=.3, etc.

I'm guessing I would create a table for this girth factor? Or would that be referenced in an outside Excel spreadsheet?

Also, ideally the size would be entered as 25x24.5x18 etc and the #'s would be extracted automatically, but if this is not possible I'm not completely opposed to having separate fields for width, height, length.

Thanks so much!

[CJ_London]

you would use a table rather than going out to excel or if the girth factor is fixed in stone and not too many values (say no more than 10) you could just hardcode it (use the switch function)

you can certainly have the user enter 25x24.5x18 and then have some code to split it into its separate components (check out the vba split function) and you will need to code against incorrect entries (spaces, forgotten x, missing dp's etc), however you may find users prefer to hit the tab or enter key to go to the next field rather than the x key so they enter the individual values. Other benefit is you can use combo boxes to limit the sizes to those available - your sheets presumably come in a fixed range of gauges for example?

[knh2r]

Thanks a lot! So I got the formula for price working as you said using different fields for the dimensions and a dlookup.

Next issue is that I have different Girth Factors depending on the item made (selected in the invoice). How would I go about having the formula lookup a different chart and size based on which item is selected? Maybe if statements?

Thanks!

[knh2r]

Or would the proper way be to somehow make the entire price field linked to the product selected?

[CJ_London]

you would need to explain how it actually works - for example say your girth is in 6" steps - do you choose the nearest one, the next one up or the next one down if it is not an exact match

[knh2r]

It is in ranges. So for a plenum it might be 0-18=.24. 19-29=.3, etc. Then for a end cap maybe it is just LxW. Then for a drain pan it's 0-25=.6 etc.

Complicated I know....

Thanks!

[CJ_London]

ok, so your girths table would be something like

tblGirths
GirthPK autonumber
GirthType - lookup to the types perhaps or could be a text description
GirthRange integer
GirthValue double

populated something like

GirthPK..GirthType..GirthRange..GirthValue
1..........plenum......18..............0.24
2..........plenum......29..............0.30
3..........endcap......100.............1.00
4..........drainpan....25..............0.60

Although endcap does not have a girth value, including it in your table with a GirthRange higher than it could possibly be will simplify your calculations down the line - (ie if endcap, do this otherwise do that)

To find the value required you would look for the minimum value in the range that is greater than the length e.g. something like

DMin("GirthValue","TblGirths","GirthType=" & itemType & " AND GirthRange>" & length)

[knh2r]

I have been playing this the DMin function like you provided and have been unable to make it work. I just keep getting a #error in the form and the entire field flashes quickly like it is trying to work?

Any ideas? Is there something I can post from my database to give you a better idea of my issue?

Thanks!

[CJ_London]

just start with

1. posting exactly what you put in the control controlsource (just copy and paste)
2. details of your tblGirths - the name you called it, the field names and types - as I've done in the first part of post #9
3. details of what it is populated with - as per the second part of post #9 - just use some examples where you are getting the error
4. details of the controls on the form you are using in the dmin - name, value where you get the error controlsource, rowsource and bound column if applicable combos and listboxes)

and to be clear, you are aware that if you have spaces or non alphanumeric characters in your field names, you need to surround them with square brackets? And you are not using reserved words (width is reserved for example)

Do not send the db - I'm trying to help you and others. By you spending a bit more time on the above a) might help you see the issue and b) I don't have to spend 20 mins trying to work out how your form is working (I don't really have the time)

[knh2r]

I got it working! To be honest I was changing so many things one at a time to find the issue that I'm not 100% what did it, but I think it was a space around the final Length portion...thought i quadruple checked that stuff...oh well! I also had to put itemtype and in "" (and changed to ProductID for my db).

Thank you SO MUCH for your help! I'm still learning these more advanced functions and seeing the logic behind the function you provided helped my understanding a lot.

I may be back with other questions as I continue development.

[CJ_London]

happy to help

[knh2r]

Ok...So one more related question...for now.

Is there a way to make the formula still work if there is no Length and one is not entered? I see what you were saying about having them in the girth table and I did do that. I was wondering if there was a way to tell the formula to use "1" if Length is not entered. I was playing around with Nz but reintroduced #error so maybe I don't have the syntax correct.

Along those lines. Is there a way to get the fields in the form to show blank or some place holder until data is entered rather than #error? They work perfectly just trying to clean it up a bit.

[CJ_London]

something like

DMin("GirthValue","TblGirths","GirthType=" & itemType & " AND GirthRange>" & iif(length=0,1,length))