Solved

Date between in Access Query

Posted on 2014-02-20
11
651 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 119

Accepted Solution

by:
Rey Obrero 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now