Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Vernon Hills, Illinois, USA
    Thanked 0 Times in 0 Posts

    Access dlookup (XP 2002)

    trying to fill in a field w/ data from field in another table IF 2 fields in each table match (est cost from one table to drop into field in invoice table if vendor name and job in both tables are a match)
    if I use: =dlookup("[est cost]","[invoice_tble]","[inv_est_tble]:,"[vendor name]=forms![inv_est_tble]![venor],[job]=forms![inv_est_tbl]![job]") I get the message expression you entered has an invalid string
    I get deal summaries which list a vendor, job and estimated cost and I have a table for actual invoices received and when I go to enter an invoice, I want the estimated cost to drop into the field based on a match of both the vendor & job (if I had an estimate to enter) - the table of inv_est_tbl only contains the 3 fields vendor, job & est cost and I added a field for estimated cost to the invoice table and that's the field I'm trying to do the dlookup in

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Access dlookup (XP 2002)

    Try this

    =dlookup("[est cost]","[inv_est_tble],"([vendor name]=" & chr(34) & forms![inv_est_tble]![venor] & chr(34) & ") and ([job]=" forms![inv_est_tbl]![job] & ")")

    This assumes that Vendor name is a piece of text, while Job is a number.
    When you combine two criteria, you need to use 'and' between them.

Posting Permissions

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