Parameters And Null Values

I have a query that I wish to return ALL values between 2 dates AND any
records that the date happens to be null

I have used the following:

WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null))

And the result is ONLY the Null values.

WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] And (Schedule.DateSubmitted) Is Null))

And the result is Nothing...an empty set.

What do I need to do so that it will show me all records between the dates
and any record where the date field is Null?

Thanx.
RHM

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

hi,

RedHeadedMonster via AccessMonster.com wrote:
I have a query that I wish to return ALL values between 2 dates AND any
records that the date happens to be null

I have used the following:

WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null))
This is basically the correct condition. Reformatted:

WHERE IsNull([Schedule].[DateSubmitted])
OR
(
[Schedule].[DateSubmitted]
BETWEEN [Forms]![ViewReports]![DateFrom]
AND [Forms]![ViewReports]![DateTo]
)

And the result is ONLY the Null values.
Check your values in [DateFrom] and [DateTo] carfully. Maybe you need a
cast like:

WHERE IsNull([Schedule].[DateSubmitted])
OR
(
[Schedule].[DateSubmitted]
BETWEEN CDate([Forms]![ViewReports]![DateFrom])
AND CDate([Forms]![ViewReports]![DateTo])
)


mfG
-- stefan --

Thanx! Worked like a charm.
Have a Nice Day!
RHM

Stefan Hoffmann wrote:
hi,

I have a query that I wish to return ALL values between 2 dates AND any
records that the date happens to be null
[quoted text clipped - 3 lines]
WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null))
This is basically the correct condition. Reformatted:

WHERE IsNull([Schedule].[DateSubmitted])
OR
(
[Schedule].[DateSubmitted]
BETWEEN [Forms]![ViewReports]![DateFrom]
AND [Forms]![ViewReports]![DateTo]
)

And the result is ONLY the Null values.
Check your values in [DateFrom] and [DateTo] carfully. Maybe you need a
cast like:

WHERE IsNull([Schedule].[DateSubmitted])
OR
(
[Schedule].[DateSubmitted]
BETWEEN CDate([Forms]![ViewReports]![DateFrom])
AND CDate([Forms]![ViewReports]![DateTo])
)

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1