Query Problem Between Two Times

[brett429]

I'm trying to set up a query that shows whether or not a retailer is Open or Closed depending on the time input (TimeSelection). See below:

Status: IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]>[Forms]![frmMain]![TimeSelection],"Open","Closed")

This works just fine UNLESS the OpenTime and CloseTime are both AM. For instance, one retailer is open from 11am-2am. This displays "Closed," even if the TimeSelection is between the two (3pm, for instance).

What can I do to fix this? I tried using Short Time and using a "Between" statement, but neither worked. Help!!!

 

[HiTechCoach]

Try:

Status: IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] OR [CloseTime]>=[Forms]![frmMain]![TimeSelection],"Open","Closed")

 

[brett429]

Try:

Status: IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] OR [CloseTime]>=[Forms]![frmMain]![TimeSelection],"Open","Closed")

That makes everything show up as "Open"

 

[WIS]

Try:

Status: IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] OR [CloseTime]>=[Forms]![frmMain]![TimeSelection],"Open","Closed")

How are Open and Close time stored. For your example 11am - 2am means 11hrs to 26 (24+2) and 3pm = 15. If Close time< Open time then Close time = close time + 24. I would move to a 24 selection, keeping the above in mind.

 

[brett429]

How are Open and Close time stored. For your example 11am - 2am means 11hrs to 26 (24+2) and 3pm = 15. If Close time< Open time then Close time = close time + 24. I would move to a 24 selection, keeping the above in mind.

I tried 24 hour time, but that didn't work, unless I did something wrong. That would just involve going through and changing all the time formats to "Short Time," right?

Here's an example of the values:

Vendor A, OpenTime: 7:00 AM, CloseTime: 11:00 PM, TimeSelection: 1:03 PM - Displays OPEN (correct, because the current time of 1:03 PM falls between the open and close times of that vendor).

Vendor B, OpenTime: 11:00 AM, CloseTime: 2:00 AM, TimeSelection: 1:03 PM - Displays CLOSED (incorrect, because the current time of 1:03 PM falls between the open and close times of that vendor, so it should display OPEN.)

 

[raskew]

Hi -

Boyd's solution (Post #2) works just fine if you are using date/time data types. Examples:

StartTime = #7:00 AM#
CloseTime = #11:00 PM#
InputTime = #1:03 PM#
? IIf(StartTime<=InputTime OR CloseTime>=InputTime,"Open","Closed")
[B]Open[/B]

StartTime = #11:00 AM#
CloseTime = #2:00 AM#
InputTime = #1:03 PM#
? IIf(StartTime<=InputTime OR CloseTime>=InputTime,"Open","Closed")
[B]Open[/B]

HTH - Bob

 

[brett429]

Is there an alternate way I can achieve what I'm trying to do, because I swear up and down that I've tried the suggestions in this topic 100 times and it does not work.

 

[DCrake]

The main issue here is the fact that the times may cross midnight so effectively 11am comes after 2am using a 12 hour clock. What you need to do if first convert the times to minutes. in this example

11am = 11*60 = 660
2am = 2*60 = 120

Then you need to question whether the closed value is less than the open value and if it is then add 1440 to the close time. Next convert the test time to minutes and do the comparison here.

David

 

[just.a.guy]

DCrake is correct - you need to compare the start and end time.
You can use nested IIF statement

Status: IIF([OpenTime] <= [CloseTime]
,IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]>[Forms]![frmMain]![TimeSelection],"Open","Closed"),
,IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]+1 >[Forms]![frmMain]![TimeSelection],"Open","Closed")
)

I am assuming that the date fields are in "days" format.
I believe there is a date function that you can use to add one
day to the ending time.

or you can change the "else" case to test for the closed time as follows

IIf([CloseTime]<=[Forms]![frmMain]![TimeSelection] And [OpenTime]>[Forms]![frmMain]![TimeSelection],"Closed","Open")

I did not test this. So I hope this helps.

 

[brett429]

DCrake is correct - you need to compare the start and end time.
You can use nested IIF statement

Status: IIF([OpenTime] <= [CloseTime]
,IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]>[Forms]![frmMain]![TimeSelection],"Open","Closed"),
,IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]+1 >[Forms]![frmMain]![TimeSelection],"Open","Closed")
)

I am assuming that the date fields are in "days" format.
I believe there is a date function that you can use to add one
day to the ending time.

or you can change the "else" case to test for the closed time as follows

IIf([CloseTime]<=[Forms]![frmMain]![TimeSelection] And [OpenTime]>[Forms]![frmMain]![TimeSelection],"Closed","Open")

I did not test this. So I hope this helps.

Your first bit of code works perfectly except for the one vendor that stays open until 2AM. When I use a selection time of 1AM, it shows that vendor as closed. I think it may just be easiest for me to upload a copy of this DB so you can see what I'm talking about. I'm sure it's something I'm doing wrong...

The database will default the day and time fields to whatever your system shows, and since everyone here is probably in different time zones, I'd suggest changing it for the following examples and clicking the GO button:

Saturday, 8AM: only two vendors show as open, which is correct.
Saturday, 2PM: everything shows as open, which is correct.
Saturday, 11PM: only one vendor shows as open, which is correct.

And here comes the problem child:

Saturday, 1AM: no vendors show open, which is incorrect. Taco Mac stays open until 2AM, and should be shown as open.

Check out the table, tblMerchants... it could also have something to do with the way I'm storing the data.

Any help would be appreciated!!!

 

[brett429]

Any takers?

 

[just.a.guy]

Well I assume for TACO MAC, that open time is something like 8:00 AM
and the close time is 2:00 AM.

If the logic works correctly, then the test

Iif( close <= open gives 8:00 AM <= 2:00 AM

This is false
therefore the second "false" IIF is tested next

iif( open <= form.time and close + 1 > form.time, "closed", "open" )

gives
iif( 8:00 AM <= 1:00 AM and 2:00 AM + 1 day > 1:00 AM, "closed" , "OPEN")

since 8:00 AM <= 1:00 AM is false, therefore the "OPEN" should be choosen.


If this is not working then what are the possible source of error?

1) The "date" portion of the time fields is getting in the way.
One way to get around this is to use the "Hour" and/or "Minute"
functions. You only need to use the "Minute" function if the
businesses open on a time other than the hour.

Use Access Help to read up on these two functions.

2) the direction of the comparison or the "THEN" "ELSE"
clauses may be coded incorretly.

To better see what is going on you might display the full date/time
values of each column. Use the "format" function with a "format"
similar to "YYYY/MM/DD HH24:MI:SS" (this is Oracle's format string
I have not had to use access's format command).

Hope this helps.

 

[just.a.guy]

by the way, if you use the "hour" command you have to change the " + 1"
to " + 24"

 

[just.a.guy]

thinking it over since hour returns 0 - 23, then you don't need to add include "+1" or "+24"

 

[brett429]

Woohoo! Finally got it to work:

Status: IIf([CloseTime]<=[OpenTime],IIf([OpenTime]>=[Forms]![frmMain]![TimeSelection] And [CloseTime]<=[Forms]![frmMain]![TimeSelection],"Closed","Open"),IIf([OpenTime]<=[Forms]![frmMain]![TimeSelection] And [CloseTime]>[Forms]![frmMain]![TimeSelection],"Open","Closed"))

Thanks to all for the assistance!