Listing Data between two Dates

[Andy1723]

I need to print out data from a access database that is between two dates.

For example, If i have entries in my database at
2/5/06 Swimming
3/5/06 Dancing
7/6/06 Fighting
2/7/08 Skipping

and I have a DTPpicker which defines my start date at "3/5/06" and another DTPpicker that defines my end date at "1/7/06" I want a picture or list box to print out

3/5/06 Dancing
7/6/06 Fighting

Thank you!
x

[Andy1723]

Hmm maybe if i used DatePart("d", dtpStartDate)

startDate = DatePart("d", dtpStartDate)
endDate = DatePart("d", dtpEndDate)

Do until startDate = endDate

then add 1 onto the startdate until it equals the number of days of the end date?

[GaryMazzone]

Some thing like this

"Select fieldname,fieldname from tablename where fieldname Between #" & datepicker start date & "# And #" & datepicker end date & "#"

I did assume you are using Access as a backend that is why the date values are inside pound signs

[Andy1723]

Some thing like this

"Select fieldname,fieldname from tablename where fieldname Between #" & datepicker start date & "# And #" & datepicker end date & "#"

I did assume you are using Access as a backend that is why the date values are inside pound signs

Access is not doing any of the work, just retrieving data from my visual basic programming. When I try using that I get an error saying "Expected Case".

[Andy1723]

startDate = DatePart("d", dtpStartDate)
endDate = DatePart("d", dtpEndDate)
 
    Do Until startDate = endDate
        While Not found And Not tblBooking.EOF
            If tblBooking!Date = dtpStartDate Then
                found = True
            Else
                tblBooking.MoveNext
            End If
        Wend
    Loop

I got this so far, but my program keeps crashing, any ideas why??

Thanks you for everyone who's helping, i'm really stuck

[GaryMazzone]

Is access storing the data? How are you retrieving data from the database? The code I showed is the way you would request a recordset from the database. Are you using ADO or DAO data access methods? How did you open the results to VB?

[Andy1723]

Is access storing the data? How are you retrieving data from the database? The code I showed is the way you would request a recordset from the database. Are you using ADO or DAO data access methods? How did you open the results to VB?

Access stores the data!

Option Explicit
Public dbfVillageHall As Database
Public tblCustDetails, tblPrices, tblBooking As Recordset
 
Sub Main()
    Set dbfVillageHall = OpenDatabase(App.Path & "\villageHall.mdb")
    Set tblCustDetails = dbfVillageHall.OpenRecordset("customerDetails")
    Set tblBooking = dbfVillageHall.OpenRecordset("booking")
    Set tblPrices = dbfVillageHall.OpenRecordset("prices")

Thats how i'm retrieving it.
ADO DAO?? Sorry I don't understand what they are.

I want to print the results in a Picture box or any other tool suitable for listing the data!
thank you!

[GaryMazzone]

ADO and DAO are methods of accessing data stored in a database. DAO is/was used pretty much exclusivly for MSAccess and has been de-supported by MS. ADO is the preferred way of accessing data using VB6. To see which you have set you check the references in VB. I think you are using DAO and are just opening up complete tables.

[Andy1723]

ADO and DAO are methods of accessing data stored in a database. DAO is/was used pretty much exclusivly for MSAccess and has been de-supported by MS. ADO is the preferred way of accessing data using VB6. To see which you have set you check the references in VB. I think you are using DAO and are just opening up complete tables.

How do i find out??

[GaryMazzone]

Check the reference me menu item. Even if using DAO you can use SQL statements to limit the data coming back from the database to just what you want and not all the extra stuff

[Andy1723]

Check the reference me menu item. Even if using DAO you can use SQL statements to limit the data coming back from the database to just what you want and not all the extra stuff

I'd prefer to do it the way I posted above if it is do-able like that??

I know its probably not the easiest way but my knowledge is really limited so it would be really hard for me to alter the results it obtains!

Thank you so much though!

[Andy1723]

Hmmm, I decided to try using a counter, still doesn't work, but i think its a better idea.


heres my code:
For counter = startDate To endDate
While Not found And Not tblBooking.EOF
If tblBooking!Date = dtpStartDate Then
found = True
Print "wow" 'debug
Else
tblBooking.MoveNext
End If
Wend
Next counter

hmm

[GaryMazzone]

This is probably the harder way to do this.

While not Found and not tblBooking.EOF 
   If tblBooking!Date >= startate and If tblBooking!Date <=EndDate then
      Do what you want here
   end if
   tblBooking.MoveNext
Wend

[Andy1723]

This is probably the harder way to do this.

While not Found and not tblBooking.EOF 
   If tblBooking!Date >= startate and If tblBooking!Date <=EndDate then
      Do what you want here
   end if
   tblBooking.MoveNext
Wend

That can't work because startDate and endDate is just the number for the day, not the full date!

[Andy1723]

Because the interval should be a week between the dates i want to retrieve, I added this to your code

startDate = DatePart("d", dtpStartDate)
endDate = startDate + 7
monthNumber = DatePart("mm", dtpStartDate)
yearNumber = DatePart("yyyy", dtpStartDate)
 
fullSDate = startDate + "/" + monthNumber + "/" + yearNumber
fullEDate = endDate + "/" + monthNumber + "/" + yearNumber
 
full
 
    While Not found And Not tblBooking.EOF
       If tblBooking!Date >= fullSDate And tblBooking!Date <= fullEDate Then
            Print "wow"
       End If
            tblBooking.MoveNext
    Wend

Hopefully that could work?

[Al42]

Use "&", not "+", to concatenate strings, but fullSDate = dtpStartDate, so you don't have to calculate it, and fullEDate is just DateAdd("d", 7, dtpStartDate).

However, tblBooking!Date would have to be the full date, not just "the number for the day", for that code (either way, yours or mine) to work.

What Gary said, though, saves you a lot of work, because you'd only have records that fell between those dates, so you wouldn't have to test anything.