Nested If Statements II

[Alex Hammerstein]

Hi,

I am slowly driving myself crazy...

I am trying to assertain the sum of the weeks that our students have been on
a course between 01/Aug/2007 and 31/Jul/2008

I have tried to breakdown the various lines as follows:

Weeks:
Sum
Iif [Date Started] < #01/08/07#
Iif [Date left] > #31/07/08#
DateDiff (³ww²,#01/08/07#,#31/07/08#)
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww²,#01/08/07#,[Date Left])
Iif [Date Started] Between #01/08/07# And #31/07/08#
Iif (IsNull([Date Left])
DateDiff(³ww², [Date Started],#31/07/08#
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww,[Date Started],[Date Left])


Can someone advise me my logic above is correct and also how do I work out
where all the brackets have to go.

V many thanks

Alex

 

[Sylvain Lafontaine]

Well, you must learn how to make the simplicity flourish out of the
complexity. In this case, you first extract those with the dates out of
range by returning 0 and then you compute value for the others with
adjusting the beginning and the end if necessary. Of course, the very first
thing to do would be to replace [Date Started] and [Date Left] with
something easier to read like DateStarted and DateLeft and then, after that
(not tested):

IIF (DateStarted > #31/07/08" or DateLeft < #01/08/07#, 0,
DateDiff (ww,
IIF (DateStarted < #01/08/07#, #01/08/07#, DateStarted),
IIF (DateLeft > #31/07/08# or IsNull (DateLeft), #31/07/08#,
DateLeft)
)
)

There are other solutions to your problem. The first one would be to
construct a weeks table with all the weeks; you join it to your first result
and you compute the number of weeks by grouping the total number of rows. A
second possibility would be to learn how to make a VBA function that can be
called from your querydef. By using a VBA function, you can easily code
complex logic.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John W. Vinson]

Hi,

I am slowly driving myself crazy...

I am trying to assertain the sum of the weeks that our students have been on
a course between 01/Aug/2007 and 31/Jul/2008

I have tried to breakdown the various lines as follows:

Weeks:
Sum
Iif [Date Started] < #01/08/07#
Iif [Date left] > #31/07/08#
DateDiff (³ww²,#01/08/07#,#31/07/08#)
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww²,#01/08/07#,[Date Left])
Iif [Date Started] Between #01/08/07# And #31/07/08#
Iif (IsNull([Date Left])
DateDiff(³ww², [Date Started],#31/07/08#
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww,[Date Started],[Date Left])


Can someone advise me my logic above is correct and also how do I work out
where all the brackets have to go.

V many thanks

Alex

First off, date literals must be in American month/day/year format, regardless
of your computer's regional settings (or you can use an unambiguouse format
such as #2008-07-31#).

Second, IIF is a function that takes three arguments separated by commas: an
expression; the value to return if that expression is TRUE; the value to
return if it's FALSE. You're missing quite a few commas.

Thirdly, you're apparently using Word or WordPad "smart quotes", which I see
here as superscript 2 and 3. They won't work - use a singlequote instead.

Finally, if you're using the literal year, you'll probably need to rewrite the
query every year, which will be a pain in the neck. The DateSerial function
may make this easier on a couple of counts; it takes three integer arguments,
a year, a month and a day and returns a date/time value.

I'm getting a little lost in the nesting here. Could you explain in words what
the logic is? I am wondering if the Switch() function might be better than
nested IIFs - it often is for multiple conditions.

 

[Alex Hammerstein]

Hello John, and thanks for your help

We have records of students who are on a course. We have a [Date Started]
field and a [Date Left] field.

I need to establish the total number of students weeks (number of students x
numbers of weeks each student has been on the course) between 01/08/07 and
31/07/08.

Students may have started the course before 01/08/07 or during the year in
question, and may have left during or after the year in question. However I
am only interested in the total number of student weeks during the year.

If a student started before 01/08/07 and was still on the course after
31/07/08 then that would be 52 students weeks. If a student started
01/09/07 and finished 30/06/08then that would be 44 weeks


Hope this helps

Thanks

Alex

Hi,

I am slowly driving myself crazy...

I am trying to assertain the sum of the weeks that our students have been on
a course between 01/Aug/2007 and 31/Jul/2008

I have tried to breakdown the various lines as follows:

Weeks:
Sum
Iif [Date Started] < #01/08/07#
Iif [Date left] > #31/07/08#
DateDiff (³ww²,#01/08/07#,#31/07/08#)
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww²,#01/08/07#,[Date Left])
Iif [Date Started] Between #01/08/07# And #31/07/08#
Iif (IsNull([Date Left])
DateDiff(³ww², [Date Started],#31/07/08#
Iif [Date Left] Between #01/08/07# And #31/07/08],
DateDiff(³ww,[Date Started],[Date Left])


Can someone advise me my logic above is correct and also how do I work out
where all the brackets have to go.

V many thanks

Alex

First off, date literals must be in American month/day/year format, regardless
of your computer's regional settings (or you can use an unambiguouse format
such as #2008-07-31#).

Second, IIF is a function that takes three arguments separated by commas: an
expression; the value to return if that expression is TRUE; the value to
return if it's FALSE. You're missing quite a few commas.

Thirdly, you're apparently using Word or WordPad "smart quotes", which I see
here as superscript 2 and 3. They won't work - use a singlequote instead.

Finally, if you're using the literal year, you'll probably need to rewrite the
query every year, which will be a pain in the neck. The DateSerial function
may make this easier on a couple of counts; it takes three integer arguments,
a year, a month and a day and returns a date/time value.

I'm getting a little lost in the nesting here. Could you explain in words what
the logic is? I am wondering if the Switch() function might be better than
nested IIFs - it often is for multiple conditions.

 

[Alex Hammerstein]

Hi Sylvain, and thanks for your help.

Your suggested resolution is certainly very near and elegant. I am just
about to try it. I will let you know how I get on

I agree that I must learn to think more laterally and outside of the box....

Alex

 

[Sylvain Lafontaine]

For cases like these, joining with a secondary table containing the
description of each week is usually the best solution because you can
precisely control for things like vacancy, etc.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi Sylvain, and thanks for your help.

Your suggested resolution is certainly very near and elegant. I am just
about to try it. I will let you know how I get on

I agree that I must learn to think more laterally and outside of the
box....

Alex

Well, you must learn how to make the simplicity flourish out of the
complexity. In this case, you first extract those with the dates out of
range by returning 0 and then you compute value for the others with
adjusting the beginning and the end if necessary. Of course, the very
first
thing to do would be to replace [Date Started] and [Date Left] with
something easier to read like DateStarted and DateLeft and then, after
that
(not tested):

IIF (DateStarted > #31/07/08" or DateLeft < #01/08/07#, 0,
DateDiff (ww,
IIF (DateStarted < #01/08/07#, #01/08/07#, DateStarted),
IIF (DateLeft > #31/07/08# or IsNull (DateLeft), #31/07/08#,
DateLeft)
)
)

There are other solutions to your problem. The first one would be to
construct a weeks table with all the weeks; you join it to your first
result
and you compute the number of weeks by grouping the total number of rows.
A
second possibility would be to learn how to make a VBA function that can
be
called from your querydef. By using a VBA function, you can easily code
complex logic.