Shift Formula??

A fellow programmer was helping me out on a formula to determine whether or not the person was entering the correct time for their shift. The problem is the time is not being looked at right(I think). Even when I put the right shift time in the fields I still get the message box "Time is wrong". The fields I'm dealing with are txtStartTime(ex: 14:00) and txtEndTime(ex:15:00), I'm using military time. Heres the code:

Enum eShift
esDay = 1
esSwing = 2
esGrave = 3
End Enum

Const dDayStart As Date = #6:30:00 AM#
Const dDayStop As Date = #3:00:00 PM#
Const dSwingStart As Date = #2:00:00 PM#
Const dSwingStop As Date = #10:30:00 PM#
Const dGraveStart As Date = #10:00:00 PM#
Const dGraveStop As Date = #6:30:00 AM#
Dim bSuccess As Boolean
Dim lShift As eShift

'get only the time from the current date/time value (now)
dTime = Format(Now(), "HH:mm")
Select Case lShift
Case esDay
' verify between times
bSuccess = (dTime >= dDayStart And dTime <= dDayStop)
Case esSwing
' verify between times
bSuccess = (dTime >= dSwingStart And dTime <= dSwingStop)
Case esGrave
' verify between times (operates differently because
' it bridges midnight)
bSuccess = (dTime >= dGraveStart Or dTime <= dGraveStop)
End Select
' check the return value
If Not bSuccess Then
MsgBox "Shift time is wrong!"
Exit Sub
txtStartTime.SetFocus
Else
Call AddRecord
End If

If anyone has any suggestions on what to do from here, they would be appreciated.

jeffro

Surely you haven't initialised IShift therefore bSuccess never gets set either?

IShift is simply declared but not set or does that default to esDay? Would be problematic if the time is not actually esDay...

Cheers,

P.

ok I did make a change:

If bSuccess = True Then
Call AddRecord
Else
MsgBox "Shift time is wrong!"
Exit Sub
txtStartTime.SetFocus
End If

I put the =true, but know I don't even get the msgbox when the time is wrong. Can you give me an example of the set?

jeffro

To your first code:
There are at least two problems with your code: There isn¡¯t any relation between your eShift and your Constants, and the Exit Sub has forever expelled the txtStartTime.SetFocus statement from its work. The first problem leads to the ever appearance of the MsgBox. An improved code may be like this:

Code:
---------------------------------------------------------------
Const dDayStart As Date = #6:30:00 AM#
Const dDayStop As Date = #3:00:00 PM#
Const dSwingStart As Date = #2:00:00 PM#
Const dSwingStop As Date = #10:30:00 PM#
Const dGraveStart As Date = #10:00:00 PM#
Const dGraveStop As Date = #6:30:00 AM#
Dim intShift As Integer

dTime = Format(Now(), "HH:mm")

If dTime >= dDayStart And dTime <= dDayStop Then
intShift = 1
ElseIf dTime >= dSwingStart And dTime <= dSwingStop Then
intShift = 2
ElseIf dTime >= dGraveStart Or dTime <= dGraveStop Then
intShift = 3
Else
intShift = 0
End If

If Not intShift Then
MsgBox "Shift time is wrong!"
txtStartTime.SetFocus
Else
Call AddRecord
End If
-----------------------------------------------
Even so, there are still problems with my code: This time you¡¯ll never get the MsgBox to show up, for your Constants have covered the whole day, and dTime is always getting in the right time from the statement. If you want to check the format of an entered time, you have to use a code in, say, the Validate or LostFocus event of a textbox. My code only discriminates among the three shifts.

Sorry xmin, I tried the code you gave me and I'm getting the same results. I'm looking into the constants in relation to eShift.

jeffro

Jeffro,

I am not sure if I totally understand what you need, but if you are having the time keyed into a textbox and then setting the shift, this revised code from xmin could be used for that purpose.

Code:

---

Option Explicit
    Enum eShift
    esDay = 1
    esSwing = 2
    esGrave = 3
    End Enum
   
    Const dDayStart As Date = #6:30:00 AM#
    Const dDayStop As Date = #3:00:00 PM#
    Const dSwingStart As Date = #2:00:00 PM#
    Const dSwingStop As Date = #10:30:00 PM#
    Const dGraveStart As Date = #10:00:00 PM#
    Const dGraveStop As Date = #6:30:00 AM#
    Dim bSuccess As Boolean
    Dim lShift As eShift
   
    Dim intShift As Integer

