Lookup Wizard Issue

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Lookup Wizard Issue

Post by Leesha »

Hi,
I just started using the lookup Wizard when creating tables and thought it was pretty neat until I went to create a query and keep getting type errors re the join. When I view the data in the query it looks like text but I'm assuming it's actually a number field that corresponds to the ID in the table the info is coming from. Nothing I do works. I'd really hate to have to undo all of these wizards I set up.
I'm lost!
Thanks,
Alicia

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Lookup Wizard Issue

Post by HansV »

Most Access MVPs recommend NOT to use the Lookup Wizard in tables, exactly for the reason you mention: when you look at a field in datasheet view, it appears to be a text field, but it actually is a number field, which can be very confusing.
But it should still be possible to use the tables, as long as you are aware of the lookup properties.
For example, if you want to join the field to a field in another table, the other field must be a number field too.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Lookup Wizard Issue

Post by Leesha »

Ugh, I had a feeling you'd say that lol. What would the code look like if I wanted to convert a cell name that is a number format to a string, in a query.
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Lookup Wizard Issue

Post by HansV »

Here is an example:

S1200.png

fkeyCategoryID is a number field, but its Display Control property is set to Combo Box. The first column of the combo box is hidden by setting the Column Widths property to 0, so you'll see a text value:
S1201.png

In a query, you can join the fkeyCategoryID field to the primary key of the tblCategories table, and add the "real" text field strCategoryName to the query grid:

S1202.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Lookup Wizard Issue

Post by Leesha »

Thanks!