Search code examples
ms-accessweek-numberdatepart

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

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!

Solution

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