Criteria in query is disregarded

[Victor70]

Hello, Access experts.

I have an issue with my Access queries. Whenever I link an external table from one of our data warehouses and run a query off it, I receive much more (if not all) records than I was supposed to with the criteria I have entered.

I have never seen anything like that, and neither the guys I am working with. My two hypothesis of what could affect the results are my credentials (this problem happens only if I create a database and/or I link the table, queries in databases created by others perform fine) or my choice of unique key fields that Access asks me for when I link the table (I played with that and discovered that this may matter).

What complicates this is that neither of my peers had any problem even when they do not select any key fields when linking the table. I had also a problem when I just saved someone's database under a different name. The table was linked already and I did not get to pick fields on my own.

SQL Developer or Toad for Data Analysis work fine and they never ask me to pick the fields when linking.

The solution may very simple but I ran out of explanations. I would really appreciate your ideas. :banghead:

Thank you.

 

[jdraw]

Show the query sql, some sample data, and the query result.

 

[Victor70]

Jdraw, thank you for your response!

This is the query. It would be hard to mess up with the one as simple as this.

SELECT INVCTRL.PO_NO, INVCTRL.REC_DATE
FROM INVCTRL
WHERE ((INVCTRL.REC_DATE)>=#1/1/2012#);

I really cannot provide a sample file because I am experiencing the problem with linked tables in our internal data warehouse. I don't know if it is possible at all to solve a problem without having a sample file, but any suggestions are greatly appreciated.

The table looks like this. The dates are of Date/Time type.
PO_NO LINE_NO ORDER_TYPE ORDER_DATE REC_DATE....
P11111 4 P 7/21/2004 8/2/2004
P22222 1 P 7/21/2004 8/2/2004
P33333 2 P 7/21/2004 8/2/2004
P44444 3 P 7/21/2004 8/2/2004
.....

My query gives me this
PO_NO REC_DATE
P94678 8/6/2004
P94679 8/6/2004
P94679 8/6/2004
P94679 8/6/2004
.....

 

[jdraw]

With the sample data you have given, you will get 0 records from the query.

Your criteria says REC_DATE greater or equal 1/1/2012, but all test records have dates in 2004, so none qualify.

When we ask for test data or an example, at least make it consistent.
Your result set has PO_NO which is not in your sample data, and as I said before, the dates exclude records from the result set.

 

[Victor70]

Thank you very much for your responses.
Apologies for being inconsistent with my data sample.

I have the same problem not only with date fields.

Pat, I think you are right that PKs matter. I just know that my peers sometimes even skip this step, and they don't have this issue. Moreover, SQL Developer or Toad never ask me to define one. My thinking is that my Access is somehow more dependent on PKs and/or defaults to a wrong PK (or none) when I create a database without choosing the fields for the key.

 

[Victor70]

Pat, thank you.