Dates

[copaaccess]

I am using MS access via OBDC to a DB.

A column in a table contains dates, however they are in the format DD/MM/YYYY and DD/MM/YYYY HH:MM:SS.

I have a query (main) that calls other queries (sub), and the Main query requires the user to input a From and To date.

The results of the query exclude the records that have HH:MM:SS for the To date.

How can I ensure I retrieve the missing dates?

I have tried converting the date to text by the of the Day, Moth, Year function, and then converting it back with DateValue. ie

td: DateValue(Day([add_date]) & "/" & Month([add_date]) & "/" & Year([add_date]))

If I place the date range in the criteria field ie "between #01/01/2012# and #05/01/2012#", the query works fine and the results back are as expected. If I change the criteria to "between [From] and [To]" and then run the query and I put in 01/01/2012 for From and 05/01/2012 for To, I get all records with a value of DD equal to 01, 02, 03, 04, 05, and MM and YYYY every combination that is in the data ie 01,02,03,04,05,06,07,08,09,11,12 and 2012, 2011, 2010 etc

All the queries are built in the Design section and not in VB

I am at wits end

Thanks

[June7]

What is the backend database?

[copaaccess]

DB2, On set of dates is added for a user process ie with time, and the other without time is added by a batch upload

[June7]

You say dates are formatted as DD/MM/YYYY but it is possible that they are actually stored as MM/DD/YYYY. I do work with some db4 tables and that is my experience. Where is the DD/MM/YYYY format set - in db2 or Access?