Begin/End Date Parameter 2

[ineedyourhelp]

I have created a form with start and end date parameters with the following code (see below)....(Taken from an Allen Browne article "Limiting a Report To a Date Range" in a previous post)

The function operates as it should, but I was wondering if there was a way to tweak it so it would...

1. Show me all dates within a date range instead of between a date range.
Example:
If I enter a Start Date 7-1-2004 and an end date of 7-14-04, I would like all records returned within those ranges (7-1-04 through 7-14-04) With the below, it only returns what is between these two dates (7-2-04 through 7-13-04 data is returned)

2. Return all data if Start and End Date fields are left blank

3. Return 1 day's worth of data if same day is used in Start and End Date.
Example: If I want only 7-14-04 data, I would enter a Start Date as 7-14-04 and an End Date as 7-14-04. Right now, if I enter it that way, it returns nothing.


Any help would be appreciated.

SEE CODE BELOW.................


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Completed Issues"
strField = "[Date/Time Completed]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If

 

[PHV]

Have you tried something like this ?
strWhere = strField & " Between " & Iif(IsNull(txtStartDate), strField, Format(txtStartDate, conDateFormat)) _
& " And " & Iif(IsNull(txtEndDate), strField, Format(txtEndDate, conDateFormat))

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

 

[ineedyourhelp]

PHV,
Just tried your code, but I am still having the same problems with all 3 of my requests.

Thanks

 

[ineedyourhelp]

Actually, My # 2 request now works. When I leave both fields blank, all data is returned.

I am still having issues with my #1 and #3 requests.

Thanks

 

[scriverb]

Look at how your date values are being inserted into your database table records. If you use the Now() function as a default value for a date field or use code to insert Now() you are inserting a full date/time value. This means that you are getting the Time also. This has an effect that you are describing here. You see if you use just a date value as the Ending date of the range the value here is your date plus 00:00 am. So , any of your records with the same date with any time value due to the Now() function will be excluded.

post back if this may be the problem.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ineedyourhelp]

Thanks Bob,
I am indeed using the Now() function for my date values. Is there any way around this without disabling Now() (It is very impt. that I keep it this way)

Could I somehow change the below to pick up date and time?

Const conDateFormat = "\#mm\/dd\/yyyy\#

 

[PHV]

And this ?
strWhere = strField & " Between " & Iif(IsNull(txtStartDate), strField, Format(CLng(txtStartDate), conDateFormat)) _
& " And " & Iif(IsNull(txtEndDate), strField, Format(CLng(txtEndDate)+1, conDateFormat))

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

 

[ineedyourhelp]

PHV,
Looks like you're on the right track. Now my #1 and #3 requests work, but now my #2 request no longer works.

If I leave both fields blank, I get

run time error 94
Invalid use of null

 

[scriverb]

Just change your insert of the date from Now() to Date(). Now the date field only inludes the Date with no time designation. Actually, it includes a time but it always 00:00 am so that just using the Between clause with two ranged dates will work all the time.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ineedyourhelp]

In addition to my #2 request not working, I have also faced another problem.

Using the original code, I was able to leave one field blank as long as one was filled. If I had a Start Date of 7-1-04 and a blank End Date, it would return dates >= 7-14-04. If I had End Date as 7-14-04 and Start Date blank, it would return dates <= 7-14-04.

If I try that now, I get the same run time error as mentioned in my previous post. Is there a way to set a condition so that a message box will come up to alert the user that the submission is invalid?

 

[ineedyourhelp]

Bob,
You said to change your insert of the date from Now() to Date(). Not sure what you mean by the insert of the date? Do you mean change the data types in the Master Table? If I do that, all of the current data in that field will be ruined, won't it?

 

[scriverb]

No that is not what I mean. Sometimes on a form the programmer has set the Default Value of a date/time field to Now() so that the user doesn't have to actually enter the date. They can enter the date and overwrite what has been entered with a different date. But, if the Now() value is left in place for this new record then you have both Date and Time stored in the field. This is problematic as I stated before because when attempting to evaluate Between and <= expressions there are records that are left out.

In addition to using the Now() function as the default value for dates sometimes it is programmed into the form to insert a record using the Now() function. This is also a problem as stated before.

I would suggest putting in a Break or Stop just after the building the strWhere variable and examinging what actually has been created given the different entry possibilities. This should help you to debug the problem. When the code stops type in ?strWhere in the immediate window and press Enter. This will display the actual string SQL that has been created by your code. This should help you understand what things are not working as expected.

I hope this helps you with the problem As for the problems with the statement being suggested by PHV, I don't see anything wrong with it. If a value is left blank then the code places the value of that particular record in the expression. You see the IIF functions in his code are evaluating for leaving the value set to null or not entering the parameter. In either case if it is null then the comparison between is made to itself which should work at all times.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]