Private Sub command1_click()
    intShift = 0
    If txtStartTime >= dDayStart And txtStartTime <= dDayStop Then
        intShift = esDay
    ElseIf txtStartTime >= dSwingStart And txtStartTime <= dSwingStop Then
        intShift = esSwing
    ElseIf txtStartTime >= dGraveStart Or txtStartTime <= dGraveStop Then
        intShift = esGrave
    End If
   
    If intShift = 0 Then
        MsgBox "Shift time is wrong!"
        txtStartTime.SetFocus
    Else
        MsgBox intShift
'    Call AddRecord
    End If
End Sub

---

jbart,

That works to a point. You are right I do enter the times and then the shift is determined. I do have a database table that has the shift assigned to an operators name. I don't think the procedure is looking at what shift(txtshift) is assigned to the certain operator(txtopername). Because when I use the formula that was given by you I was able to enter the midnight shift to an operator that works the afternoon shift. I hope this is understandable.

jeffro

Have you checked your constants ? You have overlapping times from 2-3 PM and again from 10-10:30 PM. In those cases, you will need to have more logic to determine which shift should be assigned.

Yes that the way the shifts are within this company. But even when I enter times between the midnight shift, the code accepts the times even though the operator works the afternoon shift. This is very complicated!!

Thanks jeffro

When they enter the time, are you requiring them to put in AM and PM ? Otherwise, that could cause the wrong shift to be assigned.

No I don't require them to put in AM or PM. I'm using military time, so it looks like this 14:00, something like that. I'm going to have to go through the code and look at more closely!

jeffro

jeffro, I now find that the conditions given in your If ¡* Then statements are too simple to meet your actual requirements (and in your orginal code you perhaps misunderstood the Select Case statement). Just think of an entered start time for the first shift. In actual operation, it has to be greater than 6:30 and smaller than 15:00 and the actually entered end time. And, the entered end time must be again smaller than 15:00 but bigger than 6:30 and also bigger than the entered start time. Here is another improved code. I hope it can be of some help to you.

Code:
--------------------------------------------
¡®In order to try it, make three textboxes on the form with the names respectively as txtStartTime, txtEndTime, and txtShift. Make a commandbutton with its default name.
¡®The Format() function is used to make all the date variables unified in format.

Option Explicit

Enum eShift
esDay = 1
esSwing = 2
esGrave = 3
End Enum

Dim dDayStart As Date
Dim dDayStop As Date
Dim dSwingStart As Date
Dim dSwingStop As Date
Dim dGraveStart As Date
Dim dGraveStop As Date

Dim intShift As Integer

