Query criteria between two dates and two times

[cocowomble]

Hi All,

Apologies if this has already been answered in another thread, but couldn't find anything.

I have a table which list a load of items, one field is date and one field is time.

I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.

This then runs a query for a report to be produced. The problem I having is getting the query to runs both criterias

it returns nothing.

here is the Where part of the current SQL, if it helps.


WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate] And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea] And [Forms]![FRM_SearchMulti]![txtreptimeb]))
ORDER BY tblIncident.IncDate, tblIncident.IncTime;

any ideas, thanks in advance

 

[Uncle Gizmo]

What Data type are the fields:- "one field is date and one field is time"?

 

[cocowomble]

Yes that's correct.

 

[Uncle Gizmo]

What Data type are the fields:- "one field is date and one field is time"?

Misunderstanding.... I was asking what are the data types?

I assume from your answer you mean the data type is Date/Time?

 

[Uncle Gizmo]

The essence of the question is why?

Why one field for Date and one for Time? Where a single Date/Time field would store both the date and the time...

 

[cocowomble]

I did consider this, but I'm a fair way through the database, and a lot of the info/queries now rely on seperate fields.

So is it possible??

Sorry to be a pain.

Thanks

 

[Uncle Gizmo]

I think if you investigate you will find the field you store the time in, will have the same date as the field you store the date in. If that's the case then just use "your" time field and extract both the time and date from it.

 

[cocowomble]

Gizmo.
Sorry to sound thick, but I don't understand what you mean.

Appreciate your help.