Help with time tracking database

[Guest]

I am new to access so any help would be greatly appreciated.

I created a database that keeps track of employeeID, date, time, and if they
clock-in or clock-out. I create a form that allows the employee to enter
their Id and click on a clock-in or clock-out button. The Clock-in and
Clock-out buttons were check boxs but I changed them to toggle buttons.

Can anyone help me create a macro, expression, or code that will NOT alllow
an employee to clock in twice in a roll and clock-out twice in a roll.

Any help will be greatly appreciated.

 

[Ken Snell \(MVP\)]

What are the fields in the table, especially the one where you record "clock
in" or "clock out" status? What you seek to do can be done, but we need a
bit more information.

 

[Guest]

The table has EmpID, Clock-in, Clock-out, timestamp. The Clock-in and
Clock-out are yes/no data type.

 

[Ken Snell \(MVP\)]

When you indicated that you're using toggle buttons for the "clock in" and
"clock out" status, I assumed that you would have a single field in the
table for recording that status. You indicate that you have two separate
fields to record what essentially are mutually exclusive values, meaning
that both the Clock-in and Clock-out fields cannot have the same value for a
single record. Normally, this would be designed as a single field with the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the table? If
you were to use one field, then you would be able to use an option group,
with the two toggle buttons as "members" of that option group -- and the
option group would be bound to the single field, and one toggle button would
be given the data value of 0 and the other button would be given the data
value of -1 (0 means No and -1 means Yes in ACCESS table for the Yes/No
fields).

So, before I suggest any code to do the validation that you seek, please
provide more details about the form's setup and how you're using the toggle
buttons (with or without an option group, to which field is each bound,
etc.).

 

[Guest]

Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee enters their Id
number, they either click on clock in or clock out. Each button is linked to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like you said.
How do I put them into an option group.

Thanks.

 

[Ken Snell \(MVP\)]

