Help with conditional expression

[Guest]

I am trying to set up an expression in a report that will show the number of
days elapsed during the current year for an item whose date field might
actually be from a previous year. For instance, the date field which I have
named orig, might actually be 01/01/04. In that case, I want to know the
number of days elapsed in t he current year. But if the orig field is
04/01/05, then I would want to know the number of days elapsed since
04/01/05. This is the code I am trying to write, but it appears to be
ignoring the first half of the statement, and is just giving me results from
the actual date instead:

=Date()-(IIf([orig]<1/1/2005,1/1/2005,[orig]))

Any help would be much appreciated.

Steve

 

[Brian Camire]

You might try an expression like:

=Date()-IIf([orig]<DateSerial(2005,1,1),DateSerial(2005,1,1),[orig])

or, in the general case

=Date()-IIf([orig]<DateSerial(Year(Date()),1,1),DateSerial(Year(Date()),1,1)
,[orig])

 

[Guest]

That did the trick.

Thanks Brian

Steve

You might try an expression like:

=Date()-IIf([orig]<DateSerial(2005,1,1),DateSerial(2005,1,1),[orig])

or, in the general case

=Date()-IIf([orig]<DateSerial(Year(Date()),1,1),DateSerial(Year(Date()),1,1)
,[orig])

I am trying to set up an expression in a report that will show the number of
days elapsed during the current year for an item whose date field might
actually be from a previous year. For instance, the date field which I have
named orig, might actually be 01/01/04. In that case, I want to know the
number of days elapsed in t he current year. But if the orig field is
04/01/05, then I would want to know the number of days elapsed since
04/01/05. This is the code I am trying to write, but it appears to be
ignoring the first half of the statement, and is just giving me results from
the actual date instead:

=Date()-(IIf([orig]<1/1/2005,1/1/2005,[orig]))

Any help would be much appreciated.

Steve

 

[Brian Camire]

What was happening in your original expression is that

1/1/2005

was being interpreted as 1 divided by 1 divided by 2005.

An alternative to my suggestion would be to put # characters around the
date. This identifies what's between as a date, as in:

=Date()-(IIf([orig]<#1/1/2005#,#1/1/2005#,[orig]))

I prefer the DateSerial approach though, because there is less room for
confusion in interpreting the date format under different regional settings.

That did the trick.

Thanks Brian

Steve

You might try an expression like:

=Date()-IIf([orig]<DateSerial(2005,1,1),DateSerial(2005,1,1),[orig])

or, in the general case

=Date()-IIf([orig]<DateSerial(Year(Date()),1,1),DateSerial(Year(Date()),1,1)
,[orig])

I am trying to set up an expression in a report that will show the

number
of

days elapsed during the current year for an item whose date field might
actually be from a previous year. For instance, the date field which I have
named orig, might actually be 01/01/04. In that case, I want to know the
number of days elapsed in t he current year. But if the orig field is
04/01/05, then I would want to know the number of days elapsed since
04/01/05. This is the code I am trying to write, but it appears to be
ignoring the first half of the statement, and is just giving me

results
from

the actual date instead:

=Date()-(IIf([orig]<1/1/2005,1/1/2005,[orig]))

Any help would be much appreciated.

Steve