Link to home
Start Free TrialLog in
Avatar of terpsichore
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 -
Avatar of mankowitz
mankowitz
Flag of United States of America image

Do you need datevalue()? how about

 strsql = strsql & " AND (" & "expdate between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of terpsichore
terpsichore

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
What is the actual value of strsql after you have built the expression?
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.
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@terpsichore

did you try the query i posted at http:#a39874075 ?
great insights - many thanks.