Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find Records between dates

Posted on 2003-03-17
12
Medium Priority
?
221 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

577 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