Pull data based on Variable

[mot98]

Hi All,

I have a query that is pulling production data from a database.

I want to pull current shift information, so basically I need to have a variable that would tell the query what shift it is based on what the current time is.

I was thinking of a something like the following:

If Time() is Between 23:00 and 07:00 then CShift = 1
ElseIf
Time() is Between 07:00 and 15:00 then CShift = 2
ElseIf
Time() is Between 15:00 and 23:00 then CShift = 3

Then my query would use CShift to pull the current data.

Any ideas on how to really code this?


mot98

"Is it friday yet?"

 

[jadams0173]

I do it like this:

Select Case format(Time(),'hh:mm')
  case "23:00" to "07:00"
     'do something....
  case "07:01" to "15:00"
     'do somthing
end Select

HTH

I tried to have patience but it took to long! -DW

 

[mot98]

Okay...that code looks good and makes sense.

But how do I define the variable in the query like this?



mot98

"Is it friday yet?"

 

[gmmastros]

What database are you using? SQL Server, Access, other...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mot98]

Sorry....it is an Access database.

mot98

"Is it friday yet?"

 

[mot98]

Okay..so I am have a field in my query called Shift..and under the Criteria I am building a function like this to get the current shift:

Select Case format(Time(),'hh:mm')
case "23:00" to "07:00"
1
case "07:00" to "15:00" 
2
Case "15:00" to "23:00"
3

However, I am getting an error saying "Check the Subquery's syntax. Enclose the Subquery in Parentheses.


mot98

"Is it friday yet?"

 

[Golom]

Doing it in Code ...

Select Case Format(Time(),[red]"[/red]hh:[red]nn"[/red])
   Case "07:00" to "[red]14:59[/red]" 
      [red]CShift =[/red] 2
   Case "15:00" to "[red]22:59[/red]"
      [red]CShift =[/red] 3
   Case [red]Else[/red]
      [red]CShift =[/red] 1
End Select

However, if you're doing this in SQL then note that Access SQL does not support a Case Statement.

In SQL you would need

Select IIF(Hour([DateTimeField]) <  7, 1, 
       IIF(Hour([DateTimeField]) < 15, 2, 
       IIF(Hour([DateTimeField]) < 23, 3, 1))) As CShift

 

[jadams0173]

I use what I posted as a function in a module. Then from my query in access (or VBA) i just call it from the query. One small edit is needed.

Function SHIFTCHECKIN(CHECKTIME As Date) As String
    
Select Case CHECKTIME
    Case "7:00:00 AM" To "3:30:00 PM"
        SHIFTCHECKIN = "1ST"
    Case "3:30:01 PM" To "11:59:59 PM"
        SHIFTCHECKIN = "2ND"
    Case "12:00:00 AM" To "6:59:59 AM"
        SHIFTCHECKIN = "3RD"

End Select


End Function

And then from a query it would be used like:

SHIFT:SHIFTCHECKIN([TIME])

I tried to have patience but it took to long! -DW

 

[mot98]

Okay...I finally got it working with the Switch command and the following code:

Switch(Time()<#7:00:00 AM# Or Time()>=#11:00:00 PM#,3,Time()>=#7:00:00 AM# or Time()<#3:00:00 PM,2,True,3)

Thanks for all the tips though..

mot98

"Is it friday yet?"