Year-to-Date by week

[froggiebeckie]

I've got a table with data from 3 machines.
I've got queries with calculated fields that return the needed info, totaled by day or month or year.

So far, so good.

Now, the powers-that-be want a trend chart showing numbers by week, for the current year.


To save myself, I can't figure out how to query the info by week.

I'm sure it's something simple, but I'd sure apprecieate any help you folks can aim my way.

Thanks,
Froggiebeckie

 

[Mile-O]

Probably add a new field into the query:

WeekNo: Format("ww", [DateField])

Crosstab or query it accordingly

 

[froggiebeckie]

I must be missing something.

WeekNo: Format("ww",[DATE])

That just returns "ww" in that field.

FroggieBeckie

 

[froggiebeckie]

Oh boy, sometimes I'm so dense.

Changed the " to a ' and reordered it and it gives me the number of the week.
WeekNO: Format([Date],'ww').

Thanks, now I can move forward and have it give the end of week date.

Thanks again,
Froggiebeckie

 

[yoco]

Hi man!

The correct formula to give the week number is:
DatePart("ww",[Your_Date_field])

This will help you to update your field:
Private Sub Your_Date_field_AfterUpdate()
[Your_Week_field] = DatePart("ww", [Your_Date_field])
End Sub

 

[Rich]

Hi man!

The correct formula to give the week number is:
DatePart("ww",[Your_Date_field])

This will help you to update your field:
Private Sub Your_Date_field_AfterUpdate()
[Your_Week_field] = DatePart("ww", [Your_Date_field])
End Sub

The formula Mile gave is equally correct and storing the Week when the actual date is stored is totally unnecessary