DatePart Question

[GregSmith]

I have this line listed as the control source.

="( " & Format([Date],"dddd") & " Week " & DatePart("ww",[Date]) & " )" & DatePart("ww","12/31/" & [year])

The last DartPart shown in red does not work.

What I am trying to do is get it to display the following:
"Monday Week 6 of 53."

The only thing is that every now and then you will hit a week 54.

Any ideas on how to get this to work correctly??

 

[charityg]

Try
="( " & Format([Date],"dddd") & " Week " & DatePart("ww",[Date]) & " )" & DatePart("ww",CDate("12/31/" & [year]))

The function can't find the datePart of a string value, so you should convert the string to a date.

 

[GregSmith]

Good idea but it does not display.
It gives the #Name? in the field now.

 

[GregSmith]

This works:

="( " & Format([Date],"dddd") & " Week " & DatePart("ww",[Date]) & " )" & DatePart("ww","12/31/" & Year([Date]))

 

[RayH]

I have used this and it 'works for me'.
Format([Date], "dddd") & " Week " & DatePart("ww", [Date]) & " of " & DatePart("ww", "12/31/" & [Year])

When I ran with it [Date] and [Year] are fields on a form.

Are you getting an error when you run yours?
I can only think that it doesn't know what [Year] is or [Year] has an invalid value.

 

[Pat Hartman]

Date and Year are really poor choices for user defined object names. They are both the names of VBA functions. You are likely running into a problem where VBA can't tell that you are referencing your fields and it thinks you are referencing its functions. Change the names!