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

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!

CodePudding user response：

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)