If you're willing, let's change your table's design. Delete the "Clock-out"
field from the table. That will give us a single field ("Clock-in") to store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn -- eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If yes, open
that query in design view and change it so that it contains the "ClockIn"
field and does not contain the previous fields (the ones that you deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar, click "on"
the Wizard button to turn the wizard on (the wizard to do option groups is
pretty good), then click on the Option group icon (a rectangular box with
XYZ at the top), then click on your form. The wizard will take you through
the setup for the option group -- select toggle buttons, set the value of
the Clock In button to -1, and the value of the Clock Out button to 0.

After the option group has been created on the form, set the ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to see if
the most recently added record (prior to the current one) is a clock-in or a
clock-out record, and determine if that would be a "duplicate" of what the
user has selected. I assume that you have the timestamp field in the form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option group.
While the form is in design view, click on the option group (not the toggle
button) and then open the Properties window. Select the Event tab. In the
box next to On Before Update, select [Event Procedure] from the dropdown
list. Then click on the three-dot button at far right of box -- this will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an Exit Sub
line. In the empty line, type the following code steps (remember to replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" & _
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID=" & _
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in"" entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out"" entry!"
End Select
strMsg = strMsg & " Change the entry that you're making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

 

[Guest]

Thanks a lot Ken, I will try that and let you know what happens. You have
been great help.

Thanks.

If you're willing, let's change your table's design. Delete the "Clock-out"
field from the table. That will give us a single field ("Clock-in") to store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn -- eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If yes, open
that query in design view and change it so that it contains the "ClockIn"
field and does not contain the previous fields (the ones that you deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar, click "on"
the Wizard button to turn the wizard on (the wizard to do option groups is
pretty good), then click on the Option group icon (a rectangular box with
XYZ at the top), then click on your form. The wizard will take you through
the setup for the option group -- select toggle buttons, set the value of
the Clock In button to -1, and the value of the Clock Out button to 0.

After the option group has been created on the form, set the ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to see if
the most recently added record (prior to the current one) is a clock-in or a
clock-out record, and determine if that would be a "duplicate" of what the
user has selected. I assume that you have the timestamp field in the form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option group.
While the form is in design view, click on the option group (not the toggle
button) and then open the Properties window. Select the Event tab. In the
box next to On Before Update, select [Event Procedure] from the dropdown
list. Then click on the three-dot button at far right of box -- this will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an Exit Sub
line. In the empty line, type the following code steps (remember to replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" & _
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID=" & _
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in"" entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out"" entry!"
End Select
strMsg = strMsg & " Change the entry that you're making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee enters their
Id
number, they either click on clock in or clock out. Each button is linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like you said.
How do I put them into an option group.

Thanks.

 

[Guest]

I also need to calculate the time difference between clock-in and clock-out.
Right now with the two clock-in clock-out flields, my calculation in the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in flied?

Thanks a lot Ken, I will try that and let you know what happens. You have
been great help.

Thanks.

If you're willing, let's change your table's design. Delete the "Clock-out"
field from the table. That will give us a single field ("Clock-in") to store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn -- eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If yes, open
that query in design view and change it so that it contains the "ClockIn"
field and does not contain the previous fields (the ones that you deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar, click "on"
the Wizard button to turn the wizard on (the wizard to do option groups is
pretty good), then click on the Option group icon (a rectangular box with
XYZ at the top), then click on your form. The wizard will take you through
the setup for the option group -- select toggle buttons, set the value of
the Clock In button to -1, and the value of the Clock Out button to 0.

After the option group has been created on the form, set the ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to see if
the most recently added record (prior to the current one) is a clock-in or a
clock-out record, and determine if that would be a "duplicate" of what the
user has selected. I assume that you have the timestamp field in the form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option group.
While the form is in design view, click on the option group (not the toggle
button) and then open the Properties window. Select the Event tab. In the
box next to On Before Update, select [Event Procedure] from the dropdown
list. Then click on the three-dot button at far right of box -- this will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an Exit Sub
line. In the empty line, type the following code steps (remember to replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" & _
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID=" & _
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in"" entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out"" entry!"
End Select
strMsg = strMsg & " Change the entry that you're making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee enters their
Id
number, they either click on clock in or clock out. Each button is linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like you said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the "clock in"
and
"clock out" status, I assumed that you would have a single field in the
table for recording that status. You indicate that you have two separate
fields to record what essentially are mutually exclusive values, meaning
that both the Clock-in and Clock-out fields cannot have the same value
for a
single record. Normally, this would be designed as a single field with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the table?
If
you were to use one field, then you would be able to use an option group,
with the two toggle buttons as "members" of that option group -- and the
option group would be bound to the single field, and one toggle button
would
be given the data value of 0 and the other button would be given the data
value of -1 (0 means No and -1 means Yes in ACCESS table for the Yes/No
fields).

So, before I suggest any code to do the validation that you seek, please
provide more details about the form's setup and how you're using the
toggle
buttons (with or without an option group, to which field is each bound,
etc.).
--

Ken Snell
<MS ACCESS MVP>



The table has EmpID, Clock-in, Clock-out, timestamp. The Clock-in and
Clock-out are yes/no data type.

:

What are the fields in the table, especially the one where you record
"clock
in" or "clock out" status? What you seek to do can be done, but we
need a
bit more information.

--

Ken Snell
<MS ACCESS MVP>

I am new to access so any help would be greatly appreciated.

I created a database that keeps track of employeeID, date, time, and
if
they
clock-in or clock-out. I create a form that allows the employee to
enter
their Id and click on a clock-in or clock-out button. The Clock-in
and
Clock-out buttons were check boxs but I changed them to toggle
buttons.

Can anyone help me create a macro, expression, or code that will NOT
alllow
an employee to clock in twice in a roll and clock-out twice in a
roll.

Any help will be greatly appreciated.

 

[Ken Snell \(MVP\)]

Again, I profess confusion. How can you use the [Clock-In] and [Clock-Out]
fields, which you said were yes/no fields, as the arguments in a DateDiff
function? If they contain boolean "yes" or "no" values, they will not
contain date/time values. I think I still do not understand your current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table, and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>

I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation in the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in
flied?

Thanks a lot Ken, I will try that and let you know what happens. You
have
been great help.

Thanks.

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field ("Clock-in") to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar, click
"on"
the Wizard button to turn the wizard on (the wizard to do option groups
is
pretty good), then click on the Option group icon (a rectangular box
with
XYZ at the top), then click on your form. The wizard will take you
through
the setup for the option group -- select toggle buttons, set the value
of
the Clock In button to -1, and the value of the Clock Out button to 0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to
see if
the most recently added record (prior to the current one) is a clock-in
or a
clock-out record, and determine if that would be a "duplicate" of what
the
user has selected. I assume that you have the timestamp field in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option
group.
While the form is in design view, click on the option group (not the
toggle
button) and then open the Properties window. Select the Event tab. In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box -- this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an Exit
Sub
line. In the empty line, type the following code steps (remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" & _
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID=" &
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee enters
their
Id
number, they either click on clock in or clock out. Each button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the "clock
in"
and
"clock out" status, I assumed that you would have a single field in
the
table for recording that status. You indicate that you have two
separate
fields to record what essentially are mutually exclusive values,
meaning
that both the Clock-in and Clock-out fields cannot have the same
value
for a
single record. Normally, this would be designed as a single field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the
table?
If
you were to use one field, then you would be able to use an option
group,
with the two toggle buttons as "members" of that option group -- and
the
option group would be bound to the single field, and one toggle
button
would
be given the data value of 0 and the other button would be given the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for the
Yes/No
fields).

So, before I suggest any code to do the validation that you seek,
please
provide more details about the form's setup and how you're using the
toggle
buttons (with or without an option group, to which field is each
bound,
etc.).
--

Ken Snell
<MS ACCESS MVP>



The table has EmpID, Clock-in, Clock-out, timestamp. The Clock-in
and
Clock-out are yes/no data type.

:

What are the fields in the table, especially the one where you
record
"clock
in" or "clock out" status? What you seek to do can be done, but
we
need a
bit more information.

--

Ken Snell
<MS ACCESS MVP>

I am new to access so any help would be greatly appreciated.

I created a database that keeps track of employeeID, date,
time, and
if
they
clock-in or clock-out. I create a form that allows the
employee to
enter
their Id and click on a clock-in or clock-out button. The
Clock-in
and
Clock-out buttons were check boxs but I changed them to toggle
buttons.

Can anyone help me create a macro, expression, or code that
will NOT
alllow
an employee to clock in twice in a roll and clock-out twice in
a
roll.

Any help will be greatly appreciated.

 

[Guest]

I'm sorry for the confussion, I need the time difference to create reports
that show how many hours a person has worked. Here is my table structure.

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
ClockOut Yes/No
TimeStamp Date/Time

The form has the SSN field, clockin and clockout cleck boxes which I turned
into toggle buttons.

Is there a way for me to load my database up so you can take a look at it?
or can I email you my database.

Thanks.

Again, I profess confusion. How can you use the [Clock-In] and [Clock-Out]
fields, which you said were yes/no fields, as the arguments in a DateDiff
function? If they contain boolean "yes" or "no" values, they will not
contain date/time values. I think I still do not understand your current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table, and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>

I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation in the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in
flied?

Thanks a lot Ken, I will try that and let you know what happens. You
have
been great help.

Thanks.

:

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field ("Clock-in") to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar, click
"on"
the Wizard button to turn the wizard on (the wizard to do option groups
is
pretty good), then click on the Option group icon (a rectangular box
with
XYZ at the top), then click on your form. The wizard will take you
through
the setup for the option group -- select toggle buttons, set the value
of
the Clock In button to -1, and the value of the Clock Out button to 0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to
see if
the most recently added record (prior to the current one) is a clock-in
or a
clock-out record, and determine if that would be a "duplicate" of what
the
user has selected. I assume that you have the timestamp field in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option
group.
While the form is in design view, click on the option group (not the
toggle
button) and then open the Properties window. Select the Event tab. In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box -- this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an Exit
Sub
line. In the empty line, type the following code steps (remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" & _
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID=" &
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee enters
their
Id
number, they either click on clock in or clock out. Each button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the "clock
in"
and
"clock out" status, I assumed that you would have a single field in
the
table for recording that status. You indicate that you have two
separate
fields to record what essentially are mutually exclusive values,
meaning
that both the Clock-in and Clock-out fields cannot have the same
value
for a
single record. Normally, this would be designed as a single field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the
table?
If
you were to use one field, then you would be able to use an option
group,
with the two toggle buttons as "members" of that option group -- and
the
option group would be bound to the single field, and one toggle
button
would
be given the data value of 0 and the other button would be given the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for the
Yes/No
fields).

So, before I suggest any code to do the validation that you seek,
please
provide more details about the form's setup and how you're using the
toggle
buttons (with or without an option group, to which field is each
bound,
etc.).
--

Ken Snell
<MS ACCESS MVP>



The table has EmpID, Clock-in, Clock-out, timestamp. The Clock-in
and
Clock-out are yes/no data type.

:

What are the fields in the table, especially the one where you
record
"clock
in" or "clock out" status? What you seek to do can be done, but
we
need a
bit more information.

--

Ken Snell
<MS ACCESS MVP>

I am new to access so any help would be greatly appreciated.

I created a database that keeps track of employeeID, date,
time, and
if
they
clock-in or clock-out. I create a form that allows the
employee to
enter
their Id and click on a clock-in or clock-out button. The
Clock-in
and
Clock-out buttons were check boxs but I changed them to toggle
buttons.

Can anyone help me create a macro, expression, or code that
will NOT
alllow
an employee to clock in twice in a roll and clock-out twice in
a
roll.

Any help will be greatly appreciated.

 

[Ken Snell \(MVP\)]

I did not get an opportunity to review your post last night. I will reply
later today.

 

[Ken Snell \(MVP\)]

Work is keeping me busy tonite... I will reply, but it will be tomorrow at
earliest ... sorry for additional delay....

 

[Ken Snell \(MVP\)]

Was that DateDiff function working for you with your current table
structure? I cannot believe that it gave you a correct elasped time.

Let's use the table structure that I'd suggested:

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
TimeStamp Date/Time

This means that each record is either a "clock in" or a "clock out", based
on the value in ClockIn field.

So, the query to get the elasped time for a clockout record would be this:

SELECT T.SSN,
(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM Tablename AS T;

--

Ken Snell
<MS ACCESS MVP>

I'm sorry for the confussion, I need the time difference to create reports
that show how many hours a person has worked. Here is my table structure.

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
ClockOut Yes/No
TimeStamp Date/Time

The form has the SSN field, clockin and clockout cleck boxes which I
turned
into toggle buttons.

Is there a way for me to load my database up so you can take a look at it?
or can I email you my database.

Thanks.

Again, I profess confusion. How can you use the [Clock-In] and
[Clock-Out]
fields, which you said were yes/no fields, as the arguments in a DateDiff
function? If they contain boolean "yes" or "no" values, they will not
contain date/time values. I think I still do not understand your current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table, and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>

I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation in
the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in
flied?

:

Thanks a lot Ken, I will try that and let you know what happens. You
have
been great help.

Thanks.

:

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field ("Clock-in")
to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If
yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar,
click
"on"
the Wizard button to turn the wizard on (the wizard to do option
groups
is
pretty good), then click on the Option group icon (a rectangular box
with
XYZ at the top), then click on your form. The wizard will take you
through
the setup for the option group -- select toggle buttons, set the
value
of
the Clock In button to -1, and the value of the Clock Out button to
0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to
see if
the most recently added record (prior to the current one) is a
clock-in
or a
clock-out record, and determine if that would be a "duplicate" of
what
the
user has selected. I assume that you have the timestamp field in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option
group.
While the form is in design view, click on the option group (not the
toggle
button) and then open the Properties window. Select the Event tab.
In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box -- this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an
Exit
Sub
line. In the empty line, type the following code steps (remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") &
"#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" &
_
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID="
&
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're
making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee
enters
their
Id
number, they either click on clock in or clock out. Each button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like
you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the
"clock
in"
and
"clock out" status, I assumed that you would have a single field
in
the
table for recording that status. You indicate that you have two
separate
fields to record what essentially are mutually exclusive values,
meaning
that both the Clock-in and Clock-out fields cannot have the same
value
for a
single record. Normally, this would be designed as a single field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the
table?
If
you were to use one field, then you would be able to use an
option
group,
with the two toggle buttons as "members" of that option group --
and
the
option group would be bound to the single field, and one toggle
button
would
be given the data value of 0 and the other button would be given
the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for the
Yes/No
fields).

So, before I suggest any code to do the validation that you seek,
please
provide more details about the form's setup and how you're using
the
toggle
buttons (with or without an option group, to which field is each
bound,
etc.).
--

Ken Snell
<MS ACCESS MVP>



The table has EmpID, Clock-in, Clock-out, timestamp. The
Clock-in
and
Clock-out are yes/no data type.

:

What are the fields in the table, especially the one where you
record
"clock
in" or "clock out" status? What you seek to do can be done,
but
we
need a
bit more information.

--

Ken Snell
<MS ACCESS MVP>

I am new to access so any help would be greatly appreciated.

I created a database that keeps track of employeeID, date,
time, and
if
they
clock-in or clock-out. I create a form that allows the
employee to
enter
their Id and click on a clock-in or clock-out button. The
Clock-in
and
Clock-out buttons were check boxs but I changed them to
toggle
buttons.

Can anyone help me create a macro, expression, or code that
will NOT
alllow
an employee to clock in twice in a roll and clock-out twice
in
a
roll.

Any help will be greatly appreciated.

 

[Guest]

My current query accually works, it gives me the difference between the two.

I have tried what you have suguessed about deleting the clockout field and
just having the clockin field, but it did not work, maybe I messed up on the
naming or something.

But I will create a new database based on what you have given me to see if
it works. I'll let you know what happens. I probably won't do it until sat
or sun but I let you know the results. I hope this works.

When you said that I was using a query for the record source, did you mean
that I created the form using a query?

So if I use your table structure, the before update coding, and the query
that you have provided then the database should work.

Thanks a lot Ken.

Was that DateDiff function working for you with your current table
structure? I cannot believe that it gave you a correct elasped time.

Let's use the table structure that I'd suggested:

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
TimeStamp Date/Time

This means that each record is either a "clock in" or a "clock out", based
on the value in ClockIn field.

So, the query to get the elasped time for a clockout record would be this:

SELECT T.SSN,
(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM Tablename AS T;

--

Ken Snell
<MS ACCESS MVP>

I'm sorry for the confussion, I need the time difference to create reports
that show how many hours a person has worked. Here is my table structure.

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
ClockOut Yes/No
TimeStamp Date/Time

The form has the SSN field, clockin and clockout cleck boxes which I
turned
into toggle buttons.

Is there a way for me to load my database up so you can take a look at it?
or can I email you my database.

Thanks.

Again, I profess confusion. How can you use the [Clock-In] and
[Clock-Out]
fields, which you said were yes/no fields, as the arguments in a DateDiff
function? If they contain boolean "yes" or "no" values, they will not
contain date/time values. I think I still do not understand your current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table, and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>


I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation in
the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in
flied?

:

Thanks a lot Ken, I will try that and let you know what happens. You
have
been great help.

Thanks.

:

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field ("Clock-in")
to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If
yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar,
click
"on"
the Wizard button to turn the wizard on (the wizard to do option
groups
is
pretty good), then click on the Option group icon (a rectangular box
with
XYZ at the top), then click on your form. The wizard will take you
through
the setup for the option group -- select toggle buttons, set the
value
of
the Clock In button to -1, and the value of the Clock Out button to
0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to
see if
the most recently added record (prior to the current one) is a
clock-in
or a
clock-out record, and determine if that would be a "duplicate" of
what
the
user has selected. I assume that you have the timestamp field in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option
group.
While the form is in design view, click on the option group (not the
toggle
button) and then open the Properties window. Select the Event tab.
In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box -- this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an
Exit
Sub
line. In the empty line, type the following code steps (remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") &
"#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" &
_
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID="
&
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're
making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee
enters
their
Id
number, they either click on clock in or clock out. Each button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like
you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the
"clock
in"
and
"clock out" status, I assumed that you would have a single field
in
the
table for recording that status. You indicate that you have two
separate
fields to record what essentially are mutually exclusive values,
meaning
that both the Clock-in and Clock-out fields cannot have the same
value
for a
single record. Normally, this would be designed as a single field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the
table?
If
you were to use one field, then you would be able to use an
option
group,
with the two toggle buttons as "members" of that option group --
and
the
option group would be bound to the single field, and one toggle
button
would
be given the data value of 0 and the other button would be given
the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for the
Yes/No
fields).

So, before I suggest any code to do the validation that you seek,
please
provide more details about the form's setup and how you're using
the
toggle
buttons (with or without an option group, to which field is each
bound,
etc.).
--

Ken Snell
<MS ACCESS MVP>



The table has EmpID, Clock-in, Clock-out, timestamp. The
Clock-in
and
Clock-out are yes/no data type.

:

What are the fields in the table, especially the one where you
record
"clock
in" or "clock out" status? What you seek to do can be done,
but
we
need a
bit more information.

 

[Guest]

I tried the query, but it gives me an error.
The error states:
syntax error (missing operator) in query expression
'(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM TimeClock AS T;'.

After I click on OK on the error box, it highlights the AS infornt of
clocktype.

My current query accually works, it gives me the difference between the two.

I have tried what you have suguessed about deleting the clockout field and
just having the clockin field, but it did not work, maybe I messed up on the
naming or something.

But I will create a new database based on what you have given me to see if
it works. I'll let you know what happens. I probably won't do it until sat
or sun but I let you know the results. I hope this works.

When you said that I was using a query for the record source, did you mean
that I created the form using a query?

So if I use your table structure, the before update coding, and the query
that you have provided then the database should work.

Thanks a lot Ken.

Was that DateDiff function working for you with your current table
structure? I cannot believe that it gave you a correct elasped time.

Let's use the table structure that I'd suggested:

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
TimeStamp Date/Time

This means that each record is either a "clock in" or a "clock out", based
on the value in ClockIn field.

So, the query to get the elasped time for a clockout record would be this:

SELECT T.SSN,
(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM Tablename AS T;

--

Ken Snell
<MS ACCESS MVP>

I'm sorry for the confussion, I need the time difference to create reports
that show how many hours a person has worked. Here is my table structure.

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
ClockOut Yes/No
TimeStamp Date/Time

The form has the SSN field, clockin and clockout cleck boxes which I
turned
into toggle buttons.

Is there a way for me to load my database up so you can take a look at it?
or can I email you my database.

Thanks.

:

Again, I profess confusion. How can you use the [Clock-In] and
[Clock-Out]
fields, which you said were yes/no fields, as the arguments in a DateDiff
function? If they contain boolean "yes" or "no" values, they will not
contain date/time values. I think I still do not understand your current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table, and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>


I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation in
the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the clock-in
flied?

:

Thanks a lot Ken, I will try that and let you know what happens. You
have
been great help.

Thanks.

:

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field ("Clock-in")
to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource. If
yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox toolbar,
click
"on"
the Wizard button to turn the wizard on (the wizard to do option
groups
is
pretty good), then click on the Option group icon (a rectangular box
with
XYZ at the top), then click on your form. The wizard will take you
through
the setup for the option group -- select toggle buttons, set the
value
of
the Clock In button to -1, and the value of the Clock Out button to
0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group control to
see if
the most recently added record (prior to the current one) is a
clock-in
or a
clock-out record, and determine if that would be a "duplicate" of
what
the
user has selected. I assume that you have the timestamp field in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the option
group.
While the form is in design view, click on the option group (not the
toggle
button) and then open the Properties window. Select the Event tab.
In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box -- this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and an
Exit
Sub
line. In the empty line, type the following code steps (remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") &
"#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName", "TimeStampField=#" &
_
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And EmpID="
&
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're
making."
Msgbox strMsg, vbExclamation + vbOK, "Change The Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee
enters
their
Id
number, they either click on clock in or clock out. Each button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one like
you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the
"clock
in"
and
"clock out" status, I assumed that you would have a single field
in
the
table for recording that status. You indicate that you have two
separate
fields to record what essentially are mutually exclusive values,
meaning
that both the Clock-in and Clock-out fields cannot have the same
value
for a
single record. Normally, this would be designed as a single field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in the
table?
If
you were to use one field, then you would be able to use an
option
group,
with the two toggle buttons as "members" of that option group --
and
the
option group would be bound to the single field, and one toggle
button
would
be given the data value of 0 and the other button would be given
the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for the
Yes/No
fields).

So, before I suggest any code to do the validation that you seek,
please
provide more details about the form's setup and how you're using
the
toggle
buttons (with or without an option group, to which field is each
bound,
etc.).

 

[Ken Snell \(MVP\)]

Sorry - I left out a parenthesis:

SELECT T.SSN,
(IIf([ClockIn]=True,"Clock In","Clock Out")) AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM Tablename AS T;


In your other post, you indicated that the query was working to give you the
time difference. I just cannot understand how that can be if the two "clock"
fields were yes/no data types.

--

Ken Snell
<MS ACCESS MVP>

I tried the query, but it gives me an error.
The error states:
syntax error (missing operator) in query expression
'(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM TimeClock AS T;'.

After I click on OK on the error box, it highlights the AS infornt of
clocktype.

My current query accually works, it gives me the difference between the
two.

I have tried what you have suguessed about deleting the clockout field
and
just having the clockin field, but it did not work, maybe I messed up on
the
naming or something.

But I will create a new database based on what you have given me to see
if
it works. I'll let you know what happens. I probably won't do it until
sat
or sun but I let you know the results. I hope this works.

When you said that I was using a query for the record source, did you
mean
that I created the form using a query?

So if I use your table structure, the before update coding, and the query
that you have provided then the database should work.

Thanks a lot Ken.

Was that DateDiff function working for you with your current table
structure? I cannot believe that it gave you a correct elasped time.

Let's use the table structure that I'd suggested:

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
TimeStamp Date/Time

This means that each record is either a "clock in" or a "clock out",
based
on the value in ClockIn field.

So, the query to get the elasped time for a clockout record would be
this:

SELECT T.SSN,
(IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
Tablename AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM Tablename AS T;

--

Ken Snell
<MS ACCESS MVP>


I'm sorry for the confussion, I need the time difference to create
reports
that show how many hours a person has worked. Here is my table
structure.

Field Name: Data Type:
SSN (this is the emplID) Text
ClockIn Yes/No
ClockOut Yes/No
TimeStamp Date/Time

The form has the SSN field, clockin and clockout cleck boxes which I
turned
into toggle buttons.

Is there a way for me to load my database up so you can take a look
at it?
or can I email you my database.

Thanks.

:

Again, I profess confusion. How can you use the [Clock-In] and
[Clock-Out]
fields, which you said were yes/no fields, as the arguments in a
DateDiff
function? If they contain boolean "yes" or "no" values, they will
not
contain date/time values. I think I still do not understand your
current
table structure, so I am hesitant to provide a suggestion yet.

Can you again describe the fields that are in your current table,
and
describe the data types that they are?
--

Ken Snell
<MS ACCESS MVP>


I also need to calculate the time difference between clock-in and
clock-out.
Right now with the two clock-in clock-out flields, my calculation
in
the
query looks like this:

Hours: DateDiff("n",[Clock-In],[Clock-Out])/60

How will I be able to calculate the difference with just the
clock-in
flied?

:

Thanks a lot Ken, I will try that and let you know what happens.
You
have
been great help.

Thanks.

:

If you're willing, let's change your table's design. Delete the
"Clock-out"
field from the table. That will give us a single field
("Clock-in")
to
store
the type of "clock" procedure.

(Note: I also suggest that you change the field name to
ClockIn --
eliminate
the hyphen from the name.)

I assume that you're using a query for the form's RecordSource.
If
yes,
open
that query in design view and change it so that it contains the
"ClockIn"
field and does not contain the previous fields (the ones that
you
deleted /
changed name).

Now, let's open your form in design view. In the Toolbox
toolbar,
click
"on"
the Wizard button to turn the wizard on (the wizard to do
option
groups
is
pretty good), then click on the Option group icon (a
rectangular box
with
XYZ at the top), then click on your form. The wizard will take
you
through
the setup for the option group -- select toggle buttons, set
the
value
of
the Clock In button to -1, and the value of the Clock Out
button to
0.

After the option group has been created on the form, set the
ControlSource
of the option group itself to ClockIn field.

Now, we'll use the BeforeUpdate event of the option group
control to
see if
the most recently added record (prior to the current one) is a
clock-in
or a
clock-out record, and determine if that would be a "duplicate"
of
what
the
user has selected. I assume that you have the timestamp field
in the
form's
RecordSource so that we can use its value in our test.

I am using "generic" names for fields and controls in the
following
code
example -- be sure to replace them with the real names.

We'll put an event procedure on the BeforeUpdate event of the
option
group.
While the form is in design view, click on the option group
(not the
toggle
button) and then open the Properties window. Select the Event
tab.
In
the
box next to On Before Update, select [Event Procedure] from the
dropdown
list. Then click on the three-dot button at far right of box --
this
will
take you to the Visual Basic Editor (VBE).

In the VBE, you'll see a Private Sub line, an empty line, and
an
Exit
Sub
line. In the empty line, type the following code steps
(remember to
replace
my generic names with the real names):

Dim blnValue As Boolean
Dim datTimeDate As Date
Dim strMsg As String
Dim varValue As Variant
Dim varTimeDate As Variant
varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss")
&
"#")
If IsNull(varTimeDate) = False Then
datTimeDate = CDate(varTimeDate)
varValue = DLookup("ClockIn", "TableName",
"TimeStampField=#" &
_
Format(datTimeDate, "m\/d\/yyyy h\:nn\:ss") & "# And
EmpID="
&
_
Me.EmpID.Value
If IsNull(varValue) = False Then
blnValue = CBool(varValue)
If blnValue = Me.OptionGroupName.Value Then
Select Case Me.OptionGroupName.Value
Case -1 ' clock in entry
strMsg = "Your last entry was a ""clock
in""
entry!"
Case 0 ' clock out entry
strMsg = "Your last entry was a ""clock
out""
entry!"
End Select
strMsg = strMsg & " Change the entry that you're
making."
Msgbox strMsg, vbExclamation + vbOK, "Change The
Entry!"
Cancel = True
End If
End If
End If

--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken,

The toggle buttons are link to the table. After an employee
enters
their
Id
number, they either click on clock in or clock out. Each
button is
linked
to
their respective flieds and they are not in an option group.
Which way would be easier, having the 2 flieds or just one
like
you
said.
How do I put them into an option group.

Thanks.

:

When you indicated that you're using toggle buttons for the
"clock
in"
and
"clock out" status, I assumed that you would have a single
field
in
the
table for recording that status. You indicate that you have
two
separate
fields to record what essentially are mutually exclusive
values,
meaning
that both the Clock-in and Clock-out fields cannot have the
same
value
for a
single record. Normally, this would be designed as a single
field
with
the
Yes/No (boolean) data type.

Do you have each toggle button bound to one of the fields in
the
table?
If
you were to use one field, then you would be able to use an
option
group,
with the two toggle buttons as "members" of that option
group --
and
the
option group would be bound to the single field, and one
toggle
button
would
be given the data value of 0 and the other button would be
given
the
data
value of -1 (0 means No and -1 means Yes in ACCESS table for
the
Yes/No
fields).

So, before I suggest any code to do the validation that you
seek,
please
provide more details about the form's setup and how you're
using
the
toggle
buttons (with or without an option group, to which field is
each
bound,
etc.).

 

[Ken Snell \(MVP\)]

A form (and report) has a Record Source that provides the records for the
form. This always is a query -- if you create and save a query and then use
it as the Record Source, or whether you write an SQL statement and use it as
the Record Source, or whether you use a table as the Record Source (in this
case, ACCESS creates a "hidden" query that gets the data from the table).

 

[Guest]

Ken, when I press on the clockin or clockout button, it gives me a syntax
error stating: Run-Time error '3075':
Syntax error in the date in query expression'SSN=987654321 and TimeStamp <##'.
After I hit debug it highlisghts,
varTimeDate = DMax("TimeStamp", "TimeClock", "SSN=" & _
Me.SSN.Value & " And TimeStamp <#" & _
Format(Me.TimeStamp.Value, "m\/d\/yyyy h\:nn\:ss") & "#")

This is the coding in the beforeupdate event.

 

[Guest]

I fixed the 3075 error, I added a extra ")", but now I'm getting a run-time
error 3464': Data type mismatch in criteria expression.
After I hit debug it highlisghts,

varTimeDate = DMax("TimeStampField", "TableName", "EmpID=" & _
Me.EmpID.Value & " And TimeStampField<#" & _
Format(Me.TimeStampField.Value, "m\/d\/yyyy h\:nn\:ss") & "#")

I have also set the On Click event to submit, so that is will capture the
data to the table.
After I click the clockin button does not pop back out and I can't click on
it anymore. Then when I click the clockout button, it gives me theabove
error.

 

[Ken Snell \(MVP\)]

I am using a generic name in the sample code: TimeStampField. You must
replace this with the actual name of the control that holds the TimeStamp
value on the form.

The clockin button will not "pop up" after you submit unless you set the
value of the option group that holds the toggle buttons to Null. Do that in
the code that runs when you do the submit -- after the record is saved, of
course.