?
Solved

Find Records between dates

Posted on 2003-03-17
12
Medium Priority
?
220 Views
Last Modified: 2010-05-01
Hi:

Using vb5 and access

I have two variables that a user inputs  txtstartdate, txtenddate -- in the database there is a field called recorddate

Trying to find all records between the dates -- i can pull up the records out of the database fine -- its getting the records between the dates that is the problem.  i was using datepart and comparing days, months, and year however i was running into snytax errors (not even sure if this is the way to approach it)

below is relevant code

Set MacAgentRecordset = MacAgent.OpenRecordset("select * from agent where advertiserid=" _
                        & Mid$(ImageCombo1.SelectedItem.key, 2)

                         this is where it screws up

                        & "and" & datepart("yyyy", recorddate) = datepart("yyyy", txtstartdate) etc etc

long story short -- cant figure out how to relate the yyyy part in the record in the database to the txtstartdate -- if i put it all in the " it errors out because of the ", outside of it, it cant find the value  





   

0
Comment
Question by:tpiazza
[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
  • 6
  • 4
  • 2
12 Comments
 
LVL 1

Expert Comment

by:JH0401
ID: 8153607
& " and " & format(format(datepart,"YYYY/MM/DD"),"#") >= format(format(txtstartdate,"YYYY/MM/DD"),"#") & " and " & format(format(datepart,"YYYY/MM/DD"),"#") <= format(format(txtenddate,"YYYY/MM/DD"),"#")
0
 
LVL 1

Expert Comment

by:JH0401
ID: 8153613
replace the portion that "screws up" with the above code
0
 
LVL 1

Expert Comment

by:JH0401
ID: 8153624
sorry -- use this

& " and " & format(format(recorddate,"YYYY/MM/DD"),"#") >= format(format(txtstartdate,"YYYY/MM/DD"),"#") & " and " & format(format(recorddate,"YYYY/MM/DD"),"#") <= format(format(txtenddate,"YYYY/MM/DD"),"#")
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:JH0401
ID: 8153632
use the latter example -- I accidentally used datepart instead of your recorddate field in the first example!!!
0
 

Author Comment

by:tpiazza
ID: 8153895
get a wierd error -- says that the the database cant find the table or query true when i insert the code
0
 
LVL 9

Expert Comment

by:dancebert
ID: 8154028
Am I missing something?  Why not use the BETWEEN operator?  e.g. WHERE myDate BETWEEN #1/1/2001# AND #1/31/2001#
0
 
LVL 1

Expert Comment

by:JH0401
ID: 8154046
sorry about that!!! what format is recorddate field in???
0
 
LVL 1

Expert Comment

by:JH0401
ID: 8154417
have you tried dancebert's suggestion???
0
 

Author Comment

by:tpiazza
ID: 8154466
Set MacAgentRecordset = MacAgent.OpenRecordset("select * from workorder where advertiserid=" & Mid$(ImageCombo1.SelectedItem.key, 2) _
    & "and date1 BETWEEN " & txtStartDate & " and " & txtenddate)
   

it never returns a result

the field is just a date/time field  -- goes across as m/d/yyy

0
 

Author Comment

by:tpiazza
ID: 8154485
im assuming between means between and equal to
0
 
LVL 9

Accepted Solution

by:
dancebert earned 1000 total points
ID: 8154532
Your resulting SQL is:
select * from workorder where advertiserid= <myNumber> and date1 BETWEEN <myMM/yy> and <myMM/YY>

(where, obviously, the stuff between <> are your real values)

What it needs to be is:
select * from workorder where advertiserid= <myNumber> and date1 BETWEEN #<myMM/yy># and #<myMM/YY>#

Without the pound signs, Access doesn't know it's a date literal.  
0
 

Author Comment

by:tpiazza
ID: 8154567
works like a champ
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

770 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