dlookup returning #Type!

[pyromaniac511]

I have a text field that was returning an ID instead of the represented name so I used

=DLookUp("Brand_Name","tblBrands","Brand_ID = " & [Brand_ID])

in the control source. It returned #Type!

I checked myself by using

=DLookUp("Brand_Name","tblBrands","Brand_ID = 2")

and it returned the name. What am I doing wrong?

tblBrands
- Brand_ID
- Brand_Name

tblItems


- Item_ID
- Brand_ID
- Model_ID
- Item_SN
- Item_Location

[pbaldy]

I'd consider this if appropriate:

BaldyWeb - Autofill

To make the DLookup() work, I'd double check what the Brand_ID field contained. If it's a combo, it may not be what you see.

[John_G]

It might not be the DLookup itself, but what you are trying to do with it.

Is the text field (control) bound to a table field? If so, what is the data type of that field?

[pbaldy]

Oh, and if the textbox has the same name (Brand_ID), change it.

[pyromaniac511]

I am assuming it is bound since it is trying to talk to another table but I have no Idea. I changed the textbox name to txtBrandID.

... Ok, I just copied the code that didn't work and put in =[Brand_ID] to confirm I was getting a number. I then pasted the failed code in and it worked. Is access always this qwarky?

[pbaldy]

Is access always this qwarky?

Yes.

Glad you got it working.

[RuralGuy]

I am assuming it is bound since it is trying to talk to another table but I have no Idea. I changed the textbox name to txtBrandID.

... Ok, I just copied the code that didn't work and put in =[Brand_ID] to confirm I was getting a number. I then pasted the failed code in and it worked. Is access always this qwarky?

If you mean by qwarky that Access gets confused when you have a Control on a form and a Field in the Recordset of the same form with the same name than I support Paul's answer. I think I would be confused as well.

[pyromaniac511]

Maybe, I'm pretty new to access. In MySQL this wasn't an issue and I grew to like matching my names (obj_ID = obj_ID). It makes remembering what I was thinking easier later on. I would think the way it is structured would prevent any confusion since DLookUp("Brand_Name","tblBrands","Brand_ID") is looking for Brand_ID inside table tblBrands and is oblivious to anything outside that table. [Brand_ID] ... ok I don't know what the brackets mean but figured they had something to do with the local page.

Are access resources not oblivious to each other?

[RuralGuy]

Depending on the context [brackets] usually mean a field rather than a control but I believe there are exceptions. Many developers have simply adopted techniques that minimize these sort of issues.

[pbaldy]

As to your confusion, the part outside the quotes:

=DLookUp("Brand_Name","tblBrands","Brand_ID = " & [Brand_ID])

is not "looking for Brand_ID inside table tblBrands", it's trying to find the value that you want to find within tblBrands. This is the point where Access can get confused, as it tries to find the field/control/variable you've specified.