Finding a week number from a given date - Custom Defined Week Numb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which has a set of Week Numbers and the associated date (from
Sunday) for that week. I would like to reference the Week Number from a form
when a user enters a particular date. I can do this in Excel using VLookup,
which returns the nearest match, but am having problems with DLookup or
coming up with a suitable query.

Many Thanks
 
DatePart("ww",[MyDate]) will give you the week number. So will
Format([MyDate],"ww").
 
I have a table which has a set of Week Numbers and the associated date (from
Sunday) for that week. I would like to reference the Week Number from a form
when a user enters a particular date.
Click to expand...

If you're not using ISO standard week numbers, your table isn't
complete. (Actually, even if you *are* using ISO standard week
numbers, your table isn't complete.) The data *should* look something
like this:

WeekNumbers
--
CalendarDate WeekNumber
01-Jan-2004 1
02-Jan-2004 1
03-Jan-2004 1
04-Jan-2004 1
05-Jan-2004 2

Lookups are dead simple, and they can take advantage of indexes. A
hundred years of calendar dates is less than 37,000 rows. Use Excel
to generate the data.
 
Thanks Mike,
It's still not quite the right solution, but you've given me an good work
around, the week numbers relate to a week in what you would call a semester
and what we would call a term. But I've taken your advice and set up a
spreadsheet with the dates in and used the Day function to identify the day,
which then allows me to quickly identify the start of the week in order to
use an autofill.

Cheers
 
Back
Top