How to Use Criteria in a Query

[dgaletar]

Hello. I am trying something new, and (of course) I am stuck!

Here is (among a LOT of other things) what I have:

• A "Vehicles" table that contains (among a LOT of other things) the following:

PK?	Field Name	Data Type	Row Source
PK	CustomerID	Number
	Department	Number	SELECT [Department].ID, [Department].Department FROM Department ORDER BY [Department];
	AlternateID	Short Text

• A "Service" table that contains the following:

PK?	Field Name	Data Type	Row Source
PK	ServiceID	AutoNumber
	CustomerID	Number
	Date	Date/Time

• A "qryMaxPublicSafetyDates" query that looks like this:

The problem is that when I run this query, I get zero results! If I instead remove the "Like Public Safety" criteria option, it runs fine, except with all of the results.

My goal here is to run a query that returns ONLY the results from the "Public Safety" Department, and a second that returns ONLY the results from everyone EXCEPT the "Public Safety" Department.

If you help me to figure out the first one, I know that I can easily accomplish the second.

I'm not sure, but I think that it has something to do with where the criteria "Department" comes from. I mean, the fact that it is a "look up" field in the "Vehicles" table, could be the issue. So I created a named "Vehicles by Dept" that sorts all of the departments out along with the vehicles that belong to them, but that didn't work either.

Any and ALL suggestions would be GREATLY appreciated!

Thx,

DG

[haissk]

Instead of Like Public Safety, if you put "= Public Safety" does it work, if it works then the problem is with like creteria.
Try Like "*Public Safety*"

[dgaletar]

"= Public Safety" produces an error - "Data Type Mismatch in Criteria Expression".

Like "*Public Safety*" returns no results.

[haissk]

Sorry i see that Department is a numeric field, or i am not able to get your exact tables, is there any way you post the Access file

[dgaletar]

No, your right. I think that it is a numeric field. It's like a "lookup" field I think. Sorry, but the file is protected and large, so I won't be able to do that. Any ideas???

[haissk]

You cannot apply like creteria on a Numeric field. Are you sure the Department field has the text values in it?

[dgaletar]

No, I'm sure that it DOESN'T have the text values in it! It has a numeric field. Do I need to change it???

[dgaletar]

I just went in and changed the field type from a "Numeric" field to a "Short Text" field. I then tried the query again and nothing... same end results. :-(

[haissk]

Sorry cant help until you give the exact table structure and values

[ItsMe]

I did not read the whole thread but if Department is a Lookup Field in a table you need to determine the index for the specific department name you are after.

Look in the table properties for the department field. Look at the value list and determine the index you need.

So your criteria may be
=1
Or
=4
Or
=0