Solved

Date between in Access Query

Posted on 2014-02-20
11
658 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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