Finding Date Based On Week And Day Name

[moke123]

I use an option group. It's designed to do what your apparently doing with code.

Here's another version. In this one I use a function to return a string of either this weeks or next weeks dates which is the row source of the combo box.
I also included a listbox as another option.

[DMT Dave]

Moke123, thank you, i did read through the code of your suggestion, i am going to download and go have a look, i have more time today to do this so happy days, as always, greatly appreciate all of your suggestions

[Minty]

The part that determines the weekday start of the week is in red below:

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

The details of which are here : https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/weekdayname-function

[Welshgasman]

@moke123
When I run that, I get Tuesday to Saturday as the 9th is being a Thursday?
I have now amended that code with vbMonday.

Also I get the dates in mm/dd/yyyy format?, yet my system is dd/mm/yyyy, as I am in the UK?

[moke123]

@moke123
When I run that, I get Tuesday to Saturday as the 9th is being a Thursday?
I have now amended that code with vbMonday.

Also I get the dates in mm/dd/yyyy format?, yet my system is dd/mm/yyyy, as I am in the UK?

I'm a Yank so I code in American.

You can change this line to format the dates

       strItem = Format(dte, "mm/dd/yyyy") & "  " & WeekdayName(Weekday(dte), True)

Not sure why you get tuesday to saturday other than perhaps you have different settings.

[Welshgasman]

I'm a Yank so I code in American.

You can change this line to format the dates

       strItem = Format(dte, "mm/dd/yyyy") & "  " & WeekdayName(Weekday(dte), True)

Not sure why you get tuesday to saturday other than perhaps you have different settings.

Damn!
Sorry, never noticed that despite adding vbMonday.

Getting so used to having to format that way for SQL etc.

[DMT Dave]

Thanks guy's i have come back to this now, going to check out Moke123 Option Group method, Minty's version of qryNext2Weeks works well, but will look at option group

Thanks again

[Welshgasman]

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

Are you sure you have not mucked about with his code?

As this

WeekdayName(n,0,1) as DoW

determines the day shown?

[DMT Dave]

Hi WGM, yes added that in, for some reason the query wouldn't save the changes but i have had numerous internet/server/telephone issues today for some reason so going to try again, had 3 restarts!! so i am unsure if that were the case

I will delete the query and start a new one with this added from SQL before saving

[Welshgasman]

Just copy and paste the latest version.