Parameters And Null Values

[RedHeadedMonster via AccessMonster.com]

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

 

[Stefan Hoffmann]

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

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 <--

 

[RedHeadedMonster via AccessMonster.com]

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

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 <--