Get Date Of This Week Based On Day Name

[DMT Dave]

Hi Guy's i have had a look at this one and trying to work the method

I have a Combo value List

Listing Is Sunday;Monday;Tuesday;Wednesday;Thursday;Friday;Sa turday

I have a date field called txtNextDate

What i am looking for is when i select Wednesday



txtNextDate would be updated with a date of Wednesday of NEXT WEEK not this week

I am going to update on Friday's for the following week, it's finding the date to add based on the Day name selected for week no + 1

hope this makes sense

Thanks guy's

[CJ_London]

all depends on when you are entering the data - does the day list you have relate to the week of today (i.e. w/c Sunday 29th Jan)? i.e. today is Friday (3rd Feb) and the user selects Saturday - is the required value the following day (4th Feb)? or the Saturday after (11th Feb)? Your description implies the 4th Feb but just seem a bit strange. Always better to provide some examples rather that just describing it

[Gicu]

Where is this combo and does relate to the date field used to calculate the NextDate (which should be a simple NextDate: DateAdd("ww",1,[CurrentDate]))?

Cheers,

[Micron]

I think it would help if you paired your day names with their weekday number if you stick with a value list. Could also base this on a small table where one field contains the number and another the day name. Then you could compare the number (Tuesday combo choice would be 3 based on what you've shown). Once you have the 3 value, you can compare the day value of the current date, then decide how far to move ahead. However, that assumes you're wanting to compare the current day against your combo choice.

This DateAdd("ww",1,[CurrentDate])) assumes the goal is one week from the current day but I don't think that has been established. If today is Monday and I pick Wednesday, do we want Wednesday next week and not Monday next week or not? My suggestion is based on "not".

Choosing only Wednesday to explain your issue is not enough, as CJ_London says.

[Gicu]

I assume Dave has a date field somewhere (my [CurrentDate] example, not to be confused with Date()). So I was asking how is the combo with just the day names linked to that date field which provides the starting point (for the week at least).

Cheers,

[DMT Dave]

hi guys thank you for your replies, my combo with listed day names not related to any field, just a value list

the date is a field from table

if i chose if it was Tuesday ie today, 31/01/23, then date field needs to be always next week 07/02/23

so whatever day is selected, its always next weeks date based on day name selected

have i helped you guys to help me ?

[DMT Dave]

vlad, if i am correct, if your currentdate was a day name selected, this maybe the answer as i can see you are adding 1 x week

[Micron]

And if you chose Monday when the calendar day was Tuesday??
If that's not how it works, then fine but if you want 7 days from the DATE you select the day value and that value will always be on the same day as the Day Date, then sure, add 7 days.

[Gicu]

The trick is to convert your selection into a date.
Have a look at this link.
https://stackoverflow.com/questions/43254203/get-tuesdays-date-for-current-week-using-vba
I adjusted the last formula to fit your case (the combo's ListIndex is 0 based and vbSunday is 1 so we add 1 to convert your combo value to vbDay constants):

NextDate: Date- Weekday(Date, [cboYourCombo].ListIndex+1) + 8

Cheers,

[DMT Dave]

Hi Vlad, thank you, nearly what i am trying to achieve

Just adding date incorrectly

It should have added 07/2/23 as that's Tuesday next week

[Minty]

Make your combo row source a query that picks the days of the week for this week, and then Labels them with the days of the week, this makes adding 7 days to the dates simple.

[DMT Dave]

Thanks Minty, so i can understand correctly

cboDay listing Sunday To Saturday

Which ever day I select, the dater will add as date for (ALWAYS NEXT WEEK)

Is set to Value List then the days listed

Do i need to change this to Table/Query then add a table with 7 days in it ? update the table weekly with today's date ?

Sorry i am slightly lost with the method!!! apologies

[DMT Dave]

I got so close with Vlad's option, do i need to just add a text box that is hidden but will always have todays date so the system knows to add

cboDay textbox today + datePart of cboDay ?

I think i maybe going the wrong direction ?

[CJ_London]

think about it logically

today (1st Feb) is Wednesday which is the 4th day (Sunday being the first day - day 1)
?weekday(date)
4

deduct this from the date


?date-weekday(date)
28/01/2023

which is a Saturday, but not a problem

assuming your combo is using 1 for Sunday, 2 for Monday etc, add your combo (say Thursday)

?date-weekday(date)+5
02/02/2023

but you don't want tomorrow, you want the following Thursday - so add 7 days

?date-weekday(date)+5+7
09/02/2023

to verify
?weekday(date-weekday(date)+5+7)
5

[ArviLaanemets]

On fly!
You have a combo like cbbWD. Set it's RowSource like
"1,Sunday,2,Monday,3,Tuesday,4,Wednesday,5,Thursda y,6,Friday,7,Saturday", BoundColumn to 1, and ColumnWidths as "0,2.5". When you activate the combo, you see weekday names. When you select a weekday, the value of combo will be the weekday number (form 1 to 7);
You have a text box txtNextDay;
Create a Change event for cbbWD, which sets txtNextDay equal with

Date + 7 + (Weekday(Date) - Me.cbbWD)