selecting records in msacces between 2 dates

[koenbuytaert]

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

[HanneSThEGreaT]

Your SQL string (strVacinitie) looks O K!
How do you execute your query ¿

[koenbuytaert]

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

[smurug]

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.

[koenbuytaert]

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

[smurug]

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)

[koenbuytaert]

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

[thuongall]

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

Try again!

[HanneSThEGreaT]

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.

[rahul.kul]

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 !

[koenbuytaert]

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

[HanneSThEGreaT]

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

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

Call Shell("rundll32.exe shell32.dll,Control_RunDLL intl.cpl")

PS. Are you from the Netherlands ¿

[rahul.kul]

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 !

[koenbuytaert]

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..

[HanneSThEGreaT]

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