Filter criteria for a conditional serialdate field in a query

[TB]

I want to query a DateSerial field called TestsDue (syntax shown below) as
follows: >=Date()-7 and <=Date()+30 (i.e., all records that are "due" by
today minus 7 days and due today plus 30 days).

TestsDue:
IIf(IsNull([Frequency]),"",DateSerial(Year([TestDate]),Month([TestDate])+[Frequency],Day([TestDate])))

 

[John Spencer]

You don't say what problem you are having, but I think you will need to change
your expression to

TestsDue:
IIf(IsNull([Frequency]),NULL,DateSerial(Year([TestDate]),Month([TestDate])+[Frequency],Day([TestDate])))

When you can return an zero-length string "" as the result of the expression,
then all the returned values will be converted to a string - so all your dates
are strings. If you use NULL then Tests due will return null or a date (not a
string).



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[TB]

Thank you, John. You were right about the zero-length string causing my
trouble. All is good now.
--
TB

You don't say what problem you are having, but I think you will need to change
your expression to

TestsDue:
IIf(IsNull([Frequency]),NULL,DateSerial(Year([TestDate]),Month([TestDate])+[Frequency],Day([TestDate])))

When you can return an zero-length string "" as the result of the expression,
then all the returned values will be converted to a string - so all your dates
are strings. If you use NULL then Tests due will return null or a date (not a
string).



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I want to query a DateSerial field called TestsDue (syntax shown below) as
follows: >=Date()-7 and <=Date()+30 (i.e., all records that are "due" by
today minus 7 days and due today plus 30 days).

TestsDue:
IIf(IsNull([Frequency]),"",DateSerial(Year([TestDate]),Month([TestDate])+[Frequency],Day([TestDate])))