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
Re: selecting records in msacces between 2 dates
Your SQL string (strVacinitie) looks O K!
How do you execute your query ¿
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
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.
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
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)
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
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!
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.
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 !
1 Attachment(s)
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
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 ¿
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 !
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..
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 :)