Hi !
Running Access2002 why my criteria qry wont show correct result with more than two fields as criteria? Has anyone had this problem? I have to transform a two criteria qry into a table and run the last
criteria on this table to have the correct result.
Any suggestion will be welcome.
Regards Penta.

Penta-

It's hard to figure out what you might be doing wrong without knowing the SQL of the query you tried or the structure of your tables. Please post the SQL from your query that doesn't work. Open the query in Design view, choose SQL View from the View menu, and paste the text you see into a reply.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

Dear John:
Now it is running.Probably afraid of your proficience.I posted the topic because
it has been happening several times with different tables.Anyway i have split
the qry in two qrys and i run a maketable qry on the first qry before i reach
the result: when the problem persists.The qry is:
SELECT tblSales.*
FROM tblSales
WHERE (((tblSales.fkCompanyID)=10) AND (([tblSales]![DateDispatched]-[tblSales]![DateOrdered])>7) AND ((tblSales.DateOrdered)>=#11/1/1998# And (tblSales.DateOrdered)<=#11/30/1998#));

Thank You very much for ur time and care.
Truly P.

Penta-

Hmmm. I'm suspicious that AND is capitalized in one place but not another - as though the query is performaing a logical AND in the wrong place. I hate the extra parens that the query builder throws in. I would write the predicate like this:

(tblSales.fkCompanyID = 10) AND
(([tblSales]![DateDispatched]-[tblSales]![DateOrdered])>7) AND
(tblSales.DateOrdered >= #11/1/1998#) AND
(tblSales.DateOrdered <= #11/30/1998#)

Are you running on a U.S. machine? Access SQL requires date literals in strict mm/dd/yyyy sequence, and this can get confused on a machine that uses a different format for short date. Also, if your DateOrdered field can possibly contain a time component, you should change the last criteria to:

(tblSales.fkCompanyID = 10) AND
(([tblSales]![DateDispatched]-[tblSales]![DateOrdered])>7) AND
(tblSales.DateOrdered >= #11/1/1998#) And
(tblSales.DateOrdered < #12/1/1998#)



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

John:
I have a dd/mm/yyyy as date literals.
I will follow ur advise although i dont know SQL. I have English Access
running in Brazilian language.
Thanks u very much again,
Truly P.

Penta-

When you design a query using the query grid on a non-U.S. machine, you should see the dates displayed in dd/mm/yyyy format, but the SQL should be corrected to mm/dd/yyyy. So, you should see, for example, #1/11/1998# on the query grid but #11/1/1998# in SQL View. You should make a copy of your query, switch to SQL view, and try replacing the WHERE clause with what I suggested earlier. If you see a different layout when you switch back to Design view, then you had the criteria laid out incorrectly on your grid.



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

John-
I will do it.
By the way ur book about SQL is out of print.
Thanks u very much again,
Truly P.

Penta-

No, the "Mere Mortals" book just had its eighth or ninth printing. Amazon has it and ships in 24 hours:

http://www.amazon.com/exec/obidos/AS...escaconsulinc/



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

I had just checked Amazon before sending the post and the site probably was not
updated. Now the status has changed. I will look for it as soon as possible.
Thanks John,
Truly P.