Update with a DLookup

[Paul H]

I'll admit this right off, dlookups bother me. I use them all the time, but they bother me. Sometimes they work fine. This is not one of them. Here goes.

Here's the function as is

DLookUp("[User_ID]","tblUser"," Left([tblOpenItemComments]![Vendor_Name],1) between [From] and [To]")

I have also tried this way with the same results.

DLookUp("[User_ID]","tblUser"," Left([Vendor_Name],1) between [From] and [To]")

The expression you entered as a query parameter caused the following error. Microsoft Access cannot name ([tblOpenItemComments]![Vendor_Name] you entered in the expression.

I have it in a SELECT query just for testing, but I will be using to update a field in tblOpenItemComments. Here's the SQL.

SELECT User_ID, 
Vendor_Name, 
Left([Vendor_Name],1) AS Initial, 
DLookUp("[User_ID]","tblUser"," Left([tblOpenItemComments]![Vendor_Name],1) between [From] and [To]") AS UserID

FROM tblOpenItemComments

WHERE tblOpenItemComments.User_ID Is Null AND tblOpenItemComments.Vendor_Name) Is Not Null

I suspect this is the wrong way of going about this. I even suspect what the problem is, but for the sake of time tell me if this is a dead end or not would be helpful.

[June7]

Maybe you should explain what you are trying to do, because I don't understand.

The DLookup will return the first User_ID encountered that matches the criteria. Since you already have User_ID in the data, why do you have to look for it?

[John_G]

Hi -

I don't see how the DLookup will work at all. You are doing a lookup on the table tblUser, but your criteria are referencing data from a different table. In order for DLookup to work as expected, your criteria have to reference field(s) in tblUser that will identify which record to retrieve data from.

What is the data you are trying to get from tblUser, and how are you identifying it, i.e. the table row?

[June7]

Actually, the criteria is referencing field of the query source table but the reference is within quote marks and should probably instead be concatenated. At first glance I also thought another table was referenced.

However, still don't understand why the DLookup is used at all.

[Paul H]

Actually the query I posted is misleading. The User_ID field in this table is empty. I will be updating it based on the result from the DLookup. Once I get it to work I'll use it in an update query which will look something like this.

UPDATE tblOpenItemComments SET tblOpenItemComments.User_ID = DLookUp("[User_ID]","tblUser"," Left([Vendor_Name],1) between [From] and [To]") 
WHERE tblOpenItemComments.User_ID Is Null AND tblOpenItemComments.Vendor_Name Is Not Null;

When I run it this way I just get a generic error "Unknown".

It doesn't seem to matter whether I put the table name in the DLookup or not. Does that help?

[Paul H]

Put simply, I can't see to pass this value, Left([tblOpenItemComments]![Vendor_Name],1) to the function.

[June7]

Concatenate.

Vendor_Name is a field in tblOpenItemComments?

From and To are fields in tblUser?

UPDATE tblOpenItemComments SET tblOpenItemComments.User_ID = DLookUp("[User_ID]", "tblUser", "'" & Left([Vendor_Name],1) & "' Between [From] And [To]")
WHERE tblOpenItemComments.User_ID Is Null AND tblOpenItemComments.Vendor_Name Is Not Null;

Why would you do the lookup with a single letter as the parameter?

[Paul H]

1. Concanonete, that work.

1. Yes

2. Yes

3. This worked.

4. Because users are assign work base on the first letter of the vendor name.

User1 = A - D
User2 = E - J
etc.

Thanks for your patience.

Paul