Recordset Returning Selected Day Of Week

[DMT Dave]

Hi Guy's, how can i get a straight forward recordset to return a specific day of the week



StartDate is Input Box
End Date is InputBox

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "#")

So if i select 1/1/22 as start date

enddate as 28/2/22

how can i get my recordset to return where delivery day is Monday then do similar output for all deliveries on Tuesday, Wed,Thur, fri

Perhaps if had a combo with those days in it, can I get set return records based on day taken from delivery date

Hope I haven't confused this ?

[Gicu]

Are you trying to get delivery days one day at the time or all Mondays in the selected interval?
If one at the time you would either need to filter your original recordset or just create a loop in which to set up the recordset to only include one day at the time, do your stuff then increment the date variable by 1...

Dim datSelectedDate as Date, rs as DAO.Recordset


datSelectedDate = StartDate 


Do Until datSelectedDate = EndDate +1
       Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate = #" & datSelectedDate & "#")
      'do your stuff here
       
datSelectedDate=datSelectedDate+1
Loop

If you want to lump all Mondays, then all Tuesdays then you can try to filter the recordset using the Weekday() function.

Cheers,

[DMT Dave]

Hi Vlad, i am reading your reply and looking at your suggestion is making sense for me to try, i should been more precise of the outcome

So I have an excel file as below, i could do with everything that has a delivery date that was delivered on a MONDAY between 2 selected dates outputting to Monday List (Sheet1)

everything that has a delivery date that was delivered on a TUESDAY between 2 selected dates outputting to TUESDAY LIST (Sheet3) etc etc

I can then pin the map on each Map Day

Your reply is greatly appreciated

Kindest

[Gicu]

Something like this then:

Dim iWeekday as Integer, rs as DAO.Recordset




For iWeekday=1 to 7 
      
	Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "# AND Weekday([DeliveryDate]) = " & iWeekday)
      'do your stuff here
       
iWeekday=iWeekday+1
Loop

[DMT Dave]

Hi Vlad, so i haven't had the chance to do this but I have 1 question, whilst i can see what you are suggesting

within that loop, how do I determine which sheet name so day number 2 (I believe is Monday) as doesn't system day class Sunday as day 1 ?

So iWeekDay 2 outputs to Monday (Sheet1)
next iWeekday goes to Tuesday (Sheet3)
next iWeekDay goes to Wednesday (Sheet5)
etc

I will start with you suggestions as I can see what you have done.

Kindest

[Welshgasman]

You can specify first day of the week.
https://docs.microsoft.com/en-us/off...ekday-function

[Gicu]

Dim iWeekday as Integer,iSheet as Integer, rs as DAO.RecordsetDim xlw as Excel.Workbook,xls as Excel.Sheet




iSheet =1
For iWeekday=1 to 7 
      
	Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "# AND Weekday([DeliveryDate]) = " & iWeekday)
      'do your stuff here
'open Excel and workbook.......
Set xls=xlw.Sheets(iSheet)
xls.Range ("A2").CopyFromRecordset rs


iWeekday=iWeekday+1
iSheet=iSheet+2
Loop

[DMT Dave]

Ahh looping the worksheets as well as the weekday ?

[Gicu]

Yes Dave, that would be the easiest I think.

[DMT Dave]

Hi Vlad, thank you, i am getting an error on my adjustments, i am unsure if i need a NEXT because i have got a FOR and need a Loop because I have a DO

I have gone wrong somewhere in my adapting ?

Also do i need to loop the recordset ? thank you so much

Dim xl As Excel.Application, xlsht As Excel.Worksheet, xlwbk As Excel.WorkbookDim srcPath As String, srcFile As String
Dim iWeekday As Integer, iSheet As Integer, rs As DAO.Recordset
Dim StartDate As Date, EndDate As Date


srcPath = "T:\Docs\"
srcFile = "Delivery Planning.xlsx"


StartDate = InputBox("Enter Start Date ?", , "1/1/22")
EndDate = InputBox("Enter End Date ?", , "28/2/22")


