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

Thread: selecting records in msacces between 2 dates

  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
    May 2005
    Posts
    126

    Re: selecting records in msacces between 2 dates

    how does a join query looks like? i tried it, but it didn't work because i need information of 3 tables at one time?

    thnx, koen

  8. #8
    Join Date
    Mar 2005
    Posts
    22

    Re: selecting records in msacces between 2 dates

    I think you need format the dates in two DatePicker controls are "mm/dd/yyyy".

    Try again!

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

    Re: selecting records in msacces between 2 dates

    Quote Originally Posted by thuongall
    I think you need format the dates in two DatePicker controls are "mm/dd/yyyy".

    Try again!
    I agree,
    Also have a look at your computer's date and time settings and make sure your dtPicker and computer's formats are the same. Run the query again.
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

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

    Re: selecting records in msacces between 2 dates

    Hi koenbuytaert,

    would you please attach you whole code as an attachement to get better help please !

    I hope that you will get faster solution then !

    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!

  11. #11
    Join Date
    May 2005
    Posts
    126

    Re: selecting records in msacces between 2 dates

    1. i searched how to configure the dates, but there are a lot of solution, wich is the best one?

    2. i attached the form, but i don't know if that's enough, i you need more files, please tell me wich ones

    3. is there a more efficient way to solve my problem? just give me some guidelines...

    thnx, Koen
    Attached Files Attached Files

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

    Re: selecting records in msacces between 2 dates

    Quote Originally Posted by koenbuytaert
    1. i searched how to configure the dates, but there are a lot of solution, wich is the best one?
    You could use your Format function
    Code:
    dteTot = Format(dteTot,"dd/mm/yyyy")
    Then have a look at your Date and Time Settings
    Start, Settings, Control Panel, Regional Settings, and set your Short Date Style to the same.

    Here is something to change the regional settings from your VB program
    Code:
    Call Shell("rundll32.exe shell32.dll,Control_RunDLL intl.cpl")
    PS. Are you from the Netherlands
    Last edited by HanneSThEGreaT; September 27th, 2005 at 03:23 AM.
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

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

    Re: selecting records in msacces between 2 dates

    I also agree,

    Use the format function in between your query.

    strVaccinatie = "Select * from tblVaccinatie where VacDatum between #" & _
    Format(dteVan,"dd-MMM-yyyy") & "#" & " and " & "#" & Format(dteTot,"dd-MMM-yyyy") & "#"

    and also check your database table, how it is storing your data in table?

    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!

  14. #14
    Join Date
    May 2005
    Posts
    126

    Re: selecting records in msacces between 2 dates

    ok thanks, i will try this and let you know the result

    @hannes : i am from belgium, you?

    i already tried it and i get this error :

    syntax error in date expression 'Vacdatum between #27-aug-2005# and #27-okt-2005#'.

    is this because the code you suggested uses dd-MMM-yyyy
    MMM is this the problem? i will change it now..
    Last edited by koenbuytaert; September 27th, 2005 at 03:24 AM.

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

    Re: selecting records in msacces between 2 dates

    Quote Originally Posted by koenbuytaert
    ok thanks, i will try this and let you know the result

    @hannes : i am from belgium, you?
    Good!
    South Africa, the reason I asked, was that most of the terms in your code is very much similar to Afrikaans
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

Page 1 of 2 12 LastLast

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)