Compairing dates

[Possum]

Hi Chaps,

not sure if this is the right place to post this (might need to be on the VB forums) but I am currently working on a documentation database at work to store action documents on an ongoing basis. I have created forms and coded them all up in VB but here is my problem.

One of the fields in this database is the "Date Due" on any action and one of the main system requirement is to be able to search for actions due between date a and date b and produce a report.

At the moment i am using this code
DoCmd.OpenReport Report, acPreview, "", "[Date Due] between'" & Date_From.Value & "' and '" & Date_To.Value & "'"

Date Due is stored as a Date/Time type and formated as a short data, there are no null or "" values. Date_From and Date_to are DTpicker objects also formated as shortdate. I have also tried using > date from and < date to instead of between but no luck there either.

Every time i try this i get a data type mismatch error, anyone know how i can fix this?

[ADezii]

Hi Chaps,

not sure if this is the right place to post this (might need to be on the VB forums) but I am currently working on a documentation database at work to store action documents on an ongoing basis. I have created forms and coded them all up in VB but here is my problem.

One of the fields in this database is the "Date Due" on any action and one of the main system requirement is to be able to search for actions due between date a and date b and produce a report.

At the moment i am using this code
DoCmd.OpenReport Report, acPreview, "", "[Date Due] between'" & Date_From.Value & "' and '" & Date_To.Value & "'"

Date Due is stored as a Date/Time type and formated as a short data, there are no null or "" values. Date_From and Date_to are DTpicker objects also formated as shortdate. I have also tried using > date from and < date to instead of between but no luck there either.

Every time i try this i get a data type mismatch error, anyone know how i can fix this?

You are treating the Date Picker values as Strings by delimitiing them with Single Quotes ('). The proper Delimiter for Date Fields is the pound (#). Try the following adjustment, and let me know how it works:

DoCmd.OpenReport Report, acPreview, "", "[Date Due] between #" & Date_From.Value & "# and #" & Date_To.Value & "#"

[Possum]

Worked like a charm, strange the Date Due variable was in mm/dd/yyyy format and the date pickers were in dd/mm/yyyy but i got round that.

Thanks alot
One happy Possum ^_^

[ADezii]

Worked like a charm, strange the Date Due variable was in mm/dd/yyyy format and the date pickers were in dd/mm/yyyy but i got round that.

Thanks alot
One happy Possum ^_^

We like to see happy Possums here at TheScripts!