Date/Time criteria for a Report

[slenish]

Hello,

Im having some trouble trying to figure out how to make a report run based on a date and time range.

What I have is a Form with 4 text boxes and a print report button. What I want to do is enter in a date and time range and then run a report based on that information. So I have Two text boxes that are start date and end date and two that are start time and end time. I have a query Criteria set to look at the form and the boxes problem is that I can not get it to look at all 4 boxes its only 2 or 2 any way i can get it to look at all 4?

Appreciate the help on this.

Here is the code I have so far in the query criteria

Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![EndDate]'this works

'have tried variations but no luck so far

Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![StartTime]And [Forms]![DateReport]![EndDate]And[Forms]![DateReport]![EndTime]
 
Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![StartTime]And Between [Forms]![DateReport]![EndDate]And[Forms]![DateReport]![EndTime]

[liimra]

//

There is a problem in the two variations you stated. You are trying to get information between timefield and datefield!

This how it should be logically speaking

Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![EndDate]And Between [Forms]![DateReport]![StartTime]And[Forms]![DateReport]![EndTime]

Regards,
Ali

[slenish]

Hi liimra,

Thanks for the reply. Well your suggestion half worked. When I input the information on the form and run the report it pulls the report with no problem now but for some reason the report always comes back with no information.

Any ideas as to why? I have tried a lot of ideas such as adding code in to the date field on the report, I tried reformating the column in the table to date/time, just date, military time and still nothing. Cant figure out what the problem is.

Thanks again for the help.

[colintis]

how about setting clear the date format from vba code? such as

Dim StartDate As Date
Dim EndDate As Date
StartDate = Date([Forms]![DateReport]![StartDate], "mm/dd/yyyy")
EndDate = Date([Forms]![DateReport]![EndDate], "mm/dd/yyyy")

....Between #" & StartDate & "# AND #" & EndDate & "#"

And the time

Dim StartTime as Date
Dim EndTime as Date
StartTime = TimeValue([Forms]![DateReport]![StartTime])
EndTime = TimeValue([Forms]![DateReport]![EndTime])

....Between '" & StartTime & "' AND '" & EndTime & "'"

Notice TimeValue will convert the time to format (HH:MM:SS AM/PM), so for example:

TimeValue ("3:12:57 PM")would return 3:12:57 PM
TimeValue ("15:12:57")	would return 3:12:57 PM

[liimra]

Solution/

The problem is not with the format or context but with the filters & scenarios.

This would happen if you are leaving one of the two criteria null. Usually when you have more than one criteria you have to assume different scenarios. For example If date is selected then filter based on date or If date & time are selected then filter based on them.

In order to overcome this, you have to define the other scenarios you might have. In your example they are three: The fitler can be date, time or both. Since you only have two time/date filters you can use simple approach:

 If [DateOne] Is Not Null And [DateTwo] Is Not Null and [TimeOne] Is Not Null And [TimeTwo] Is Not Null Then
DoCmd.OpenReport .... Criteria = Time & Date
ElseIF [DateOne] Is Not Null And [DateTwo] Is Not Null and [TimeOne] Is Null And [TimeTwo] Is Null Then
DoCmd.OpenReport .... Criteria = Only Date

....and so on 
Else
MsgBox "Choose Criteria"
End If

Please find this approach in the attached database. Note that you have to select "from & to" Date or "from & to" Time or Both, otherwise if you choose just "From" date only you will have MsgBox "Choose Criteria".



Regards,
Ali