Solved

Date between in Access Query

Posted on 2014-02-20
11
649 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
Comment Utility
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
Comment Utility
try this

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

Author Comment

by:terpsichore
Comment Utility
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
Comment Utility
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
Comment Utility
What is the actual value of strsql after you have built the expression?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

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

Expert Comment

by:SarahDaisy8
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@terpsichore

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

Author Closing Comment

by:terpsichore
Comment Utility
great insights - many thanks.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

11 Experts available now in Live!

Get 1:1 Help Now