Search Query

[grogmi1]

Hi,

I have a search query on my access database which uses a form to set it's search criteria. The form asks for Start Date, Start time, End date, End time. If the user specifies 1 day and a set time criteria the search works fine. If the user specifies a date range we then have problems !!

If the user states eg 01-03-20016 06:00 to 06-03-2016 17:00 the search query falls down. It shows each date but shows only between 06:00 and 17:00 on each day instead of going all the way through from 06:00 on 01-03-2016 to 17:00 on the 06-03-2016.

Any ideas how I can get the search function to go all the way through?

Below are the search details I use on my query:

Search Date:

Between [Forms]![reports_menu_form]![startdate] And [Forms]![reports_menu_form]![enddate]

Search Time:

Between [Forms]![reports_menu_form]![starttime] And [Forms]![reports_menu_form]![endtime]

Any help in laymans terms would be much appreciated.

Thanks in advance

 

[jdraw]

Please post the SQL view of the query you are using?

Am I correct that you can enter a startdate and time And an EndDate and time to search for records between these?

 

[grogmi1]

Here is the sql view

SELECT Agent_Activity.Extension, Agent_Activity.User, Agent_Activity.Date, Agent_Activity.Time, Agent_Activity.[ACTUAL Duration], Agent_Activity.[Status ID], Agent_Activity.[Reason Code], Agent_Activity.[INBOUND CALL], Agent_Activity.[OUTBOUND CALL], IIf([Reason Code]="Lunch Break",[ACTUAL Duration],0) AS Lunch, IIf([Reason Code]="Break",[ACTUAL Duration],0) AS Break, IIf([Reason Code]="Personal",[ACTUAL Duration],0) AS Personal, IIf([Reason Code]="Query",[ACTUAL Duration],0) AS Query, IIf([Reason Code]="Meeting",[ACTUAL Duration],0) AS Meeting, IIf([INBOUND CALL]>0,[ACTUAL Duration],0) AS CallDuration
FROM Agent_Activity
WHERE (((Agent_Activity.Extension)=[Forms]![reports_menu_form]![adv_name]) AND ((Agent_Activity.Date) Between [Forms]![reports_menu_form]![startdate] And [Forms]![reports_menu_form]![enddate]) AND ((Agent_Activity.Time) Between [Forms]![reports_menu_form]![starttime] And [Forms]![reports_menu_form]![endtime]));

Yes, you can enter the start date, then the start time and then end date and end time all in separate fields