+ Reply to Thread
Results 1 to 6 of 6

Combining IIF and DLOOKUP

  1. #1
    Registered User
    Join Date
    07-26-2011
    Location
    Lake Luzerne, New York
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    24

    Combining IIF and DLOOKUP

    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.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Combining IIF and DLOOKUP

    couold you upload a sample to make it easier for us to determine how best to help you?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-26-2011
    Location
    Lake Luzerne, New York
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    24

    Re: Combining IIF and DLOOKUP

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

  4. #4
    Registered User
    Join Date
    07-26-2011
    Location
    Lake Luzerne, New York
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    24

    Re: Combining IIF and DLOOKUP

    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Combining IIF and DLOOKUP

    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
    "I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
    If my advice has been helpful to you, then please help me by clicking on the "Star" and adding to my reputation, Thanks!

  6. #6
    Registered User
    Join Date
    07-26-2011
    Location
    Lake Luzerne, New York
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    24

    Re: Combining IIF and DLOOKUP

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1