Formula/table assistance

  • Thread starter Thread starter Dechienne
  • Start date Start date
D

Dechienne

I need help with the fine details behind this idea:

Need to pull a weeks worth of data from a database (Outbound - Total : Date)
I have a date conversion table that takes every day of the year - factors it
into which week it fall into (Starting on the Monday), as well as month, and
day of week (Example: 3/20/2006-3/26/2006 is in week 3/20/2006)
I need to be able to provide a set date formula (Like Date()-1) have the
formula look at that date, figure out what week it falls into, and then
query the table for all dates within that week and spit out the data.

The main developer keeps telling me "Oh yeah that's super easy, takes like 5
seconds and you are going to laugh at how simple this is", 3 months later
and I'd still like this so I can put a few finishing touches on some reports
 
I would create a global VBA function that returns the week number, based on
a date passed to it. Then in the query, call that function.

e.g.
Select * from tablename where tablename.weekno = GetWeekNo([SomeDateField])

HTH.
 
So if it is that easy, then have the main developer spend 30 seconds and
show you how to do it.

Barring that the following may help you.

The following gives you Monday (day 2) of the week involved.

DateAdd("d",1-Weekday([YourDateField],2),[YourDateField])


Assumption:
Your date field does NOT have a time component (other than midnight)

In a query to get all records in a week where you specify any date in that
week
SELECT *
FROM SomeTable
WHERE SomeDate
Between DateAdd("d",1-Weekday([Your Date Input],2),[Your Date Input]) and
DateAdd("d",7-Weekday([Your Date Input],2),[Your Date Input])
 
Back
Top