If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Parameters And Null Values
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 -- |
#3
|
|||
|
|||
Parameters And Null Values
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 |
Thread Tools | |
Display Modes | |
|
|