date nightmare

[BJM]

I have a database that stores date field values as week
numbers of a school year. Semester 1 has 20 weeks but two
are the Christmas holiday. The calculations to this point
have simply taken the value of September 1st as week one
and count up from there.

Now that Christmas is over, all week numbers report two
too many! How can I get it to omit the two weeks over
Christmas but only when the value is January 5th or better?

<ARGH>

BJM

 

[Marshall Barton]

I have a database that stores date field values as week
numbers of a school year. Semester 1 has 20 weeks but two
are the Christmas holiday. The calculations to this point
have simply taken the value of September 1st as week one
and count up from there.

Now that Christmas is over, all week numbers report two
too many! How can I get it to omit the two weeks over
Christmas but only when the value is January 5th or better?

<ARGH>

Arrrgggghhhhh is a good way to sum up this data ;-)

You didn't post the calculation that needs to be adjusted,
but I think you might get by with adding a term something
like:

- IIf(datefield > DateSerial(Year(datefield), 1, 5) And
datefield < DateSerial(Year(datefield), 7, 1), 2, 0)