dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 21

Thread: selecting records in msacces between 2 dates

Hybrid View

  1. #1
    Join Date
    May 2005
    Posts
    126

    selecting records in msacces between 2 dates

    hello,

    i need to select records between 2 dates from a table called tblVaccinatie with columns :
    - VaccinatieId
    - VacDatum
    - VacProdukt
    - PatId

    i am using this code now, but it isn't working

    strVaccinatie = "Select * from tblVaccinatie where VacDatum between #" & _
    dteVan & "#" & " and " & "#" & dteTot & "#"

    dteVan and dteTot are the values of two datepicker controls

    if u need more info to solve my problem, please tell me what is lacking

    thnx, Koen

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,268

    Re: selecting records in msacces between 2 dates

    Your SQL string (strVacinitie) looks O K!
    How do you execute your query
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

  3. #3
    Join Date
    May 2005
    Posts
    126

    Cool Re: selecting records in msacces between 2 dates

    hello

    this is a part of a veterinary software i try to design myself

    the reason i distract one year of the startdate and enddate is that when i use the programme, i want to know wich patients need to be revaccinated in lets say this month

    i can look that up by looking who was vaccinated one year ago, then i look for information of the patient and then i look for info of the owner of the animal
    then all the information is put in a listview (report)


    some more info :
    connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=C:\mijn documenten\VetBase\dbVetBase.mdb"

    this string is put in the load area of the main page, so i can use it everywhere in my programme without the constant need of retyping the whole sentence


    here is the complete code of the form

    if u need more info, let me now

    |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    Option Explicit

    Private Sub Command1_Click()

    Dim dteVan As Date
    Dim dteTot As Date
    Dim strVaccinatie As String
    Dim strPatient As String
    Dim strKlant As String
    Dim liOverzicht As ListItem
    Dim strOverzicht() As String

    ' i use this string array to store later on all information to put in the listview

    Dim intPatientId() As Integer

    ' i use this integer array to store the patient id's who are selected (in order
    ' to look up the name of the patient, species, i.e. dog or cat, ...)

    Dim intClientId() As Integer

    ' i use this array to store the client id's who are selected (in order to look up
    ' information like adress, city, name and lastname owner ....)

    Dim intA As Integer
    Dim intP As Integer
    Dim intK As Integer
    Dim intTeller As Integer
    Dim intO As Integer
    '-----------------------------------------------------------------------------
    dteVan = DateAdd("yyyy", -1, dpVan)
    dteTot = DateAdd("yyyy", -1, dpTot)
    intP = 0
    '---------------------------------------------------------------------------
    lvOverzicht.ListItems.Clear 'this is obvious
    '---------------------------------------------------------------------------
    strVaccinatie = "Select * from tblVaccinatie where VacDatum between #" & _
    dteVan & "#" & " and " & "#" & dteTot & "#"

    Set adoCn = New ADODB.Connection
    adoCn.Open connectstring

    Set adoRs = New ADODB.Recordset
    adoRs.Open strVaccinatie, adoCn, adLockReadOnly

    If adoRs.RecordCount = 0 Then
    MsgBox "Geen records gevonden voor opgegeven periode."

    'it says no records fount between the two date

    Exit Sub
    End If


    'now we know how many records there are so i redefine the arrays, then
    ' i store the id of the patients in the array intPatientId, and the name of
    ' the 'vaccin used in the array strOverzicht

    ReDim intPatientId(adoRs.RecordCount - 1) As Integer
    ReDim strOverzicht(adoRs.RecordCount - 1, 8) As String
    intTeller = adoRs.RecordCount - 1

    Do Until adoRs.EOF
    intPatientId(intP) = adoRs!PatId
    strOverzicht(intP, 1) = adoRs!Vaccin
    intP = intP + 1
    adoRs.MoveNext
    Loop
    adoRs.Close
    adoCn.Close

    ReDim intClientId(intTeller)
    '---------------------------------------------------------------------------
    ' now i open the table with the patients information to get name of the
    ' patient (stored in array strOverzicht) and the owners id (= KlantId) that i
    ' store in the array inClientId

    For intA = 0 To intTeller

    strPatient = "Select * from tblPatient where PatId = " & _
    intPatientId(intA)
    Set adoCn = New ADODB.Connection
    adoCn.Open connectstring
    Set adoRs = New ADODB.Recordset
    adoRs.Open strPatient, adoCn, adLockReadOnly

    intClientId(intA) = adoRs!KlantId
    strOverzicht(intA, 2) = adoRs!PatNaam & ""

    adoRs.Close
    adoCn.Close

    Next intA
    '----------------------------------------------------------------------------
    'now i open the table with information on the owners to get adress, name
    ' and lastname of the owner

    For intK = 0 To intTeller

    strKlant = "Select * from tblKlant where KlantId = " & _
    intClientId(intK)
    Set adoCn = New ADODB.Connection
    adoCn.Open connectstring
    Set adoRs = New ADODB.Recordset
    adoRs.Open strKlant, adoCn, adLockReadOnly

    strOverzicht(intK, 3) = adoRs!KlantNaam & ""
    strOverzicht(intK, 4) = adoRs!KlantVoorNaam & ""
    strOverzicht(intK, 5) = adoRs!KlantStraatnaam & ""
    strOverzicht(intK, 6) = adoRs!KlantStraatnummer & ""
    strOverzicht(intK, 7) = adoRs!KlantPostcode & ""
    strOverzicht(intK, 8) = adoRs!KlantGemeente & ""

    adoRs.Close
    adoCn.Close

    Next intK
    '---------------------------------------------------------------------------
    For intO = 0 To intTeller
    Set liOverzicht = lvOverzicht.ListItems.Add(, , strOverzicht(intO, 3))
    liOverzicht.SubItems(1) = strOverzicht(intO, 4)
    liOverzicht.SubItems(2) = strOverzicht(intO, 5)
    liOverzicht.SubItems(3) = strOverzicht(intO, 6)
    liOverzicht.SubItems(4) = strOverzicht(intO, 7)
    liOverzicht.SubItems(5) = strOverzicht(intO, 8)
    liOverzicht.SubItems(6) = strOverzicht(intO, 2)
    liOverzicht.SubItems(7) = strOverzicht(intO, 1)
    Next intO
    '----------------------------------------------------------------------------
    End Sub

    Private Sub Form_Load()
    Dim column_header As ColumnHeader

    dpVan = Date
    dpTot = DateAdd("m", 1, Date)

    lvOverzicht.Top = 3000
    lvOverzicht.Left = 100
    lvOverzicht.Width = frmOverzicht.ScaleWidth
    lvOverzicht.Height = frmOverzicht.ScaleHeight - 3000

    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Naam Klant", TextWidth("NAAM KLANT"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Voornaam Klant", TextWidth("VOORNAAM KLANT"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Straatnaam", TextWidth("STRAATNAAM VAN DE KLANT"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Straatnummer", TextWidth("STRAATNUMMER"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Postcode", TextWidth("XXXX"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Gemeente", TextWidth("GEMEENTE"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Naam Patient", TextWidth("NAAM PATIENT"))
    Set column_header = lvOverzicht.ColumnHeaders.Add(, , "Vaccin", TextWidth("GEBRUIKT VACCIN"))

    End Sub
    Last edited by koenbuytaert; September 26th, 2005 at 02:16 PM.

  4. #4
    Join Date
    Sep 2005
    Location
    India
    Posts
    58

    Re: selecting records in msacces between 2 dates

    Hai,
    Can i know what is your problem, whether you are getting any error messages (if so please mention the error) or your not getting the expected results.
    Please make a habbit of rating posts that help you,
    Everything is not possible but
    nothing is impossible

    With Regards,
    S.Murugan

  5. #5
    Join Date
    May 2005
    Posts
    126

    Re: selecting records in msacces between 2 dates

    hey,
    i dont get any errors, it is just that i dont get the right results,

    i almost always get all the records in the listview,

    lets say i select from 01/02/2005 to 30/02/2005 i know there are only a few records in this month, but i get in my listview also records from other months, so it not working well

  6. #6
    Join Date
    Sep 2005
    Location
    India
    Posts
    58

    Re: selecting records in msacces between 2 dates

    Hai,
    You have used a complex code to do a simple task, and moreover you have used so many arrays that are needless, sorry to tell you it's not the right way, try to get the records you want from a single query using the natural join query, and then add it to the listview (feel free to write if you have any doubts)
    Last edited by smurug; September 26th, 2005 at 02:54 PM.
    Please make a habbit of rating posts that help you,
    Everything is not possible but
    nothing is impossible

    With Regards,
    S.Murugan

  7. #7
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,268

    Re: selecting records in msacces between 2 dates

    Code:
            MousePointer = vbHourglass
    'your code here
    Set it back to default once finished
    Code:
    'Change the mousepointer back to the arrow
            MousePointer = vbDefault
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

  8. #8
    Join Date
    Sep 2005
    Location
    Delhi, INDIA
    Posts
    237

    Re: selecting records in msacces between 2 dates

    Dear KoenBuytaert,

    Chnage the above syntax to

    Code:
     screen.mousepointer = vbhourglass
    and then set back to

    Code:
     screen.mousepointer = vbnormal
    to get clear results, as just mousepointer will work for only active control not for the whole sceen.

    regards !
    I'M BACK AGAIN !!
    -------------------------------------------------------------------------
    enjoy the VB !
    If any post helps you, please rate that.
    Always try to findout the Solutions, instead just discussing the problem and its scope!

  9. #9
    Join Date
    May 2005
    Posts
    126

    Re: selecting records in msacces between 2 dates

    ok the sand glass is working now also

    thnx and till the next problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)