An Access function that works like Excel's vlookup or hlookup?

P

PeteyP

Need something that will pull an item from a large table by matching
something from the first column, then selecting a corresponding field (cell)
from one of 20 neighboring columns on the table.

I have tried:
=(DLookup([DLA]![Seventeen],[DLA],[caselog]![deadline]=[DLA]![Deadline]))
but only get the #Name? response.

I have also tried putting the Dlookup function statement within an IIf
statement.

In Excel, an expression that has worked for me is:
VLOOKUP(D17,LUTable06,17,FALSE),"")
 
K

Klatuu

The syntax is all wrong
=DLookup("[Seventeen]","DLA","[Deadline] = " & [caselog]![deadline])
The problem I have is I don't know what [caselog]![deadline] is.
Also, the syntax will vary depending on the data type of the field you are
using for criteria. The above syntax is correct if [Deadline] is numeric.
If it is text:
=DLookup("[Seventeen]","DLA","[Deadline] = '" & [caselog]![deadline]) & "'"
If it is a date field:
=DLookup("[Seventeen]","DLA","[Deadline] = #" & [caselog]![deadline]) & "#"
 
P

PeteyP

Thank you.

Klatuu said:
The syntax is all wrong
=DLookup("[Seventeen]","DLA","[Deadline] = " & [caselog]![deadline])
The problem I have is I don't know what [caselog]![deadline] is.
Also, the syntax will vary depending on the data type of the field you are
using for criteria. The above syntax is correct if [Deadline] is numeric.
If it is text:
=DLookup("[Seventeen]","DLA","[Deadline] = '" & [caselog]![deadline]) & "'"
If it is a date field:
=DLookup("[Seventeen]","DLA","[Deadline] = #" & [caselog]![deadline]) & "#"


PeteyP said:
Need something that will pull an item from a large table by matching
something from the first column, then selecting a corresponding field (cell)
from one of 20 neighboring columns on the table.

I have tried:
=(DLookup([DLA]![Seventeen],[DLA],[caselog]![deadline]=[DLA]![Deadline]))
but only get the #Name? response.

I have also tried putting the Dlookup function statement within an IIf
statement.

In Excel, an expression that has worked for me is:
VLOOKUP(D17,LUTable06,17,FALSE),"")
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup In Access 3
VLOOKUP or HLOOKUP function question 1
Using Max or DMax in DLookUp criteria? 1
DLookUp for an Alphanumeric Record 3
Reference function from an out of sort table 5
VLOOKUP/HLOOKUP Question 11
anything like Excel If Statements that I can use with Access Queries? 6
HLOOKUP or VLOOKUP or Index or Match or WHAT? 2

Top