Sorting By week

[BringTheR41n]

I am working in reports and i am trying to keep all of my bills organized by week. i have been messing around with it for some time and i would like for my bills to be separated by weeks. Ie:
Week 1: $5
$20
------------
Week 2:...$10


$100
------------
etc
to where all of my bills are sorted by lets say for the first week of july. the first week starts on June 29th and ends on july 5th. is there a way for access to read that and say , "ok everything from june 29th to july 5th get tagged as week 1. anything from july 6-12 is week 2..etc" i am also wanting to do a separate report for each month so it wouldnt have to go over more than week 6 at most. Thanks for the help.

[burrina]

Here are a few examples:

The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1
The last day of the current week:
Date() - WeekDay(Date()) + 7

The current month:
DateSerial(Year(Date()), Month(Date()), 1)

HTH . . . Plus we would need to know what date fields you have in your table(s) and or Queries!

[BringTheR41n]

I have my bills sorted by date due, then date paid. so if i have to pay for lets say electric by the end of the month then i have a due date of july 31 but no date paid because it has not come yet. i dont have this report based off of a query because it seemed to work without the query. where would i insert your suggestions? would i need to make a query and insert it into a criteria of the due date?

[June7]

Don't trust sorting in query to manage record order on report. Use report Sorting & Grouping features to manage sort order. The expressions shown by burrina can be applied in the report Sort configuration.

[BringTheR41n]

Is there somewhere i can input a custom sort like above? all i am finding is the group/sort pull down tabs like sort from oldest to newest, by day, with totals..etc

[June7]

Use Expression option showing below the field list.

The alternative is to use query as report RecordSource - create field in query with the expression and then the constructed field will show in the field list.

[BringTheR41n]

i have tried both examples from above but neither work. the current month example displays 7/1/2014 on all the records while the example above that displays 7/18/2014 on all records. is it even possible to sort it all into simply week by week bills in report? just basically week 1 = bills 1-10, week 2 = bills 11-20...etc. i cant figure out how to do this if it is even possible.

[June7]

The expressions burrina show use current Date(). Instead use your date field.

Don't really need a week identifier just to sort records - can sort by date field from record.

Week identifier would be useful as a group identifier.

Can let Access determine the week identifier with: DatePart("ww", {some date value here})
However, that will not include days of different years in the same week. Burrina's code will. So how do you want to deal with the week that crosses years?

[BringTheR41n]

what youre talking about is dec. 28-jan 2? with that i would be able to start a new year then. would it really be that big of a deal when the new year arrives?

[June7]

Did you mean Jan 3?

That's for you to decide. If you want to select/group records for an entire week (Sun-Sat) regardless of month and year, then use burrina's suggested code.

Note that DatePart("w", {some date value here}) will show week numbering for each month and has the same issue for week that crosses months.

Be aware you will need the year part to further sort/group records if your dataset includes multiple years.

[BringTheR41n]

ya i meant jan 3. if i use burrina's code for over a year, would i start to get duplicate week results?

[June7]

burrina's suggestion to calculate a week start or week end date and use that for selecting/grouping will not have duplicate issue.

Certainly week numbers will show for every year. So can calculate another field to pull year: Year({some date value here})

[BringTheR41n]

alright ill mess with it some. thank you for the help