How to filter from two dates to generate a data report in VB6


hi experts

could any one help me on this.

I want to generate a data report filtered by two dates,
I currently use the following method to filter with one date.

Private Sub Command1_Click()
Dim sDate As String
 sDate = Format(CDate(Text1), "mm-dd-yyyy")
   MsgBox sDate
   DataEnvironment1.Connection1.Open
   DataEnvironment1.Command1 sDate
   'DataReport1.Orientation = rptOrientLandscape
  DataReport1.Orientation = rptOrientLandscape
DataReport1.Show
       DataReport1.PrintReport True, rptRangeAllPages
End Sub

Im using this SQL statement in DataEnvironments command1 properties.
SELECT flt, consignee, donumber, awbnumber, pkg, weight, examofficer, recname, remarks, `user`, serial, type, dodate FROM courier WHERE (dodate = ?)

Parameter Properties

Name: Param1
Direction :Input
Datatype : adDate

host data type :Date(VT_DATE)

This method works to filter by one date.
I couldnt figure out a way to filter it by two dates!!!!

THANX

jiyadAsked:
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.

Éric MoreauSenior .Net ConsultantCommented:
0
Programmer_JeffCommented:
A possiblility (not a guarantee) is add another variable.  After your dodate variable:
dodate, dodate2 FROM courier WHERE (dodate = ? OR dodate2 = ?)

Good luck.
0
georg74Commented:
I assume you want to get records with sDate <= dodate <= sDate2
you have to change following:

add Text2 field which will contain the second date

add these lines to the code after similar lines with sDate:

-    sDate2 = Format(CDate(Text2), "mm-dd-yyyy")

-    DataEnvironment1.Command2 sDate2

change the MsgBox statement to MsgBox "From " & sDate & " to " & sDate2

change the SQL statement to following:

SELECT flt, consignee, donumber, awbnumber, pkg, weight, examofficer, recname, remarks, `user`, serial, type, dodate FROM courier WHERE dodate >= ? AND dodate <= ?

HTH,
g.
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.

georg74Commented:
... + you need a second Parameter of the same type as the first one
0
jiyadAuthor Commented:

thanx for the help georg
thanx for the help emoreau

im checking on it!!!!
0
jiyadAuthor Commented:
emoreau:

Ive checked the following link..


http://support.microsoft.com/default.aspx?scid=kb;en-us;244779

could you pls show me a way to filter between two dates using this method in the article( Parameterized DataReport)

thanks.
0
Éric MoreauSenior .Net ConsultantCommented:
CommandText: Select * From Employees Where HireDate Between ? AND ?

Create a second parameters (pDate2)

.Command1 CDate(Text1.Text, Text2.text)
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
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
DB Reporting Tools

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.