Loop thru recordset using begin and end dates

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Loop thru recordset using begin and end dates

Post by burrina »

how can I add this to my loop code?

Code: Select all

Between Dte1 And Dte2 ' Variable set when form loads.

Dte1 = Forms!frmMainMenu!BeginDate
Dte2 = Forms!frmMainMenu!EndDate

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop thru recordset using begin and end dates

Post by HansV »

What recordset?
Which field do you want to loop?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Loop thru recordset using begin and end dates

Post by burrina »

Dim rst As DAO.Recordset

Set rst = MyDB.OpenRecordset("TCheckNo", dbOpenDynaset)

If Not ![ChkPD] Then 'If Employee has not been paid
.Edit
'Fields to loop would be Dte1 and Dte2 since they are called via the module. I need between those dates.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop thru recordset using begin and end dates

Post by HansV »

Dte1 and Dte2 are specific dates, like 23-Feb-2021.
What do you want to loop between Dte1 and Dte2?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Loop thru recordset using begin and end dates

Post by burrina »

I woulld use the BeginDate for my starting date and EndDate for the end date. These would be set via the calendar.
Or between Dte1 and Dte2 would be the same.
Example: Dte1 = 2/23/21 and Dte2 = 2/24/21 Or Between Forms!frmMainMenu!BeginDate and Forms!frmMainMenu!EndDate

Hope this explains it.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop thru recordset using begin and end dates

Post by HansV »

No, it doesn't. You still haven't told me which field in the recordset should be between the start and end dates.
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Loop thru recordset using begin and end dates

Post by burrina »

Sorry! CheckDate is the field

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop thru recordset using begin and end dates

Post by HansV »

It could look like this (air code warning!):

Code: Select all

    Dim rst As DAO.Recordset
    Set rst = MyDB.OpenRecordset("SELECT * FROM TCheckNO WHERE CheckDate Between #" & _
        Format(Dte1, "yyyy-mm-dd") & "# And #" & Format(Dte2, "yyyy-mm-dd") & "#", dbOpenDynaset)
    With rst
        Do While Not .EOF
            If Not ![ChkPD] Then 'If Employee has not been paid
                .Edit
                ...
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Loop thru recordset using begin and end dates

Post by burrina »

Thank You Hans. The set statement was throwing me off. I appreciate it!