Search Between Date and Time

[Kallu]

Hi there.
Can someone help me solving problem Searching on query Between Date and Time
I want to prepare report:
1. Report between: StartDate 11/02/13, StartTime 20:00 and EndDate 12/02/13, EndTime 08:00.
2. Report between: StartDate 12/02/13, StartTime 08:00 and EndDate 12/02/13, EndTime 20:00.
Thanks in any help.

 

[JHB]

Do you have date and time in 2 fields?
If yes, then collect them in 1 field and use between in your query.

 

[Kallu]

I tried those method not working, i tried this:
Collected fields: Expr1: [Date] & " " & [Time]
Criteria: Between ([Forms]![myForm]![srqStartDate] & " " & [Forms]![myForm]![srqStartTime]) And ([Forms]![myForm]![srqEndData] & " " & [Forms]![myForm]![srqEndTime]).

 

[bbagini]

Could you use two criterias, for example
On your date field
Between ([Forms]![myForm]![srqStartDate] & [Forms]![myForm]![srqEndData])

and the criteria for the time field
Between ([Forms]![myForm]![srqStartTime] & [Forms]![myForm]![srqEndTime])

 

[JHB]

I tried those method not working, i tried this:
Collected fields: Expr1: [Date] & " " & [Time]
Criteria: Between ([Forms]![myForm]![srqStartDate] & " " & [Forms]![myForm]![srqStartTime]) And ([Forms]![myForm]![srqEndData] & " " & [Forms]![myForm]![srqEndTime]).

I'm not sure it is converted to a datetime the way you do it, so try:

Collected fields: Expr1: DateValue([Date] & " " & [Time])
Criteria: Between DateValue([Forms]![myForm]![srqStartDate] & " " & [Forms]![myForm]![srqStartTime]) And DateValue([Forms]![myForm]![srqEndData] & " " & [Forms]![myForm]![srqEndTime]).

And again - it's a bad idea to use MS Access reserved words as field names, (Date and Time is such words).
You can find a list over these words here: http://support.microsoft.com/kb/286335

 

[Kallu]

Does not work. Query filters only dates not the time.
You can try attached example.

 

[JHB]

The required form in the query is not in the database.

 

[jdraw]

This combination of separate Date and Time fields does work to create a Long Date
In the immediate window
?CDate(Date & " " & Time) =Now
True

But why break these fields out ? Why not use Now for current Date and Time in single field

 

[spikepl]

?date()+time()=now()
True

 

[Kallu]

Hi there.
I find the solutions according to jdraw idea, i put the vba code on main form to combine date and time on single field.
Query:
Field: DateTime
Criteria: Between ([Forms]![myForm]![srqStartDate] & " " & [Forms]![myForm]![srqStartTime]) And ([Forms]![myForm]![srqEndData] & " " & [Forms]![myForm]![srqEndTime])
Thanks to all.