dlookup

[steve111]

hi

I have 2 tables
1. order details
2 invoicedetails

in the table "order details" I have a field called , productno and required( required in a date field)

in the table "invoicedetails" I have a field called procduct no1

what I would like to do is get the result "required" in my dlookup of the "required" date where the productno and product no1 match numbers match

I did try this as I am just learning dlookup but it failed.

requireddate: IIf(DLookUp("[productno]","order details","product no1= '" & [invoicedetails] & "'"),[required])



any help appricaited

 

[Isskint]

Hi Steve

The first argument in the Dlookup() function should be the field you wish to return the value of. The second argument is the record source (table, query etc) that holds the first argument. The third argument is the criteria (not always needed) that identifies which record in the record source you wish to lookup. So you want to lookup required (argument1) in the table order details (argument 2) where product no = product no 1 (argument 3).

I am curoius to know why you included an iif() condition?

DLookUp("[required]","[order details]","[product no]=" & [product no1])

 

[steve111]

hi

I am getting the word "error" in my query that I put that lookup in


required date: DLookUp("[required]","[order details]","[productno]=" & [product no1])

or should I put this in a form before I use it in the query for my report


steve

 

[plog]

If this is in a query, why are you using a DLookup? You should bring order details into the query, link appropriately and just bring down required.

 

[Isskint]

Like Plog said, in a query just join the 2 tables, much cleaner.

But to answer your scenario, the error could be for a couple of things.
required date is an invalid field name - you can not have spaces. required_date or [required date] would be OK.
Invalid field names - Are the field names in my formula exactly as you have them in your tables?
Wrong data type - are [product no] & [product no1] numbers, text, ??

 

[steve111]

hi

I tried to bring the 2 fields down into the query but when I run the query I get 5 times more than the result quantity should be. and cannot join them any other way to only get the 5 results I needed that's why I went for the dlookup

for some reason the data type in the order detail form is numbers( yet the product number could be gt1234

steve5 ( this was set up by others ) and the part I have done in invoicedetails is "text"

 

[Isskint]

OK. On the data types you will be best served tidying that up so both are text.

With that done the DLookup() would need amending to account for text. When specifying criteria for a text field you need to enclose the parameter in quotes, so

DLookUp("[required]","[order details]","[product no]='" & [product no1] & "'")

If tidying is not feasible, you could coerce the parameter value into text, as

DLookUp("[required]","[order details]","[product no]='" & Cstr([product no1]) & "'")