Private Sub Form_Load()
dDayStart = Format(#6:30:00 AM#, "General Date")
dDayStop = Format(#3:00:00 PM#, "General Date")
dSwingStart = Format(#2:00:00 PM#, "General Date")
dSwingStop = Format(#10:30:00 PM#, "General Date")
dGraveStart = Format(#10:00:00 PM#, "General Date")
dGraveStop = Format(#6:30:00 AM#, "General Date")


End Sub

Private Sub Command1_Click()

If (Format(CDate(txtStartTime.Text), "General Date") >= dDayStart And _
Format(CDate(txtStartTime.Text), "General Date") <= dDayStop) And _
(Format(CDate(txtEndTime.Text), "General Date") >= dDayStart And _
Format(CDate(txtEndTime.Text), "General Date") <= dDayStop) Then
intShift = esDay
ElseIf (Format(CDate(txtStartTime.Text), "General Date") >= dSwingStart And _
Format(CDate(txtStartTime.Text), "General Date") <= dSwingStop) And _
(Format(CDate(txtEndTime.Text), "General Date") <= dSwingStop And _
Format(CDate(txtEndTime.Text), "General Date") >= dSwingStart) Then
intShift = esSwing
ElseIf (Format(CDate(txtStartTime.Text), "General Date") >= dGraveStart Or _
Format(CDate(txtStartTime.Text), "General Date") <= dGraveStop) And _
(Format(CDate(txtEndTime.Text), "General Date") <= dGraveStop Or _
Format(CDate(txtEndTime.Text), "General Date") >= dGraveStart) Then
intShift = esGrave
Else
intShift = 0
End If

If intShift > 0 Then
txtShift.Text = intShift
Else
MsgBox "Shift time is wrong."
End If

End Sub
------------------------------------------------------
And you have to verify (in the Validate event of a textbox) users' input to ensure that the entered end time is later than the entered start time. -- Edited by xmin

[Edited by xmin on 11-30-2000 at 03:45 AM]

Hi xmin,

Thanks I tested the code you gave me and it works great. Now I just need to find a way to get the opername to put in there. I want the code to allow the user to enter only the opername that is on shift 1 or 2 or 3. Ex: Bob is on shift 2 and the user types in a time for shift 1, I want to have a msgbox come up and say something and then allow the user to reenter the right time for shift 2. Hope that isn't confusing.

Thanks xmin for your help!! Thank you to everyone else that helped out too!!!

jeffro

Hi, jeffro. The following code supposes that you have a table which is mapped on a record set called rst, and the table has a field (Opername) to hold the names of users and a field (ShiftNumber) to hold their shifts. Since I don¡¯t have such a table, I have not tried it. If any bug should crop up, please be patient to solve it.

Code:
----------------------------------
¡®Add another textbox with the name txtOperName.

Private Function VeriOper(ByVal OpName As String) As Long
With rst
.FindFirst ¡°Opername = ¡° & OpName
If .NoMatch Then
VeriOper = 0
Exit Function
End If
If intShift <> !ShiftNumber Then
VeriOper = 1
Exit Function
End If
End With
VeriOper = 3
End Function
---------------------------------

Then add the following to the end of the last Command1_Click() sub.

Code:
--------------------------------
¡®strOper should be declared in advance.
strOper = Trim(txtOperName.Text)

Select Case VeriOper(strOper)
Case 0
Msgbox ¡°No such a person! Enter again.¡±, vbOkOnly + vbExclamation, ¡°Warning¡±
With txtOperName
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub

Case 1
Msgbox ¡°Wrong Shift! Enter again.¡±, vbOkOnly + vbExclamation, ¡°Warning¡±
With txtStartTime
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub

Case 3
¡®The user has entered right.

End Select
------------------------------

hi xmin,

Is this correct .FindFirst txtOpername = OpName? I keep getting an error. Just wondering if I put the code in correctly.

thanks jeffro

jbart,

that worked but now it does not like the .FindFirst! ARGH!!

jeffro

The ADO I am using does not have findfirst, only find. Might give that a try and see what happens.

I'm using the same ADO. I did try using .Find!txtOpername = OpName. No luck!

jeffro

The field after the exclamation point should be the name of the field in your recordset. Is the name of that field Opername or txtOpername ?

txtopername is the name of the field in the recordset.

jeffro

I am sorry to reply so late. I was out for the last few hours and my computer is not always connected to the Internet. Since you are using ADO, the .FindFirst method should be changed to .Find. Make sure that the term Opername (as you have said, you should use txtopername instead) in the quotes must be the exact field name in your table/record set. And perhaps there is still a problem, but I am not sure. Please try changing:

Code:
-------------------------------------
.FindFirst ¡°Opername = ¡° & OpName
-------------------------------------

To

Code:
-------------------------------------
.Find ¡°txtopername = ¡®¡± & OpName & ¡°¡¯¡±
-------------------------------------

For the reason, I am afraid, that the .Find/FindFirst method requires a string value. Note the last part of the statement: a single quote in a pair of double quotes. (And also please pay attention to the spaces in the statement.) That is perhaps where the problem comes from.

Good luck.

Be careful with times. Notice the following:

Code:

---

? format(#6:30:00 AM#,"MM/DD/YY")
12/30/99
? format(date(),"MM/DD/YY")
12/02/00

---

Try it in your debug window.

Yes, jmcswain. Thank you for your reminding. I have noticed that, as you can see in my 12-01-2000 09:06 AM post. Still we have to pay enough attention to that.

xmin,

You have some great code recommendations, but most of the special characters do not display correctly, at least on my screen. It is hard to tell which characters are quotes or exclamation points, etc.

Please look here http://forums.vb-world.net/index.php?action=bbcode on how to use the CODE tag to enclose your VB Code.

Thanks a bunch.

Thank you very, very much indeed, jbart. I have been wondering how you can do so beautifully. I thought you were using a special editor. And now I am leaving for that.
----------------------------------------

The following is for jeffro.

jeffro, please use this improved code for your VeriOper function. This is with ADO, while I was writing with DAO ¨C I didn¡¯t notice that you are using ADO.

Code:
----------------------------------------
Private Function VeriOper(ByVal OpName As String) As Long
With rst
.Find ¡°txtopername = ¡®¡° & OpName & ¡±¡¯¡±
If .Eof Then
VeriOper = 0
Exit Function
End If
If intShift <> !ShiftNumber Then
VeriOper = 1
Exit Function
End If
End With
VeriOper = 3
End Function
-----------------------------------------
You will have good luck. And I am waiting for your good news.


[Edited by xmin on 12-03-2000 at 01:06 AM]

Hey thanks for responding to my post but I can't try anything right now. I won't be into work until monday. I'll try it then.

Thanks everyone

jeffro

Hi xmin,

I'm not sure what all the upside down exclamation marks mean and all the other different markings mean. Could tell me where the double quotes and single qoutes and all the other things I should enter in the new code you gave me? Thank you.

jeffro

I am sorry, jeffro. I did not know how to use the vb code tags so the signs were not clear. Here is the code with code tags.

Code:

---

Private Function VeriOper(ByVal OpName As String) As Long

With rst
    .Find ¡°txtopername = ¡®¡° & OpName & ¡±¡¯¡±
    If .Eof Then
        VeriOper = 0
        Exit Function
    End If

    If intShift <> !ShiftNumber Then
        VeriOper = 1
        Exit Function
    End If
End With

VeriOper = 3

End Function

---

---------------------------------
Many thanks to jbart who told me how to use the vb code tags.

Is this line of code suppose to look like this:

.Find "txtOperName = " & OpName

it's just all the weird characters in your code mess me up. Is there a way to find out what each character means?

jeffro

Hi,

I finely worked out the bugs! I've got the code working with everyone's help of course. I wanted thank xmin and jbart and jmcswain for being patient and for helping me out. Thanks!!!!

If you want the finished code just in case another similiar problem shows up you can email me at [email protected].

jeffro

Quote:

---

.Find ¡°txtOperName = ¡° & OpName

---

Hi, Jeffro. I am sorry not to read your post earlier. After putting up my last post I left on an errand. I am wondering why you still put up the last but one post. Were the marks in my code still not clear? If not I have to think of another way. That is perhaps exactly where the mess came up. As we know VB uses a pair of double quotes (¡°¡*¡±) to mark the beginning and end of a string. When the .Find/FindFirst method receives a string, it strips the quotes off and just takes in the characters. Let¡¯s say in the above quoted statement the variable holds a string ¡°John¡±. When .Find has taken in the argument, into its ¡°stomach¡±, it looks like this:

txtOperName = John

where txtOperName is a field name in your table and John ¨C What is it? A variable! It is taken as in the same status as txtOperName! Just think of txtOpername. It does not refer to any specific operator in your table until you assign one to it. But now you assign John as a variable to it and so .Find can not retrieve from it any specific assignment. That is why the problem came up. In order to improve this situation, I suggested a code:

Code:

---

.Find ¡°txtOperName = ¡®¡± & OpName & ¡°¡¯¡±

---

which, after taken in by .Find, with the double quotes stripped off, will be retrieved as:

txtOperName = ¡®John¡¯

Now we see the complete difference. ¡®John¡¯ is taken as a simple string (.Find uses a pair of single quotes as the string delimiters) and not as a variable. The problem dies away.

That is what I think. I would like to see any other suggestions or improvement on it.

----------------------------------------------
Many thanks to jbart who was so kind to suggest a way for me to learn the vb code tags.