Find Records between dates

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  





   

tpiazzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JH0401Commented:
& " 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
JH0401Commented:
replace the portion that "screws up" with the above code
0
JH0401Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JH0401Commented:
use the latter example -- I accidentally used datepart instead of your recorddate field in the first example!!!
0
tpiazzaAuthor Commented:
get a wierd error -- says that the the database cant find the table or query true when i insert the code
0
dancebertCommented:
Am I missing something?  Why not use the BETWEEN operator?  e.g. WHERE myDate BETWEEN #1/1/2001# AND #1/31/2001#
0
JH0401Commented:
sorry about that!!! what format is recorddate field in???
0
JH0401Commented:
have you tried dancebert's suggestion???
0
tpiazzaAuthor Commented:
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
tpiazzaAuthor Commented:
im assuming between means between and equal to
0
dancebertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tpiazzaAuthor Commented:
works like a champ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.