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
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
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
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
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
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
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.
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!
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!
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.