terpsichore
asked on
Date between in Access Query
Dear experts -
I'm getting an error on the following part of a query:
I build a string that include a 'between two dates' component:
strsql = strsql & " AND (" & "datevalue(expdate) between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"
expdate is a datetime field.
The reason I'm trying to use it, is that I want to grab all records where that date - regardless of the time - falls between the two specified dates.
The error I'm getting - definitely attributable to this line - is:
DATA TYPE MISMATCH IN CRITERIA EXPRESSION.
Thanks -
I'm getting an error on the following part of a query:
I build a string that include a 'between two dates' component:
strsql = strsql & " AND (" & "datevalue(expdate) between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"
expdate is a datetime field.
The reason I'm trying to use it, is that I want to grab all records where that date - regardless of the time - falls between the two specified dates.
The error I'm getting - definitely attributable to this line - is:
DATA TYPE MISMATCH IN CRITERIA EXPRESSION.
Thanks -
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I want to be sure to be IGNORING ALL TIMES - and only look at dates - the expdate is a datetime field and I had been having issues with some records being dropped - maybe that doesn't apply in SQL query?
I think it's your wording. Without testing it here's what I'm thinking.
strsql = strsql & " AND (" & datevalue(expdate) & " between #" & me.txtdatefrom & "# and #" & me.txtdatato & "#)"
I think it's the quotes on the datevalue call.
Give that a try.
-Sarah
strsql = strsql & " AND (" & datevalue(expdate) & " between #" & me.txtdatefrom & "# and #" & me.txtdatato & "#)"
I think it's the quotes on the datevalue call.
Give that a try.
-Sarah
What is the actual value of strsql after you have built the expression?
ASKER
I changed datevalue to int and it seems to work fine... any idea why that would be?
I guess I misread your problem. Are you using the function DateValue?
Datevalue takes the actual date and assigns the given number to the date. So it's converting expdate (text) into a number.
Datevalue takes the actual date and assigns the given number to the date. So it's converting expdate (text) into a number.
ASKER
expdate is a datetime field - maybe that's the problem - applying datevalue to a datetime field?
Int seems to extra only the integer (date) part of datetime - which is what I want.
I guess that solves the problem?
Int seems to extra only the integer (date) part of datetime - which is what I want.
I guess that solves the problem?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great insights - many thanks.
strsql = strsql & " AND (" & "expdate between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"