#  Other Applications & Softwares  > Access Tables & Databases >  >  Combining IIF and DLOOKUP

## snoproladd

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

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

----------


## snoproladd

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

----------


## snoproladd

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

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

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!

----------

