Dlookup

[MartijnAn]

Hi All,

I want to retrieve a value from a query with a Dlookup function, where the 'Recordnumber' is the criteria. Something like this...

x = dlookup("[Field]","[query]","Recordnumber = 2")

Is that possible?

 

[ghudson]

Search the Access help files on how to use the DLookup() function for more info and examples.
Or check out this link DLookup Function

You have to format the criteria differently for numeric values versus text values!

 

[MartijnAn]

Thanks for your reply...

I know how the dlookup function works, I just wanted to know if you can set your criteria by the 'recordnumber' / 'record listing' in the result of the query.

For example, if I run a query, I want to know the value of field x in row 2

 

[RuralGuy]

There are *no* record numbers in Access. You will have to find another way for the record to be unique using the WHERE clause at the end if you want to use DLookup(). Otherwise just use a RecordSet.

 

[Pat Hartman]

In a query there is almost never a reason to use DLookup() at all. You should simply join to the table that contains the look up value.

 

[MartijnAn]

What would you suggest to do so? If I join the table, I still cannot grab the value I am looking for.

For example: After running the query I want the value that is in the second observation and in field X

 

[Summerwind]

Am I missing something????

I not sure if I'm missing something here or not.

Why not just open the table in a recordset then MoveFirst,MoveNext?

 

[Pat Hartman]

Select tblA.*, tblB.ObservationFld, tblB.fldX
From tblA Inner Join tblB On tblA.someField = tblB.someField;