dLookUp Function Help Please!

[bmcgree1]

Okay so I feel like an idiot b/c I can't get this to work but I'm out of ideas. When put into SQL, it works fine, but when on a control, it has issues.

Here's what I have: =DLookUp("Location","tbl_User, tbl_AssetLocation","tbl_User.AssetLocationID = tbl_AssetLocation.AssetLocationID AND tbl_User.UserID = forms!frm_UpdateAssetForm.Userid")

and here's the SQL version that works fine:

SELECT Location FROM tbl_User, tbl_AssetLocation WHERE tbl_User.AssetLocationID = tbl_AssetLocation.AssetLocationID AND tbl_User.UserID = forms!frm_UpdateAssetForm.Userid;

The fact that the SQL works but the dLookUp says "#Error" is very confusing to me. I have all the above refrenced tables, fields, etc (obviously since the SQL works)

Any help is greatly appreciated, thank you.

 

[vbaInet]

Okay so I feel like an idiot b/c I can't get this to work but I'm out of ideas. When put into SQL, it works fine, but when on a control, it has issues.

Here's what I have: =DLookUp("Location","tbl_User, tbl_AssetLocation","tbl_User.AssetLocationID = tbl_AssetLocation.AssetLocationID AND tbl_User.UserID = forms!frm_UpdateAssetForm.Userid")

and here's the SQL version that works fine:

SELECT Location FROM tbl_User, tbl_AssetLocation WHERE tbl_User.AssetLocationID = tbl_AssetLocation.AssetLocationID AND tbl_User.UserID = forms!frm_UpdateAssetForm.Userid;

The fact that the SQL works but the dLookUp says "#Error" is very confusing to me. I have all the above refrenced tables, fields, etc (obviously since the SQL works)

Any help is greatly appreciated, thank you.

if Location is a field in tblUser then do this:

= Nz(DLookup("[tbl_User.Location]", "tbl_User", "[tbl_User.AssetLocationID] = " & [tbl_AssetLocation.AssetLocationID] & " AND " & [tbl_User.UserID] & " = " & Forms![frm_UpdateAssetForm.Userid]), "")

i'm assuming that UserId and AssetLocationID are both integer data types?
if otherwise let me know

 

[bmcgree1]

Location is a field inside tbl_AssetLocation (primary key=AssetLocationID) with a foreign key of AssetLocationID inside tbl_User.

It's now saying "#Name?" instead of error...

 

[vbaInet]

Location is a field inside tbl_AssetLocation (primary key=AssetLocationID) with a foreign key of AssetLocationID inside tbl_User.

It's now saying "#Name?" instead of error...

in that case simply replace
tbl_User.Location with
tbl_AssetLocation.Location

 

[bmcgree1]

hmm.. it's still giving me a #Name? error... do you know why this could be happening?

 

[vbaInet]

hmm.. it's still giving me a #Name? error... do you know why this could be happening?

the syntax was incorrect. try this:

= Nz(DLookup("[tbl_User.Location]", "tbl_User", "[tbl_User.AssetLocationID] = " & [tbl_AssetLocation.AssetLocationID] & " AND [tbl_User.UserID] = " & [frm_UpdateAssetForm.Userid]), "")

 

[boblarson]

You can't reference another table in this.

So you would need a query to pull those together before using the DLookup.

1. You don't include the table name when using the fields in the field spots in the DLookup.

2. You can't use more than one table for this UNLESS the "table" you reference in the DLookup is a query.

3. vbaInet's syntax is off because the second field ([UserID]) has to be INSIDE of the quotes along with the equals sign.

So something like this:

= Nz(DLookup("[Location]", "YourQueryNameHere", "[AssetLocationID] = " & [AssetLocationID] & " AND [UserID]= " & Forms![frm_UpdateAssetForm.Userid]), "")

 

[vbaInet]

You can't reference another table in this.

So you would need a query to pull those together before using the DLookup.

1. You don't include the table name when using the fields in the field spots in the DLookup.

2. You can't use more than one table for this UNLESS the "table" you reference in the DLookup is a query.

3. vbaInet's syntax is off because the second field ([UserID]) has to be INSIDE of the quotes along with the equals sign.

So something like this:

= Nz(DLookup("[Location]", "YourQueryNameHere", "[AssetLocationID] = " & [AssetLocationID] & " AND [UserID]= " & Forms![frm_UpdateAssetForm.Userid]), "")

you are absolutely correct boblarson. i amended it and posted back. and (although not necessary) you can include table names either inside or outside the square brackets. the jet engine will understand the syntax and interpret correctly

 

[SOS]

you can include table names either inside or outside the square brackets. the jet engine will understand the syntax and interpret correctly

I've not had that be the case. It has always given me an error if I do it outside of the quotes.

 

[vbaInet]

I've not had that be the case. It has always given me an error if I do it outside of the quotes.

it has to be inside the quotes but i was referring to the square brackets

 

[bmcgree1]

I forgot that you can't reference tables inside dLookUp, thank you boblarson. I created a query, referenced that and bingo, it works great.

Thank you everyone for all your help!

 

[vbaInet]

I forgot that you can't reference tables inside dLookUp, thank you boblarson. I created a query, referenced that and bingo, it works great.

Thank you everyone for all your help!

Great job bmcgree1. FYI have a look at the definition of a DLookup and its paramaters as given by microsoft. you will find that you can reference a table
http://msdn.microsoft.com/en-us/library/bb148913.aspx

cheers