Combining IIF and DLOOKUP

**snoproladd** · 03-16-2012, 02:30 PM

I'm creating a database to track the cost of material damaged in my aluminum plant. Eventually reports will be made to track each department's errors, the type of errors, which job/customer they are assigned to, and so on. I also need to generate a list of adjustments to make out of my inventory software, which unfortunately has no import feature, so i will be using printed reports.

My problem is some of my adjustments will be for a part which exists in its raw, full length form (each) and other adjustments will be by the foot (feet).

These units of measure exist in a field calld UOM in a table made from a linked excel spreadsheet from my inventory software. This table is arranged as follows:

WinSysPart | Description | UOM | UnitCost

Another table is filled with a form that matches paperwork used on my production floor:

PRIMARY KEY | PARTNUMBER | LENGTH | QUANTITY | DEPARTMENT | TYPE OF ERROR...AND SO ON

The PARTNUMBER form box is restricted to the List "WinSysPart" so only real numbers exist

I would like it to be impossible to enter a length if the unit of measure is "EACH"; only allowed if UOM is "FEET"

I've tried the IIF( DLOOKUP function but I'm having trouble. I'm probably missing something very stupid, but help would be apprecitated.

**FDibbins** · 03-16-2012, 03:57 PM

couold you upload a sample to make it easier for us to determine how best to help you?

**snoproladd** · 03-16-2012, 10:28 PM

For some reason I can't upload any database files. I'll try again tomorrow.

**snoproladd** · 03-19-2012, 11:45 AM

I attached the file with the .xls extension, it will need to be changes to .mbd to funtion properly. Thanks to anyone that can help.

**split_atom18** · 03-27-2012, 04:48 PM

Hi snoproladd,
There are several ways to accomplish this. I will give you a few:
One would be to set the after update trigger on the PARTNUMBER field to enable = false the length field. I also recommend setting the length field to 0 if an item that is "EACH" is selected.

The Second: The before update event on the length field. Check if the part is set to each. Undo change if it is.

I have modified your database and recommend you look at the changes I made.
Changes Made:
RecordSource Field on PARTNUMBER
Column Count: 3
Column Width
List Width
After Update Property

LENGTH field Before Update Property

Hope this helps,

Danmaterial rework.zip

**snoproladd** · 04-11-2012, 01:15 PM

Hey, thanks a lot Split_atom18...sorry its been so long getting back to you, I've been crazy busy and this has taken a back burner. I'll look more closely at it when I have more time but it functions well. All I really have to do is make some of the Form items drop downs limited to lists, easy, and generate some queries and reports to consolidate the data...thanks a lot!