Concatenate calculated fields

[renrut]

I am using Access in Office 365 ProPlus. I have a table with access card reads and the field I'm working from is the [date/time] field. With this field, I would like to show the week (so 12/1/2020 would be week 49) and then concatenate fields so I can show the range that the date field falls within (Work Week 49 would be comprised of November 30 to December 4 and would show as "Work Week 49: Nov 30 - Dec 04"



So far, I can get the work week showing with DatePart("ww",[date/time]), but I can't - for the life of me - figure out how to get it to pull the first work day of this criteria and concatenate to the last day of this criteria. HELP???

Please note I am a very poor user of Access and only understand the very basics.

[ranman256]

usage:
calcWeekRangeTxt([datefld])

 'give any date and get the date that starts the week
Public Function calcWeekRangeTxt(ByVal pvDate)
Dim iDow As Integer, iSTARTDAY As Integer
Dim i As Integer
Dim vStart, vEnd


iSTARTDAY = vbSunday
'iSTARTDAY = DLookup("[StartOfWeek]", kCFG)     'use the config table value, set by admin

iDow = Format(pvDate, "w")
Select Case True
   Case IsNull(pvDate)
     'nothing to do
  Case Else
        vStart = DateAdd("d", -(iDow - 1), pvDate)
        vEnd = DateAdd("d", 6, vStart)
        calcWeekRangeTxt = "Week:" & DatePart("ww", pvDate) & " from " & vStart & " to " & vEnd
End Select
End Function

[Micron]

Given your level of expertise I'm going to suggest that you need to copy the db, compact and then zip it and post a copy here. AFAIC, you are going to need a custom function because of the complexity (multiple values need to be calculated). Just getting

Work week 49: Mon 01
seems to require

"Work week " & datepart("ww",#12/01/2020#) & ": " & Format(weekday(#12/01/2020#,vbMonday),"ddd") & " " & Format(weekday(#12/01/2020#,vbMonday),"mm")
and that's without getting the month, and that's only for the first day/date

I may not have the time to write this, but there are a few very good coders here and if you provide a db, they will probably jump on it. I'm supposed to be building living room tables but I drop in on my breaks!

[isladogs]

I'm supposed to be building living room tables but I drop in on my breaks!

Hi Micron
You do seem to have a lot of breaks each day ....

[Micron]

...because I work for a very lenient boss!

[isladogs]

...because I work for a very lenient boss!

LOL. Me too!