Query Criteria not working

[Guest]

I hope someone has seen this issue before.
I have a simple database for tracking friends, family and club members.
I have a query to sort them. I have the criteria written like this:
Like [What Type?]&"*"

If I put in any one of the selections like family, or friend, the query
comes up empty.
If I put in nothing, I get all the records that are there.
If I take that criteria out, and put in Like "Family", I still get nothing
in the query.

The only way it works, is if I have no criteria there, or on the first one,
I leave it empty.
I have all the updates for Access. This is access 2003.
I have other databases with the same type of criteria information, and they
work correctly.

Thanks

 

[Guest]

Did you by any chance use the lookup wizard when creating the column in the
table? If so the values in the column in the table won't be what you see in
datasheet view. The actual values will be probably be arbitrary numbers
which reference a numeric primary key column of another table, ContactTypes
for instance, in which the text value swill be in another column.

The above scenario would be consistent with the behaviour you are
experiencing, and if this is the situation what you need to do is include
the ContactTypes (or whatever its called) table in the query, joined on the
relevant columns, and put the parameter in the 'criteria' row of the text
column from the ContactTypes table. To make it optional a better way is to
test for the parameter being Null rather than using the Like operator and a
wildcard character. This allows for Nulls in the foreign key column in the
referencing table, whereas the Like operator would not return such rows. So
you'd put the following in the criteria row:

[What Type?] OR [What Type?] IS NULL

You'll find that if you do this and save the query and later reopen it in
design view again Access will have moved things around. The result will be
the same, however.

If the use of the lookup wizard is the culprit here, you won't be surprised
to learn that most Access developers regard this feature not only as
pointless, but also as an ill-advised and misleading inclusion in the Access
table design interface, which should never be used.

Ken Sheridan
Stafford, England

 

[Guest]

Ken

Thanks. That was it. As soon as I read that, I realized what I had did.

Thanks for the quick reply.
Todd

Did you by any chance use the lookup wizard when creating the column in the
table? If so the values in the column in the table won't be what you see in
datasheet view. The actual values will be probably be arbitrary numbers
which reference a numeric primary key column of another table, ContactTypes
for instance, in which the text value swill be in another column.

The above scenario would be consistent with the behaviour you are
experiencing, and if this is the situation what you need to do is include
the ContactTypes (or whatever its called) table in the query, joined on the
relevant columns, and put the parameter in the 'criteria' row of the text
column from the ContactTypes table. To make it optional a better way is to
test for the parameter being Null rather than using the Like operator and a
wildcard character. This allows for Nulls in the foreign key column in the
referencing table, whereas the Like operator would not return such rows. So
you'd put the following in the criteria row:

[What Type?] OR [What Type?] IS NULL

You'll find that if you do this and save the query and later reopen it in
design view again Access will have moved things around. The result will be
the same, however.

If the use of the lookup wizard is the culprit here, you won't be surprised
to learn that most Access developers regard this feature not only as
pointless, but also as an ill-advised and misleading inclusion in the Access
table design interface, which should never be used.

Ken Sheridan
Stafford, England

I hope someone has seen this issue before.
I have a simple database for tracking friends, family and club members.
I have a query to sort them. I have the criteria written like this:
Like [What Type?]&"*"

If I put in any one of the selections like family, or friend, the query
comes up empty.
If I put in nothing, I get all the records that are there.
If I take that criteria out, and put in Like "Family", I still get nothing
in the query.

The only way it works, is if I have no criteria there, or on the first one,
I leave it empty.
I have all the updates for Access. This is access 2003.
I have other databases with the same type of criteria information, and they
work correctly.

Thanks