asked on

Please modify the query (dates related)

Hi, everybody!
="Total for Week # " & Format$([InspectionDate],"ww",0,0)---> will give me number of the week (32 )
Total for Week# 32

how to make ------>(first day of the week -last day of the week)
Total for Week# 32 (08/04/2008-08/11/2008))

Rey Obrero (Capricorn1)

format(DateAdd("d",first day of the week -last day of the week,last day of the week),"ww")

Roman F

ASKER

sorry,
"the expression you entered contains invalid syntax
you may have entered a comma without a preceding value or identrifier"

GRayL

="Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy")

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman F

ASKER

To GRayL::
same error...

GRayL

I tested my answer in the Immediate pane before posting. Try it by typing:

InspectionDate = #08/05/2008#

? "Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy") & ")"

This is all one line.

If you need the line breaks, add the line continuation character (underscore followed by Enter.

? "Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & _
Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & _
Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy") & ")"

Gustav Brock

Well, use my formula:

="Total For Week # " & Format$([InspectionDate],"ww", 0, 0) & " (" & Format(DateWeekFirst([InspectionDate], 2), "mm/dd/yyyy") & "-" & Format(DateWeekFirst(DateAdd("ww", 1, [InspectionDate]), 2), "mm/dd/yyyy") & ")"

It will even return the correct end date.

/gustav

Roman F

ASKER

thank You, great

Gustav Brock

You are welcome!

/gustav