30 day old

[lmossolle]

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist.

Thanks

 

[Duane Hookom]

Read your question and try to answer it imagining that you don't know what
"30 days old" means or a table name. Is the age determined by datereceived?

 

[lmossolle]

The age is determined by the daterecieved. Table name is SCD, sorry for the
confusion. I was curious if the datediff would work!

 

[Duane Hookom]

"I was curious if the datediff would work!"
You don't need our permission to try. Come on back if you can't get this to
work for you.

 

[lmossolle]

I tried this, it did not work...

Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

 

[Douglas J. Steele]

Not overly surprising that that didn't work.

IIf(daterecieved < Date() - 30 AND completed Is Null)

is invalid. An IIf function is defined as IIf(expr, truepart, falsepart)

You've got the expr part (the expression you want to evaluate), but you
don't have the truepart (the value you want returned if expr is True), not
the falsepart (the value you want returned if expr is False)

 

[Evi]

It looks like you want to count in your report how many records have a
DateReceived which is less than Date()-30 and the Completed field is null

Use

=-Sum(DateRecieved <(Date()-30) AND IsNull(Completed))


(there is a - before the Sum)

If a statement is true, it has a value of -1, otherwise it has a value of 0

Evi