Singel Table Query With lookup table not working.

[Barryg]

Hey everyone,
This will be my first post.
I am using Microsoft Access 2007 – 2010 on Widows 7 and am trying to run a query on a single table. I am getting this msg: Data type mismatch in criteria expression.
The column that I am trying to query is the tblNPI.Status column where I want the query to return any Status other than the status of pending.


Caveat: The table tblNPI is the main table and I have a second table called tblStatus that contains 16 different statuses. The main table field for Status is defined as a Lookup field where I can quickly use the drop down to select a status from the tblStatus table. (This is where I am suspecting my issue)
I have tried several different methods to no avail. Or instance in thee WHERE statement, I have tried (in the query design view mind you) of the Status field for criteria these: NOT “Pending”, <> “Pending”, Not [Pending] , <> [Pending], Not [“Pending”], Not [‘Pending’], <> ‘Pending’, Not [‘Pending’]
The query (in SQL view) looks like this for <>“Pending” in the design view:
SELECT tblNPI.NPI_ID, tblNPI.Customer, tblNPI.Assembly, tblNPI.JobNumber, tblNPI.Status
FROM tblNPI
WHERE (((tblNPI.Status)<>"Pending"));

I have four other fields called in this query as you can see in the code.

Thank you for your reviews in advance.

[ItsMe]

The value of your lookup field is not literal text. It is the index of an array. Try using a number in your query. WHERE (((tblNPI.Status)<>2));

[Barryg]

YOU ARE AWESOME...
I guess I was not looking at it from that light. Thank you so much. Your advice solved my problem.

Next question is Can I use multiple numbers here? For example would I use WHERE (((tblNPI.Status)<>1,2,));

[ItsMe]

No, you will need to use the Or operator. the query builder can help you with this. Just keep adding numbers in the criteria field for Status. Only, when you add another criteria, use the field that is available just below the previous. This will create OR vs. creating AND.

[JoeM]

Next question is Can I use multiple numbers here? For example would I use WHERE (((tblNPI.Status)<>1,2,));

Actually, you can! Like this:

WHERE tblNPI.Status Not In (1,2);

[ssanfu]

You might want to read about "Look up FIELDS" (not look up tables)
http://access.mvps.org/access/lookupfields.htm

And

http://access.mvps.org/access/tencommandments.htm


I never use lookup FIELDS....

[ItsMe]

There you go, I stand corrected. Just goes to show you there are disadvantages to depending on the query builder.

[JoeM]

There you go, I stand corrected. Just goes to show you there are disadvantages to depending on the query builder.

You can actually just type:

Not in (1,2)

in the Criteria row of that field in the Query Builder (that is how I usually do it).

But you are right in saying that Query Builder does have some limitations, i.e. you can do Union Queries in Access, but you'll have to do them in SQL View, and not in Query Builder View.

[Barryg]

Ok,
So let me understand this. I used the term look up field, when I really should have said that the data type is "Look Up Wizard" when setting the data type in this example my Status. Does this change your statement about Look ups? I need to make sure that I get this now before my database is too big to change.

Thank you for the valuable input from all.

[ssanfu]

I used the term look up field, when I really should have said that the data type is "Look Up Wizard" when setting the data type in this example my Status. Does this change your statement about Look ups?

From Help: "Lookup Wizard: Creates a lookup column in a table, which displays a list of values the user can choose from. "
Yes, that is the same thing.

If, when designing the fields in a table (table design view), you change the field from a text box to a combo box (via the Look up tab), that is a "look up field".
Example: you have a field named "IDNumber" that is a Long Integer, but displays text when viewing the table in datasheet view. That is a "Look up Field".

Even if you have a "look up field" defined in a table, if you use forms and want to use that field on the form, you need to add a combo box control and configure it. The look up field has gained you nothing - just used up time.

I have one or more tables that are the source(s) for combo boxes. These are what I term "look up tables". They might have relationships with other tables or they might be stand alone tables.

If you decide to upsize to SQL, SQL Express, MySQL, Oracle, etc and you have "LookUp FIELDs", you will be unable to complete the upsizing because those databases do not recognize look up fields.


My rules are:
- Data entry/data viewing is *always* through forms. Users never see tables.
- Forms/subforms are never in datasheet view. If I need datasheet view, I create my own: continuous form with controls spaced tightly together. I have more control over what is entered/edited.
(Yes, I am a control freak) (Pun intended)


My $0.02...

[Barryg]

Thank you Steve, this is valuable information for me. This is truly my first stab at a database that will be used for work purposes.
I guess I will need to remove the look up fields.

[ItsMe]

For me, datasheet view is not that bad. I am a control freak also. Probably not in a punny way though

If I decide a user needs an overview that DS or continuous offers, I will usually maintain constraints by using a Main form Subform link/relationship and lots of code. Did I mention lots of code? So, like Steve is describing, Continuous forms better pay dividends because it is extra work on the development side of things. Users seem to like DS view. If there is a measurable benefit, I will write the code to make it happen.