We help IT Professionals succeed at work.

Date range access query/report

I have a report based on a query which has 2 date columns '[DATE IN]' & '[DATE OUT]'.

I want to run report and pick all rows which has no entry in [DATE OUT] or [DATE OUT] is between two dates selected from userform.

I have setup variables startdate and enddate to the range of dates selected on userform. Running following produces the rows which has [DATE OUT] within range, and is correct

DoCmd.OpenReport "Movement", acViewReport, , "[DATE OUT] " & "Between #" & startdate & " # AND #" & enddate & "#"

I want to also pick row which doesn't have an entry in [DATE OUT].

I as assuming [DATE OUT] will be NULL ( or "" ?)

How can I modify the OpenReport to include rows with no date in [DATE OUT] field ?
Comment
Watch Question

Author

Commented:
I have tried
 DoCmd.OpenReport "Movement", acViewReport, , "[DATE OUT] " & "Between #" & startdate & " # AND #" & enddate & "#" & " OR ISNULL([DATE OUT])"

But this produced the same results as before, maybe [DATE OUT] isn't NULL when empty.

Commented:
you may have to exclude blanks as well where date out <> ' '
BRONZE EXPERT
Commented:
Good idea to add this conditions to query and test it.
Sometimes date values should be used as Datevalue(startdate) and Datevalue([DATE  JUT])

Author

Commented:
DoCmd.OpenReport "Movement", acViewReport, , "[DATE OUT] " & "Between #" & startdate & " # AND #" & enddate & "# AND [DATE OUT]<> ''"

Produced error, expression is typed incorrectly or too complex to be evaluated

May need to expand a little more as what I am trying ot do has gt me stumped.

The 2 date ranges [DATE IN] & [DATE OUT]. I am trying to run report were [DATE OUT] IS within 2 selected dates, or [DATE OUT] is empty.  
I also need to calculate the number of days it was 'IN' during the selected period.

Selected date range dates 1/10/2011 - 24/10/2011

i.e if [DATE IN] = 6/09/2011  and [DATE OUT]  was emtpy then days IN would be 24 ( 1/10 - 24/10)
if [DATE IN] = 18/10/2011 and [DATE OUT] was empty the days IN would be 7 (18/10 - 24/10)
if [DATE IN] = 27/06/2011 and [DATE OUT] was 19/10/2011 days IN would be 19 (1/10 - 19/10)

I hope all this makes some sense !

Author

Commented:
als315, Don't know what you mean.
BRONZE EXPERT
Commented:
You report has recordsource - it is table or query. Modify it, adding your conditions. You can see result and check your conditions.
If you upload your DB with  form, table with sample data and query, may be we can help more effectively

Explore More ContentExplore courses, solutions, and other research materials related to this topic.