Date Range entry on form for a query

[torf66]

I have a query that runs from a form. In the form I have the option for a user to enter a From-Thru date to print to a report based on the From-Thru dates they entered on the form. My form is called frmSelectResolutionRpt my fields on the form are RevDenDate and RevDenDateThru. WHen I use this in the criteria portion of my query I get mixed results. If one of my records does not have a date entered in the field in my table when user enters a date range then they get those dates there entered but also any records that
have a date missing. If the user enters a specific range then I only want records with dates that fall in between what they have entered, not records with no date as well.
How can I set my criteria in my query to get the results I need. Also if they do not enter any date in the From-Thru selections on the form then I want all records to display.

(>=[forms]![frmSelectResolutionRpt]![RevDenDate] Or Like [forms]![frmSelectResolutionRpt]![RevDenDate] & "*" Or Is Null) And (<=[forms]![frmSelectResolutionRpt]![RevDenDateThru] Or Like [forms]![frmSelectResolutionRpt]![RevDenDateThru] & "*" Or Is Null)

 

[Trendsetter]

Try using:
Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru] Or IsNull

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[torf66]

I put this in like this:
Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru] Or Is Null

When I enter a from thru date say 01/01/04 and 01/15/04 I get all the records in the date range but then I also get the records that do not have a date in this field. I only want the records that have a date. If I leave the from-thru dates blank then I get only the record that does not have a date.

If I enter 01/01/04 to 01/15/04 then I only want records that have a date populated in the field between these dates.

If I do not enter anything in the from-thru dates range then I want all records to show.

How can I get this to work this way??

 

[Trendsetter]

on the end of the between .. and .. statement you need to add AND Not Is Null
This will ensure that only records with a date in will show.

On the next criteria line in the Query builder add Is Null

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[torf66]

I have this on line 1 of the criteria
Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru] And Is Not Null

then on the next criteria line I have
Is Null this is on the or: line.

Now if I do not enter a specific date range I get that only the records that do not have a date entered. I want all records if the date range is left blank.

If I enter a specific date range 01/01/2004 to 01/15/2004
I get records in this range and the record with a blank date.

This still does not get me what I need. If from-thru blank all records should show. If a date range entered then just those records in the range should show. Not the ones with blank dates with the desired date range is entered.

 

[Trendsetter]

The expression at the end of the between string is
Not Is Null

not

Is Not Null


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[torf66]

This is what I have entered. Is this the correct format?
I have nothing on the next criteria line down the or:

With this below if I leave the from-thru dates blank then no records display, all records should display.

If I put a from-thru date in then I get just those records that have dates the match the from-thru so this is working.

But when I enter no dates in I should get everything since I did not enter a specific range. How can I accomplish
that as well??

Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru] And Not Is Null

 

[Trendsetter]

I have just played about with a similar invented query and found that if you enter another column with the target date field - the date field you wish to filter. you can enter criteria into the blank row of this second field that will work with the criteria of the first field.
I entered a search criteria of between ... and in the first criteria row of the first date column and then..
Is Null in the second criteria row of the second date field.

You need to play around to get the required result - this is how we all learn.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[PHV]

In SQL view:
WHERE ... AND (([Date field] Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru]) Or IsNull( [forms]![frmSelectResolutionRpt]![RevDenDate]) Or IsNull([forms]![frmSelectResolutionRpt]![RevDenDateThru])) ...



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[torf66]

Nothing seems to be working. I tried putting in another field for the same date and in the second criteria row put in Is Null. With this below in the first criteria row for the first date and Is Null when I run the query with nothing in the date range I get only the record with nothing in the date field. I should get all records since I did not specify a date range. I have tried everything and cannot get this to work. PHV how do you get to the SQL view and what should I put in there exactly? Your reply is somewhat vague not knowing how to get to the SQL View and then your reply is WHERE....AND is vague. I need more info to use your suggestion. If anyone can help that would be great why does this have to be so difficult.

Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru] And Not Is Null

 

[torf66]

I have this in my SQL View(I found it by the way) but I get an error join expression not supported any ideas?

SELECT tblResolutions.[Resolution Desc], tblReviewerNames.[Reviewer Name], tblRequestor.Requestor, tblTrackingRequestorNew.[Review/Denial Date], tblTrackingRequestorNew.[Date Completed], tblTrackingRequestorNew.[Admit Number]
FROM tbladmissioninfo INNER JOIN ((tblReviewerNames INNER JOIN (tblResolutions INNER JOIN tblTrackingRequestorNew ON tblResolutions.[Resolution Number] = tblTrackingRequestorNew.[Resolution Number]) ON tblReviewerNames.[Reviewer Num] = tblTrackingRequestorNew.[Reviewer Num]) INNER JOIN tblRequestor ON tblTrackingRequestorNew.[Requestor Num] = tblRequestor.[Requestor Num]) ON tbladmissioninfo.[Admit Number] = tblTrackingRequestorNew.[Admit Number]AND (([Date field] Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru]) Or IsNull( [forms]![frmSelectResolutionRpt]![RevDenDate]) Or IsNull([forms]![frmSelectResolutionRpt]![RevDenDateThru]))
ORDER BY tblResolutions.[Resolution Desc], tblReviewerNames.[Reviewer Name], tblRequestor.Requestor, tblTrackingRequestorNew.[Review/Denial Date];

 

[torf66]

([Date field] Between [forms]![frmSelectResolutionRpt]![RevDenDate] And [forms]![frmSelectResolutionRpt]![RevDenDateThru]) Or IsNull( [forms]![frmSelectResolutionRpt]![RevDenDate]) Or IsNull([forms]![frmSelectResolutionRpt]![RevDenDateThru])

I get an error that states Join Expression not supported.

 

[PHV]

I think you're very close:
Replace this:
AND (([Date field] Between
By this
WHERE (([Date field] Between

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244