Finding Date Based On Week And Day Name

[DMT Dave]

Hi Minty, this is what i have done with yours but my understanding isn't good of your method which i am sure works if I understood



I have tried your method to fill a combo but the combo just fills with the code

combo is set to value list, i am guessing that is correct ?

Dim sSQL As String

sSQL = "SELECT NumTable.N, DateAdd(""d"",[n]-Weekday(Date()),Date()) AS ThisWeek, DateAdd(""d"",[n]+7-Weekday(Date()),Date()) AS NextWeek, WeekdayName(n,0,1) as DoW " _
        & "(From " _
        & "(SELECT " _
        & "DISTINCT Abs([id] Mod 10) AS N " _
        & "FROM " _
        & "MSysObjects)  AS NumTable " _
        & "WHERE (((NumTable.n) > 0 And (NumTable.n) < 8))"
        
 Forms!frmPlanning!frmPlanningDS!cboDay.RowSource = sSQL

[Minty]

I suspect you forgot to change the source type to Table/Query??

Just save it as a query qryNextTwoWeeks and set that as the rowsource?
You're never going to change it, so why get complicated with it.
If it's not working as is debug.print sSQL and chuck it in the query editor to see where it falls over.

And if your are running that from the form it's on simply use

Me.cboDay.RowSource = sSQL

[Minty]

combo is set to value list, i am guessing that is correct ?

I missed this in your reply - no set it to Table/query

[moke123]

Hi Moke123, I've downloaded the database, there is no VBA in there and no update on combos when selecting ?

I just downloaded a new copy and the vba is there. Its all in the form module

Option Compare Database
Option Explicit

Private Sub Form_Load()

    sGetDates

    sGetDatesNoWeekend

End Sub


Private Sub Frame0_AfterUpdate()

    sGetDates

    sGetDatesNoWeekend

End Sub

Sub sGetDates()

    Dim ThisWeek As Date
    Dim NextWeek As Date
    Dim i As Integer

    ThisWeek = Date - Weekday(Date, 1) + 1
    NextWeek = DateAdd("ww", 1, ThisWeek)
    Me.cboWK.RowSource = ""

    Select Case Me.Frame0

        Case 1
        
            For i = 1 To 7
                Me.cboWK.AddItem ThisWeek & " " & WeekdayName(Weekday(ThisWeek)) & ";" & ThisWeek
                ThisWeek = DateAdd("d", 1, ThisWeek)
            Next i

        Case 2
        
            For i = 1 To 7
                Me.cboWK.AddItem NextWeek & " " & WeekdayName(Weekday(NextWeek)) & ";" & NextWeek
                NextWeek = DateAdd("d", 1, NextWeek)
            Next i

    End Select

End Sub

Sub sGetDatesNoWeekend()

    Dim ThisWeek As Date
    Dim NextWeek As Date
    Dim i As Integer

    ThisWeek = Date - Weekday(Date, 2) + 1
    NextWeek = DateAdd("ww", 1, ThisWeek)

    Me.cboWK2.RowSource = ""

    Select Case Me.Frame0

        Case 1
        
            For i = 1 To 5
                Me.cboWK2.AddItem ThisWeek & " " & WeekdayName(Weekday(ThisWeek)) & ";" & ThisWeek
                ThisWeek = DateAdd("d", 1, ThisWeek)
            Next i

        Case 2
        
            For i = 1 To 5
                Me.cboWK2.AddItem NextWeek & " " & WeekdayName(Weekday(NextWeek)) & ";" & NextWeek
                NextWeek = DateAdd("d", 1, NextWeek)
            Next i

    End Select

End Sub

[DMT Dave]

Ahh Minty, yes sorry, i certainly did misunderstand that method

@Moke123, thank you also for input, will come back to it very shortly and update you all

Kindest

[DMT Dave]

Hi Minty, yes done exactly as you suggested, here is the query result when running

It's a day out ? i guess is that a simple change the + - Day in DateAdd ?

today Wed 9th

[DMT Dave]

@Moke123, i see how yours is generated now, option to rule out weekend, will also have a read through it again to soak in the calcs

[Welshgasman]

Hi Minty, yes done exactly as you suggested, here is the query result when running

It's a day out ? i guess is that a simple change the + - Day in DateAdd ?

today Wed 9th

There is an option for starting day of the week?

However I have not touched the code and I get?????

Hmm for some reason I am unable to upload my pic, but I get the 9th as a Wednesday?

Stupid site never gave any indication it had loaded the pic and kept telling me to select one???

[DMT Dave]

Hi WGM, yes very true, Sunday is usually day 1 with system date, is that what you are referring to ?

[Welshgasman]

Hi WGM, yes very true, Sunday is usually day 1 with system date, is that what you are referring to ?

Yes, but with no changes to Minty's code, see my pic?

[DMT Dave]

Yes, no changes to Mintys code on my side and get the 8th as wed today and 15th for next week

I am curious about NumTable where Minty has added to a table that doesn't exist, never seen that before, i always thought you need a source table, interesting

I also like Moke123 version

Minty's version is perfect for me also because once the dates are set correct, when i choose optThisWeek then delivery date is Combo Column(1), when optNExtWeek is selected then delivery date is Column(2)

i have tried changing the criteria numbers in N field but getting incorrect dates as you see

This qry also will be correct 365 without updating is by the looks of it

1st and last line didn't make sense, changed values in N field then left it how Minty wrote it!!!

[Welshgasman]

Well my first day is Sunday, yours is Monday?

[DMT Dave]

ahh WGM, your N field 1 starts with Sunday, mine starts with Monday, just seen the differences on images

My Sunday is day 7

[moke123]

I have got 2 option boxes called

optThisWeek

optNextWeek

When one is selected, the other becomes false

Are you not using an Option Group?

[DMT Dave]

Moke123 No using 2 yes/No option check boxes

1 called optThisWeek

the other called optNextWeek

if one is selected, the other is false

Hope this clarifies, between your good self and Minty, there is 2 options for me which as i always mention, totally appreciated

If opt this week selected, your suggestion is 1 case statement and optNextWeek the other case statement ?