Problems with Date calculation in Query

[Murphybp2]

I have a query I use to identify the age of certain records, and then
group them in corresponding age buckets. I am using the following
statement:
Expr1: IIf(Date()-[RequestDate]<="3","1-3
days",IIf(Date()-[RequestDate]<="7","4-7
days",IIf(Date()-[RequestDate]<="13","8-13
days",IIf(Date()-[RequestDate]<="21","14-21
days",IIf(Date()-[RequestDate]<="29","22-29 days","30+ days")))))

I am having some problems with this though. For some records,
everything works fine. But for other records, the formula puts them in
the wrong category. I have one record that was created in October, but
is falling in the 4-7 days category.

Does anyone have any suggestions on what I can do differently to get
this to work right??

 

[kingston via AccessMonster.com]

Date()-[RequestDate] should result in a number, not a string. So there is no
need for quotation marks around the comparison values:

Expr1: IIf(Date()-[RequestDate]<=3,"1-3 days",IIf(Date()-[RequestDate]<=7,"4-
7 days",IIf(Date()-[RequestDate]<=13,"8-13 days",IIf(Date()-[RequestDate]<=21,
"14-21 days",IIf(Date()-[RequestDate]<=29,"22-29 days","30+ days")))))

I have a query I use to identify the age of certain records, and then
group them in corresponding age buckets. I am using the following
statement:
Expr1: IIf(Date()-[RequestDate]<="3","1-3
days",IIf(Date()-[RequestDate]<="7","4-7
days",IIf(Date()-[RequestDate]<="13","8-13
days",IIf(Date()-[RequestDate]<="21","14-21
days",IIf(Date()-[RequestDate]<="29","22-29 days","30+ days")))))

I am having some problems with this though. For some records,
everything works fine. But for other records, the formula puts them in
the wrong category. I have one record that was created in October, but
is falling in the 4-7 days category.

Does anyone have any suggestions on what I can do differently to get
this to work right??

 

[John Vinson]

I have a query I use to identify the age of certain records, and then
group them in corresponding age buckets. I am using the following
statement:
Expr1: IIf(Date()-[RequestDate]<="3","1-3
days",IIf(Date()-[RequestDate]<="7","4-7
days",IIf(Date()-[RequestDate]<="13","8-13
days",IIf(Date()-[RequestDate]<="21","14-21
days",IIf(Date()-[RequestDate]<="29","22-29 days","30+ days")))))

I am having some problems with this though. For some records,
everything works fine. But for other records, the formula puts them in
the wrong category. I have one record that was created in October, but
is falling in the 4-7 days category.

Does anyone have any suggestions on what I can do differently to get
this to work right??

Just one suggestion in addition to the others: rather than deeply
nested IIFs, consider the Switch() function. It takes arguments in
pairs; the pairs are evaluated left to right, and the function returns
the second member of the first pair for which the first member is
True:

Expr1: Switch(Date()-[RequestDate]<=3, "1-3 days",
Date()-[RequestDate]<=7,"4-7 days",
Date()-[RequestDate]<=13,"8-13 days",
Date()-[RequestDate]<=21,"14-21 days",
Date()-[RequestDate]<=29,"22-29 days",
True, "30+ days")

John W. Vinson[MVP]