By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Compairing dates

P: 2
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?
Aug 8 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport Report, acPreview, "", "[Date Due] between #" & Date_From.Value & "# and #" & Date_To.Value & "#"
Aug 8 '07 #2

P: 2
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 ^_^
Aug 8 '07 #3

ADezii
Expert 5K+
P: 8,669
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!
Aug 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.