[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

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 -
0
terpsichore
Asked:
terpsichore
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
mankowitzCommented:
Do you need datevalue()? how about

 strsql = strsql & " AND (" & "expdate between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"
0
 
Rey Obrero (Capricorn1)Commented:
try this

  strsql = strsql & " AND " & [expdate] between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto + 1 & "#"
0
 
terpsichoreAuthor Commented:
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?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
SarahDaisy8Commented:
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
0
 
JimFiveCommented:
What is the actual value of strsql after you have built the expression?
0
 
terpsichoreAuthor Commented:
I changed datevalue to int and it seems to work fine... any idea why that would be?
0
 
SarahDaisy8Commented:
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.
0
 
terpsichoreAuthor Commented:
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?
0
 
mankowitzCommented:
The reason why int works is that access stores dates as a floating point number as the number of days after a particular epoch (I think it's 1/1/1904). So, for example, This morning at 12:00 was 41690. Noon today was 41690.5, and so on.

When you reduce the day to an int, you ignore the time-of-day portion, which is what you were trying to do.
0
 
Rey Obrero (Capricorn1)Commented:
@terpsichore

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now