Finding Date Based On Week And Day Name

[DMT Dave]

Good Day guy's, can one of you great people help with this one please ?

I am trying to return date based on DayName and which week selected, i have asked the questions in the code commented out



Thanks as always

sDay = Me.cboDay

If Forms!frmPlanning!optNextWeek = True Then
    sWeek = DatePart("ww", Date) + 1
        dtNewDate = "???????"
            'Need to find date based on sDay For sWeek ???
            'If Friday is Selected in cboDay, because optNextWeek is selected,
            'i need dtNewDate to return 18/08/23
    MsgBox (sWeek)
End If


If Forms!frmPlanning!optThisWeek = True Then
    sWeek = DatePart("ww", Date)
             dtNewDate = "???????"
            'Need to find date based on sDay For sWeek ???
            'If Friday is Selected in cboDay, because optThisWeek is selected,
            'i need dtNewDate to return 11/08/23
    MsgBox (sWeek)
End If

[Minty]

Sorry I'm not getting it. You want to either return this weeks date or next weeks based you typing in Monday, Tuesday etc??
Or if you have a combo box for the the days of the week why not simply calculate it in the combo source and present both results and pick the one you want ?

DayOfWeek	DateThisWeek	DateNextWeek
Monday	07/08/2023	14/08/2023
Tuesday	08/08/2023	15/08/2023
Wednesday	09/08/2023	16/08/2023
Thursday	10/08/2023	17/08/2023
Friday	11/08/2023	18/08/2023
Saturday	12/08/2023	19/08/2023
Sunday	13/08/2023	20/08/2023

If that's not it can you put together a small table of inputs and desired outputs?

[DMT Dave]

Hi Minty, yes that would do it instead of adding variables to give the results

How do i set the source to have a list of what you have done there ?

I have got 2 option boxes called

optThisWeek

optNextWeek

When one is selected, the other becomes false

When i select optThisWeek, i could do with requery the combo in the sub form

optThisWeek Selected
Forms!frmPlanningDS.cboDay.RowSource = ?

Monday 07/08/2023
Tuesday 08/08/2023
Wednesday 09/08/2023
Thursday 10/08/2023
Friday 11/08/2023

optNextWeek Selected
Forms!frmPlanningDS.cboDay.RowSource = ?

Monday 14/08/2023
Tuesday 15/08/2023
Wednesday 16/08/2023
Thursday 17/08/2023
Friday 18/08/2023

this is the kind of result would be great ?

Then i can to a after update event on cboDay

Me.txtDate = Me.cboDay.Column(1) or which ever column the date is stored

[DMT Dave]

or your other suggestion, I could add a tempTable as these dates are all deleted weekly

delete * in tempTable when list is cleared
then
Add 14 days to a temp table after anew import of data

would this be the easier way in your opinion and requery cboDay bound to temptable ?

[ArviLaanemets]

Why don't you simply create a separate calendary database?
The calendary database contains a calendary table with date column as primary key field, and a number of various other fields (having numeric values) e.g. date type (determining date as workday, weekend, or holiday), year number, month number (in format yyyymm), week number (in format yyyyww), weekday, number of days to previous workday (0 if the date is workday, otherwise a number from 1 to 5), number of days to next workday (0 if the date is workday, otherwise a number from 1 to 5), the number of normative working hours, etc., etc. It also contains a stored procedure which fills the calendary table with data for year determined as parameter, and can be run either manually or by schedule. The calendary table has data for reasonable number of years into future.

In case you want to use this calendary table in any other database in same LAN, you simply link it to this database. In case your database must be used outside of LAN, you have to copy the calendary table into this database (and probably you have to update the calendary there in future, unless you fill it for enough years into future at start).

Now with calendary table in your database, to get e.g. a date of certain weekday of certain week, you simply have to sum calendary dates which have this week number and weekday. There will be a single one matching those conditions, so it will be the searched answer!

The number of possible uses fur such calendary database is limited only by your imigination!

[DMT Dave]

