Problem with Finding Records on Dates


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Problem with Finding Records on Dates

  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Problem with Finding Records on Dates

    Hi


    I am a new user in VISUAL BASIC.

    I have a form which I placed 2 DTPicker for dates

    The structure of my table is

    Field Type

    startdate ( Date )
    amount ( Number )


    The name of DTPicker component is as a sdate and edate

    Inside the form I used this code


    dummy.Source = "select * from payments where startdate >= #" & sdate & "# and startdate <= #" & edate & "# order by startdate"
    dummy.Open



    If dummy.BOF = True And dummy.EOF = True Then
    MsgBox "Record Not Found"
    Exit Sub
    Else
    List1.AddItem "--------------------------------------------"
    List1.AddItem sdate & " to " & edate
    List1.AddItem "--------------------------------------------"

    While Not dummy.EOF
    Me.List1.AddItem dummy.Fields("startdate")
    dummy.MoveNext
    Wend
    End If


    If I select the dates from dtpickers like sdate="02/01/2009" and edate="04/01/2009"
    its give me the messge record not found instead of in the table there is a record.

    If I select the dates from dtpickers like sdate="04/01/2009" and edate="04/01/2009"
    its give me the again same messge record not found although there is a record in the table.Its should find the record but its give me the nothing

    Please help me in this matter.

    Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,262
    If your database only has the date in it, no time part, then you may need to
    strip off the default time part of each date. But if your database does have
    date and time parts you may need to strip off the default times and hard
    code the min and max time parts.
    Code:
    'just remove the time part
    dummy.Source = "select * from payments where startdate >= #" & Fix(sdate) & "# and startdate <= #" & Fix(edate) & "# order by startdate"
    
    'remove and then hard code the time parts
    dummy.Source = "select * from payments where startdate >= #" & Fix(sdate) & " 00:00:00# and startdate <= #" & Fix(edate) & " 23:59:59# order by startdate"
    FYI: Dates are stored as numbers where the whole part is the date and the
    decimal part it the time, so using the Fix() function truncates the decimal
    part, the time, making it just a date.

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Thanks for the reply. I tried to both queries with fix function but the result is same nothing to change.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What is "dummy"?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Finding missing records
    By DesiMcK in forum Database
    Replies: 2
    Last Post: 04-22-2008, 04:03 AM
  2. CMR problem
    By vikassheelgupta in forum Java
    Replies: 0
    Last Post: 07-22-2005, 05:35 AM
  3. SQL as recordsource problem - desp. need help
    By VB6noobz in forum Database
    Replies: 3
    Last Post: 02-20-2005, 01:46 PM
  4. Finding records in VB.
    By Mustafa in forum VB Classic
    Replies: 1
    Last Post: 11-30-2001, 07:56 AM
  5. insert 20000 records time problem
    By v.des in forum Database
    Replies: 6
    Last Post: 06-06-2000, 11:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center