Query to stop showing records after 18:00 (More complex than it sound) 1

[Stevehewitt]

Hi Everyone,

I have the following three criteria in my query:

DateCompleted:
Between ([Forms]![frmReportDateSpecific]![txtStartDate]) And ([Forms]![frmReportDateSpecific]![txtEndDate])
[b]OR[/b]
DateIn:
Between ([Forms]![frmReportDateSpecific]![txtStartDate]) And ([Forms]![frmReportDateSpecific]![txtEndDate])
[b]OR[/b]
DateIn:
[Forms]![frmReportDateSpecific]![txtStartDate]-1 AND TimeIn: >#17:59:59#

(Hope that makes some sort of sense!)

As you can see, the query shows all records that were completed between two dates on a form, it also shows all records that were entered into the system between the two dates, and it shows all records on the previous day of the txtStartDate field after 6:00pm.

My question is how can make it so that the query will not include any records after 6:00pm on the txtEndDate field?

Hope this makes sense, any help appreciated.

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[mp9]

Some psuedo-code to help:

(DateCompleted between dates criteria
OR
DateIn between dates criteria)
AND
DateIn < ([Forms]![frmReportDateSpecific]![txtStartDate]+0.75)

This should work on the basis that a date value without a time default to 00:00 on that date, so adding 0.75 to that date adds 18hrs, hence you're checking for dates before 6pm on the specified date.

All this is untested, and you'll need to be careful with your ANDs and ORs (not the brackets in the pseudo-code).

Hope this helps a little.

http://www22.brinkster.com/accessory/

 

[PHV]

Can you please post the WHERE clause of the generated SQL code ?

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

 

[Stevehewitt]

Thanks guys.

mp9

Something I didn't mention, is that we use two seperate fields for TimeIn and DateIn, so we couldn't use your suggestion - but thank you.

PHV

Hope this helps:

WHERE (((tblInboxInput.DateCompleted) Between ([Forms]![frmReportDateSpecific]![txtStartDate]) And ([Forms]![frmReportDateSpecific]![txtEndDate])) AND ((tblInboxInput.TypeOfLead)="Click")) OR (((tblInboxInput.TypeOfLead)="Click") AND ((tblInboxInput.DateIn) Between ([Forms]![frmReportDateSpecific]![txtStartDate]) And ([Forms]![frmReportDateSpecific]![txtEndDate]))) OR (((tblInboxInput.TypeOfLead)="Click") AND ((tblInboxInput.DateIn)=[Forms]![frmReportDateSpecific]![txtStartDate]-1) AND ((Format([TimeIn],"hh:nn:ss"))>#12/30/1899 17:59:59#));

Cheers again guys,

Steve.

"They have the internet on computers now!" - Homer Simpson

 

[mp9]

Okay, how about simply:

WHERE
(tblInboxInput.DateCompleted >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateCompleted < [Forms]![frmReportDateSpecific]![txtEndDate]
AND tblInboxInput.TypeOfLead)="Click")
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00# 
AND tblInboxInput.TypeOfLead)="Click")

http://www22.brinkster.com/accessory/

 

[Stevehewitt]

Thanks again mp9, appreciate your help.

The problem with that is that we need to see all of the records that were entered in (DateIn) between the variable dates (txtStartDate and txtEndDate) - but the time range (which is in a different field called TimeIn) must be from 6:00pm the previous day of the start date and stop showing records that are after 6:00pm of the end date.

Same applies for the DateCompleted - both completed and in records between the date ranges; including the previous days records from 6:00pm and not counting the records after 6:00pm of the last / end date.

Does this make sense?!
(Talk about management making it hard!)

Cheers guys,

Steve.

"They have the internet on computers now!" - Homer Simpson

 

[mp9]

Okay, it's long and unwieldy but I think this covers all the eventualities you've described:

WHERE
tblInboxInput.TypeOfLead="Click"
AND
((tblInboxInput.DateCompleted >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateCompleted < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#))
AND
((tblInboxInput.DateIn >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateIn < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#))

http://www22.brinkster.com/accessory/

 

[mp9]

Oops, missed one pair of important parentheses in my last post:

WHERE
tblInboxInput.TypeOfLead="Click"
AND
(((tblInboxInput.DateCompleted >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateCompleted < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#))
AND
((tblInboxInput.DateIn >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateIn < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#)))

http://www22.brinkster.com/accessory/

 

[Stevehewitt]

Thanks again mp9 - SQL all seems to work technically, but it's not producing correct results.

It returned one record, but we have about 100 in the table.

Basically we're running a simple query where all records between the two dates for both DateIn and DateCompleted are shown.

The problem is that we want the query to display the previous days records for both DateIn and DateCompleted if the time is after 6:00pm, and we don't want it to include anything that on the last date was time stamped later than 6:00pm.

Thanks again for all of your help!!!

Cheers,

Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Stevehewitt]

Normally Access would handle the query as such:

Enter in: 10/01/2000 ----> 17/01/2000


Shows us: 10/01/2000 ----> 17/01/2000
00.00 ----> 00.00

This was working fine in the original / current query.

However, we need it to show the following:

Enter in: 10/01/2000 ----> 17/01/2000

Shows us:
09/01/2000 ----> 16/01/2000
18.00 ----> 18.00

Hope this makes things a bit clearer! Also, the query needs to run it for DateIn OR DateCompleted.

Thanks,

Steve.

"They have the internet on computers now!" - Homer Simpson

 

[mp9]

Okay, one more try:

WHERE
tblInboxInput.TypeOfLead="Click"
AND
(((tblInboxInput.DateCompleted >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateCompleted < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateCompleted = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#))
OR
((tblInboxInput.DateIn >= [Forms]![frmReportDateSpecific]![txtStartDate] 
AND tblInboxInput.DateIn < [Forms]![frmReportDateSpecific]![txtEndDate])
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtStartDate]-1
AND Format([TimeIn],"hh:nn:ss")>=#12/30/1899 18:00:00#)
OR
(tblInboxInput.DateIn = [Forms]![frmReportDateSpecific]![txtEndDate]
AND Format([TimeIn],"hh:nn:ss")<#12/30/1899 18:00:00#)))

http://www22.brinkster.com/accessory/

 

[Stevehewitt]

Whey! Looks like that could be the one!

Thanks ever so much - seems to be great. I'll need to do some more testing, but seems to be spot on perfect.

Cheers, much appreciated.

Thanks again,




Steve.

"They have the internet on computers now!" - Homer Simpson