Using criteria in Query

[Bill]

Access 2007.

I have created a field in a table using the lookup wizard and it works ok
when filling out the input data sheet. I get the drop down combo box to pick
from

However when I run a query on the table to extract data I cannot get it to
work. I keep getting an error message that the input type does not match the
field data type.

When I look back at the data field type created by the look up wizard in the
table it has it as "number" However the query parameter does not have any
number type listed. The query criteria input is text format

Hope I explained that ok

Any help appreciated
Thanks
Bill

 

[Jeff Boyce]

Bill

I'm going to read between the lines of your description and guess that you
created a lookup data type in a table (OK, so I really didn't read between
anything). Although Access offers that, the general consensus in these
newsgroups (and in .tablesdbdesign, in particular), is that the lookup
datatype causes more confusion than it solves.

The reason your query isn't working is because you expect to be able to use
the values showing in that field as the selection criteria. Guess what?!
The values stored in that field are actually the underlying rowIDs, not the
values displayed!

The preferred option is to store (and display) the rowID itself (i.e., don't
use the lookup datatype) when in the table, and to use a combobox on a form
to display (and look up) values.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Access 2007.

I have created a field in a table using the lookup wizard and it works ok
when filling out the input data sheet. I get the drop down combo box to pick
from

However when I run a query on the table to extract data I cannot get it to
work. I keep getting an error message that the input type does not match the
field data type.

When I look back at the data field type created by the look up wizard in the
table it has it as "number" However the query parameter does not have any
number type listed. The query criteria input is text format

Hope I explained that ok

Any help appreciated
Thanks
Bill

This is one of several reasons that lots of us REALLY despise the Lookup
Wizard misfeature. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

The Lookup Field actually stores a numeric ID in your table, but *displays*
the text from the lookup table. What you see is actually not what is there,
hence your understandable confusion!

You can still do your query, but you will need to do what the lookup wizard
isn't smart enough to help you with: create a Query joining your table to the
lookup table, and put the criterion on the text field from the lookup table.

An alternative is to create an unbound Form, frmCrit say, to get the user's
criteria. You can put a combo box (again, unbound, nothing in its Control
Source) on the form - cboMyLookup let's call it - and use a criterion on the
number field in your table

=[Forms]![frmCrit]![cboMyLookup]

The user will see the text, the query will see the number, and they'll both be
happier.

 

[Bill]

Access 2007.

I have created a field in a table using the lookup wizard and it works ok
when filling out the input data sheet. I get the drop down combo box to
pick
from

However when I run a query on the table to extract data I cannot get it
to
work. I keep getting an error message that the input type does not match
the
field data type.

When I look back at the data field type created by the look up wizard in
the
table it has it as "number" However the query parameter does not have any
number type listed. The query criteria input is text format

Hope I explained that ok

Any help appreciated
Thanks
Bill

This is one of several reasons that lots of us REALLY despise the Lookup
Wizard misfeature. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

The Lookup Field actually stores a numeric ID in your table, but
*displays*
the text from the lookup table. What you see is actually not what is
there,
hence your understandable confusion!

You can still do your query, but you will need to do what the lookup
wizard
isn't smart enough to help you with: create a Query joining your table to
the
lookup table, and put the criterion on the text field from the lookup
table.

An alternative is to create an unbound Form, frmCrit say, to get the
user's
criteria. You can put a combo box (again, unbound, nothing in its Control
Source) on the form - cboMyLookup let's call it - and use a criterion on
the
number field in your table

=[Forms]![frmCrit]![cboMyLookup]

The user will see the text, the query will see the number, and they'll
both be
happier.

John and Jeff thanks for that . It was driving me crazy

I am no expert at this but will try your suggestions

Thanks
Bill

 

[Bill]

Thanks Jeff.

You understood my issue perfectly

I will rework what I am trying to accomplish and incorporate the form

Bill