Help please! unexpected results from query

[Walshie1987]

I've got a db containing 12 linked speadsheets (Jan - Dec) and have then used a UNION ALL query to consolidate all od the data, and have then ran a further query (filtering specific dates) based on the UNION ALL Query.

But I am not getting the correct results. Even though the data in my linked tables is in date format, and the combo box im using is in the same, the query only seems the be looking at the first 2 digits of the date (DD)e.g looking for date range 01/01/2007 - 10/01/2007 and query is returning every date that starts with anything between 1 and 10, 02/03/2007, 06/11/2007 etc.


Cheers

 

[RivetHed]

Post up your SQL.

 

[Walshie1987]

Not sure if this is right, first attempt at Union query

SELECT [Customer], [Consolidated invoice#],[Invoice Number],[Invoice date],[Order#],[Order Line],[Customer PO#],[Ship-to Code],[Ship-to Suffix],[Item Number],[Description],[User ID],[Department:],[SE Order Number:],[Machine #:],[IT Purchase?],[Prod/Svcs:],[H&S Purchase?],[Shift],[Requester Name],[Quantity],[Unit of Measure],[Price],[Extended Value],[Freight],[Tax Rate],[Tax],[Line Total] from Jan07 WHERE Customer =2579

UNION ALL SELECT [Customer],[Consolidated invoice#],[Invoice Number],[Invoice date],[Order#],[Order Line],[Customer PO#],[Ship-to Code],[Ship-to Suffix],[Item Number],[Description],[User ID],[Department:],[SE Order Number:],[Machine #:],[IT Purchase?],[Prod/Svcs:],[H&S Purchase?],[Shift],[Requester Name],[Quantity],[Unit of Measure],[Price],[Extended Value],[Freight],[Tax Rate],[Tax],[Line Total] from Feb07 WHERE Customer =2579

then same for Mar07, Apr07 etc.

I'm getting all the results displayed from the above SQL absolutly fine. Just when I run the query based on the results I get the prob.

 

[RivetHed]

All looks ok. What's the SQL for the query you're running off the results?

 

[Walshie1987]

SELECT qryAll.Customer, qryAll.[Consolidated invoice#], qryAll.[Invoice Number], qryAll.[Invoice date], qryAll.[Order#], qryAll.[Order Line], qryAll.[Customer PO#], qryAll.[Ship-to Code], qryAll.[Ship-to Suffix], qryAll.[Item Number], qryAll.Description, qryAll.[User ID], qryAll.[Department:], qryAll.[SE Order Number:], qryAll.[Machine #:], qryAll.[IT Purchase?], qryAll.[Prod/Svcs:], qryAll.[H&S Purchase?], qryAll.Shift, qryAll.[Requester Name], qryAll.Quantity, qryAll.[Unit of Measure], qryAll.Price, qryAll.[Extended Value], qryAll.Freight, qryAll.[Tax Rate], qryAll.Tax, qryAll.[Line Total]
FROM qryAll
WHERE (((qryAll.[Invoice date]) Between [forms]![main]![cmbSDate] And [forms]![Main]![cmbEDate]) AND ((qryAll.[Line Total]) Between [forms]![Main]![cmbSDate] And [forms]![Main]![cmbEDate]));

 

[Spenney]

Looks like the query is interpreting the dates as text. Are the dates in the Excel sheet formatted as dates?

 

[Walshie1987]

the dates in excel were originally in text (yyyymmdd), but i created a macro to do a "data to column" conversion and change them to dd/mm/yyyy (date format), i've looked in the properties of the linked tables in access and it is recognising them in date/time format

 

[Spenney]

My next attempt at trouble shooting would be seeing what results you get by simply entering the dates directly into the query (rather than using the values on your form) to help understand how the criteria is being applied by the query.

It doesn't look like your problem is caused by any ambiguity in the date format but it's worth checking the regional date settings of the PC to ensure they are dd/mm/yyyy?

Finally, you may find the DateSerial function useful in getting around such ambiguity. See this thread...

http://www.tek-tips.com/viewthread.cfm?qid=1405749

 

[Walshie1987]

Thanks for the above, will have a play around with it this afternoon see what I can figure out

Cheers
Chris

 

[Walshie1987]

Just had a quite look at the db, tried putting #dd/mm/yyyy# in the year and changing the format but still no look.

I did set the query to show records that = one of my combo boxes and it shows the correct data each time so the format must be correct.

Its got to be something to either do with the UNION ALL Query or my between SQL.

Will get there eventually!!

 

[randy700]

Is your query in a module or did you create it using the query builder?


Randy

 

[Walshie1987]

I created the query in query builder