iSheet = 1
For iWeekday = 1 To 7
      
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "# AND Weekday([DeliveryDate]) = " & iWeekday)
        rs.MoveFirst
        Do Until rs.EOF
        Set xl = CreateObject("Excel.Application")
        Set xlWrkBk = GetObject(srcPath & srcFile)
        Set xlsht = xlWrkBk.Worksheets(iSheet)
        xlsht.Range("A3").CopyFromRecordset rs


        rs.MoveNext


iWeekday = iWeekday + 1
Next
iSheet = iSheet + 2
Loop

[Welshgasman]

If you indented your code correctly, it would be easier to see the issue?

[Gicu]

Not at my computer right now, but looks like you need to swap the Next and Loop. Also you might want to do a quick Google search on Excel workbook.sheets() collection as it might be 0 based so you need to initialize the iSheet variable to 0 instead of 1.
Cheers,

[ssanfu]

PMFJI,

I was interested in the code... I came up with this modified code:

    Dim rs As DAO.Recordset
    Dim xl As Excel.Application, xlsht As Excel.Worksheet, xlWrkBk As Excel.Workbook
    Dim srcPath As String, srcFile As String, sSQL As String
    Dim iWeekday As Integer
    Dim iSheet As Integer
    Dim StartDate As Date, EndDate As Date


    srcPath = "T:\Docs\"
    srcFile = "Delivery Planning.xlsx"

    StartDate = InputBox("Enter Start Date ?", , "1/1/2022")
    EndDate = InputBox("Enter End Date ?", , "28/2/2022")

    Set xl = CreateObject("Excel.Application")               ' these two lines should not be inside any loops
    Set xlWrkBk = Workbooks.Open(srcPath & srcFile)          ' these two lines should not be inside any loops


    'The Weekday function can return any of these values:
    '--------------------------------------------------
    'vbSunday    = 1   Sunday 
    'vbMonday    = 2   Monday
    'vbTuesday   = 3   Tuesday
    'vbWednesday = 4   Wednesday
    'vbThursday  = 5   Thursday
    'vbFriday    = 6   Friday
    'vbSaturday  = 7   Saturday

    iSheet = 1
    For iWeekday = 2 To 6      'for Weekdays only
  ' For iWeekday = 1 To 7      'for Weekdays and Weekends
        sSQL = "SELECT * FROM tblAssign"
        sSQL = sSQL & " WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "# AND Weekday([DeliveryDate]) = " & iWeekday
        '        Debug.Print sSQL

        Set rs = CurrentDb.OpenRecordset(sSQL)
        If Not rs.EOF Then
            Set xlsht = xlWrkBk.Worksheets(iSheet)
            xlsht.Range("A3").CopyFromRecordset rs
        End If

        iSheet = iSheet + 2    ' increment the worksheet number
    Next


    rs.Close   ' close the recordset
    Set rs = Nothing
    
    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    xlWrkBk.Close True   ' close the EXCEL file and save the new data
    Set xl = Nothing
    Set xlsht = Nothing
    Set xlWrkBk = Nothing

[DMT Dave]

Bingo Steve and thanks to all, that does the trick, i just need 2 more things to complete this

1 I can do myself, is now have only selected fields in the recordset and more criteria's which i think i can do

the only one i may come across is I wouldn't mind trying to group the recordset so that only one of each "DelTo" goes to the sheet

so lets say we have been to

Steve & Vlad 9 times on a monday, 4 times on a tuesday
Vlad-Steve & Dave 5 times on a monday and 15 times on a tuesday
Joe Blogs Day 3 (Tuesday)
etc...


Monday will show

Del To

MONDAY LIST
Steve & Vlad
Vlad-Steve & Dave

TUESDAY LIST
Steve & Vlad
Vlad-Steve & Dave
Joe Blogs

ALSO CAN I GROUP THIS ALONG WITH ARRANGING BY POSTCODE Ascending ?

[Gicu]

I would suggest you create a new query using the interface, add the grouping and sorting you want, run it to check the output looks OK then have a look at its SQL code and adjust the one in the VBA to match. Make sure you do not include any unique field (such as IDs, dates\times) in the Group By as those will create duplicates.

Cheers,