Hi Arvil, thank you for suggestion, these dates would be updated every week

At the end of every week, all data is deleted and new import

It's only for adding new imports throughout the week then selecting are we delivering this week or next week

Then on Friday, all data is deleted and new import

every Friday, i am trying to add the following 2 weeks starting from Monday (next week)

this would only need to be done once a week

import a new record then the option of delivery this week would be if optThisWeek is Selected

[DMT Dave]

My biggest issue is that occasionally when i select optThisWeek, dates are going back 1 week

or occasionally dates a re going forward 1 week when the relevant opt is selected based on this code

this code is occasionally giving the wrong week dtDelDate

If Forms!frmPlanning!optNextWeek = True Then    If Forms!frmPlanning!optThisWeek = False Then
    dtNewDate = Date - Weekday(Date, Me.cboDay.ListIndex + 1) + 9
    End If
End If
If Forms!frmPlanning!optNextWeek = False Then
    If Forms!frmPlanning!optThisWeek = True Then
    dtNewDate = Date - Weekday(Date, Me.cboDay.ListIndex + 1) + 2
    End If
End If

cboDay RowSource is:

2;Monday;3;Tuesday;4;Wednesday;5;Thursday;6;Friday

[Minty]

Make a calendar table and simply query it.
Or use this:

SELECT NumTable.N, DateAdd("d",[n]-Weekday(Date()),Date()) AS ThisWeek, DateAdd("d",[n]+7-Weekday(Date()),Date()) AS NextWeek, WeekdayName([n]) AS DayOfWeek
FROM (SELECT 
    DISTINCT Abs([id] Mod 10) AS N
FROM 
    MSysObjects)  AS NumTable
WHERE (((NumTable.N)>0 And (NumTable.N)<8));

As you combo row source.

Edit: The weekday come out wrong... But I'll let you work that out

[Minty]

Actually took me more searching than I thought to fix that:

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

[DMT Dave]

Hi Minty, thank you, i will paste into vba and look at the method and try

thank you

[moke123]

Something like this maybe?

edit: If you only want to show todays date through friday's date you could add an if condition

Another Edit: If it's friday do you want this weeks combo to start on the following Monday? And the next week combo to show the following week?

            For i = 1 To 5
                If Weekday(Date) <= Weekday(ThisWeek) Then
                    Me.cboWK2.AddItem ThisWeek & " " & WeekdayName(Weekday(ThisWeek)) & ";" & ThisWeek
                End If
                ThisWeek = DateAdd("d", 1, ThisWeek)
            Next i

[DMT Dave]

Thanks guy's, i will take a look, i have been pulled away from this but going to come back to it very soon

Thanks again

[DMT Dave]

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

Thank you

Also yes apart from, there is only 1 combo called cboDay, this is a datasheet Subform

the optThisWeek and optNextWeek options are on a main form

options are on frmPlanning

cboDay is on frmPlanningDS

Select optTHISWeek and cboday i need listing

Column(0) Monday column(1) 07/08/23
Column(0) Tuesday column(1) 08/08/23
Column(0) Wednesday column(1) 09/08/23
Column(0) Thursday column(1) 10/08/23
Column(0) Friday column(1) 11/08/23

Select optNEXTWeek and cboday i need listing

Column(0) Monday column(1) 14/08/23
Column(0) Tuesday column(1) 15/08/23
Column(0) Wednesday column(1) 16/08/23
Column(0) Thursday column(1) 17/08/23
Column(0) Friday column(1) 18/08/23

I can then tell the DeliveryDate field in the subform for this single record a delivery date from column(1) of either 09/08/23 or 16/08/23 dependant on with option is selected (When Wednesday is chosen from the combo)

note when i click optThisWeek then optNextWeek is auto false and visa versa

[Minty]

Just for clarity my possible solution is a query for the combobox, that would always give you the current week and next week, no VBA required, except to pick the correct column.

[DMT Dave]

Hi Minty, thank you, going try that now

Thank you