Access DatePart function will not give the correct week number when used in a tabular form text box Control Source

Question

I am attempting to create a tabular form in Access that lists the date and week number for each record. When I use the Format option in the textbox property sheet (ww), my weeks are off by 1 week (the week of Dec. 19, 2021 is considered week 52). I have been able to correct this in an unbound field in the header using VBA

DatePart("ww", Date, vbSunday, vbFirstFourDays)

but I can't get the same code to work in the textbox Control Source for the tabular form. I can get the same wrong week 52 answer using:

=DatePart("ww", [dtmEventStartDate])

but both of the following give me a "#Name?" error in the text box when the form loads:

=DatePart("ww",[dtmEventStartDate],[vbSunday],[vbFirstFourDays])

=Format([dtmEventStartDate],[ww],[vbSunday],[vbFirstFourDays])

Oddly, the pop-up help for the Control Source seems to indicate that the format I am using is supported, but then it proceeds to not work. Any help would be greatly appreciated!

score 0 · Accepted Answer · answered Dec 23 '21 at 19:45

0

Access doesn't know about VBA constants like vbSunday, vbFirstFourDays and makes assumption these are field names.

Use the number values. =DatePart("ww", [dtmEventStartDate], 1, 2)

answered Dec 23 '21 at 19:45

June7

19,874
8
24
34

Thanks! I tried using the numbers, but had them in square brackets which I now know doesn't work. Thanks again! – Bert Dec 23 '21 at 20:34
Correct, brackets define object names. – June7 Dec 23 '21 at 20:41

Access DatePart function will not give the correct week number when used in a tabular form text box Control Source

1 Answers1