Create a query when table has a look up in it

[Chris S]

Hi There - I am trying to create a basic query that excludes certain records.

My table has a field that is status. This is a lookup value that I created
using the wizard and it is stored as a number field type. I am trying to
create a simple query where I exclude one of the status types. However, I
keep getting the "Data type mismatch criteria expression" error. I am not
sure how to go about fixing this since my field in my table is a look up?

 

[Chris S]

Thank you for responding. I tried the idea you provided below and that does
not work either. Any ideas on how better to have selections for a field in a
table other than the lookup wizzard that will not cause this issue?

 

[John W. Vinson]

Thank you for responding. I tried the idea you provided below and that does
not work either. Any ideas on how better to have selections for a field in a
table other than the lookup wizzard that will not cause this issue?

The solution is...

Don't use Tables that way.

Tables have one purpose, and one purpose ONLY: data storage.

They're not designed for data entry, they're not designed for data editing,
they're not designed for searching. They're designed for *data storage*.

Use a Form to interact with the data. You can (and should!) put combo boxes
and other such *data interaction* tools on your Form, to store the numbers,
foreign keys, etc. in your Table. You only need to look at the Table for
debugging and design work; users should never need to see one.

 

[David W. Fenton]

Firstly, for future reference avoid the 'lookup wizard' like the
plague.

I implemented code to do just that in a cleanup utility that I wrote
a little more than a week ago:

http://dfenton.com/DFA/download/Access/DataRename.zip

The lookup deletion code came from here:

http://www.xtremevbtalk.com/archive/index.php/t-170060.html