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]

 

[Jamie Collins]

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.

Rather than hard-code the logic into your design (with which you are
having problems), consider putting the data into a table and using a
join e.g.

CREATE TABLE DatePartitions (
start INTEGER NOT NULL UNIQUE,
stop INTEGER UNIQUE,
caption VARCHAR(12) NOT NULL
)
;
INSERT INTO DatePartitions (start, stop, caption)
VALUES (1, 3, '1-3')
;
INSERT INTO DatePartitions (start, stop, caption)
VALUES (4, 7, '4-7')
;
INSERT INTO DatePartitions (start, stop, caption)
VALUES (8, 13, '8-13')
;
etc

SELECT C1.dt, P1.caption
FROM Calendar AS C1, DatePartitions AS P1
WHERE DATEDIFF('D', C1.dt, DATE())
BETWEEN P1.start AND IIF(P1.stop IS NULL, 2.1E9, P1.stop)
;

Jamie.

--