Solved

Date between in Access Query

Posted on 2014-02-20
11
652 Views
Last Modified: 2014-02-20
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
Comment
Question by:terpsichore
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39874071
Do you need datevalue()? how about

 strsql = strsql & " AND (" & "expdate between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto & "#)"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39874075
try this

  strsql = strsql & " AND " & [expdate] between #" & Me.txtdatefrom & "# AND #" & Me.txtdateto + 1 & "#"
0
 

Author Comment

by:terpsichore
ID: 39874088
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Expert Comment

by:SarahDaisy8
ID: 39874089
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
 
LVL 15

Expert Comment

by:JimFive
ID: 39874106
What is the actual value of strsql after you have built the expression?
0
 

Author Comment

by:terpsichore
ID: 39874109
I changed datevalue to int and it seems to work fine... any idea why that would be?
0
 
LVL 1

Expert Comment

by:SarahDaisy8
ID: 39874190
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
 

Author Comment

by:terpsichore
ID: 39874219
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
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 250 total points
ID: 39874222
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39874251
@terpsichore

did you try the query i posted at http:#a39874075 ?
0
 

Author Closing Comment

by:terpsichore
ID: 39874259
great insights - many thanks.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question