Solved

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

Posted on 2004-10-27
1,965 Views
Last Modified: 2010-04-29

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

0
Question by:jiyad
    7 Comments
     
    LVL 69

    Expert Comment

    by:Éric Moreau
    0
     
    LVL 1

    Expert Comment

    by:Programmer_Jeff
    A possiblility (not a guarantee) is add another variable.  After your dodate variable:
    dodate, dodate2 FROM courier WHERE (dodate = ? OR dodate2 = ?)

    Good luck.
    0
     
    LVL 3

    Expert Comment

    by:georg74
    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
     
    LVL 3

    Expert Comment

    by:georg74
    ... + you need a second Parameter of the same type as the first one
    0
     

    Author Comment

    by:jiyad

    thanx for the help georg
    thanx for the help emoreau

    im checking on it!!!!
    0
     

    Author Comment

    by:jiyad
    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
     
    LVL 69

    Accepted Solution

    by:
    CommandText: Select * From Employees Where HireDate Between ? AND ?

    Create a second parameters (pDate2)

    .Command1 CDate(Text1.Text, Text2.text)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Suggested Solutions

    Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
    A short article about problems I had with the new location API and permissions in Marshmallow
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now