Date Question

R

Rick_C

I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
 
K

KARL DEWEY

This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));
 
R

Rick_C

Karl,

Thank you for your response...

I have a follow-up...

I presume the query you gave me would be for a report. Is there a way that I
can put that query in the before or after update of the OTWorkEnd field so
that it let's me know before the record is saved?

Thanks in advance for your help...

Rick

KARL DEWEY said:
This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));

--
KARL DEWEY
Build a little - Test a little


Rick_C said:
I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
Click to expand...
Click to expand...
 
D

Dale Fye

Rick,

Sure. I would probably write a function to test the values, or maybe do the
test in the forms BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel as integer)

Dim dtRStart as date, dtREnd as date, _
dtOStart as Date, dtOEnd as date

dtRStart = me.RegWorkDate + me.RegWorkStart
dtREnd = me.regWorkDate + me.regWorkEnd
dtOStart = me.OTWorkDate + me.OTWorkStart
dtOEnd = me.OTWorkDate + me.OTWorkEnd

if dtOStart >= dtRStart AND dtOStart <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
elseif dtOEnd >= dtRStart AND dtOEnd <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
ENDIF

End Sub

You could throw some additional checks in there to make sure the regular
work end is greater than regular work start, and the same for the OT work end
and start.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rick_C said:
Karl,

Thank you for your response...

I have a follow-up...

I presume the query you gave me would be for a report. Is there a way that I
can put that query in the before or after update of the OTWorkEnd field so
that it let's me know before the record is saved?

Thanks in advance for your help...

Rick

KARL DEWEY said:
This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));

--
KARL DEWEY
Build a little - Test a little


Rick_C said:
I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
Click to expand...
Click to expand...
Click to expand...
 
R

Rick_C

Thank you very much Dale...

Dale Fye said:
Rick,

Sure. I would probably write a function to test the values, or maybe do the
test in the forms BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel as integer)

Dim dtRStart as date, dtREnd as date, _
dtOStart as Date, dtOEnd as date

dtRStart = me.RegWorkDate + me.RegWorkStart
dtREnd = me.regWorkDate + me.regWorkEnd
dtOStart = me.OTWorkDate + me.OTWorkStart
dtOEnd = me.OTWorkDate + me.OTWorkEnd

if dtOStart >= dtRStart AND dtOStart <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
elseif dtOEnd >= dtRStart AND dtOEnd <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
ENDIF

End Sub

You could throw some additional checks in there to make sure the regular
work end is greater than regular work start, and the same for the OT work end
and start.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rick_C said:
Karl,

Thank you for your response...

I have a follow-up...

I presume the query you gave me would be for a report. Is there a way that I
can put that query in the before or after update of the OTWorkEnd field so
that it let's me know before the record is saved?

Thanks in advance for your help...

Rick

KARL DEWEY said:
This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));

--
KARL DEWEY
Build a little - Test a little


:

I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Rick,

You will also need to check that the values entered in each of the date/time
fields are valid date/time values. If not, you will most likely generate an
error when you try to sum the date and time portions of the various fields.

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rick_C said:
Thank you very much Dale...

Dale Fye said:
Rick,

Sure. I would probably write a function to test the values, or maybe do the
test in the forms BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel as integer)

Dim dtRStart as date, dtREnd as date, _
dtOStart as Date, dtOEnd as date

dtRStart = me.RegWorkDate + me.RegWorkStart
dtREnd = me.regWorkDate + me.regWorkEnd
dtOStart = me.OTWorkDate + me.OTWorkStart
dtOEnd = me.OTWorkDate + me.OTWorkEnd

if dtOStart >= dtRStart AND dtOStart <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
elseif dtOEnd >= dtRStart AND dtOEnd <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
ENDIF

End Sub

You could throw some additional checks in there to make sure the regular
work end is greater than regular work start, and the same for the OT work end
and start.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rick_C said:
Karl,

Thank you for your response...

I have a follow-up...

I presume the query you gave me would be for a report. Is there a way that I
can put that query in the before or after update of the OTWorkEnd field so
that it let's me know before the record is saved?

Thanks in advance for your help...

Rick

:

This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));

--
KARL DEWEY
Build a little - Test a little


:

I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
Click to expand...
Click to expand...
Click to expand...
 
R

Rick_C

Dale,

Evidently my values are correct since your code is working. I did modify it
a little to better suit my needs. Thank you for your assistance.... and I did
rate the post.

Cheers,

Rick

Dale Fye said:
Rick,

You will also need to check that the values entered in each of the date/time
fields are valid date/time values. If not, you will most likely generate an
error when you try to sum the date and time portions of the various fields.

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rick_C said:
Thank you very much Dale...

Dale Fye said:
Rick,

Sure. I would probably write a function to test the values, or maybe do the
test in the forms BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel as integer)

Dim dtRStart as date, dtREnd as date, _
dtOStart as Date, dtOEnd as date

dtRStart = me.RegWorkDate + me.RegWorkStart
dtREnd = me.regWorkDate + me.regWorkEnd
dtOStart = me.OTWorkDate + me.OTWorkStart
dtOEnd = me.OTWorkDate + me.OTWorkEnd

if dtOStart >= dtRStart AND dtOStart <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
elseif dtOEnd >= dtRStart AND dtOEnd <= dtREnd Then
msgbox "OT Start must be after the regular work day ends!"
Cancel = True
ENDIF

End Sub

You could throw some additional checks in there to make sure the regular
work end is greater than regular work start, and the same for the OT work end
and start.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Karl,

Thank you for your response...

I have a follow-up...

I presume the query you gave me would be for a report. Is there a way that I
can put that query in the before or after update of the OTWorkEnd field so
that it let's me know before the record is saved?

Thanks in advance for your help...

Rick

:

This query will find overlap ---
SELECT Rick_C.EmpCode, Rick_C.RegWorkDate, Rick_C.RegWorkStart,
Rick_C.RegWorkEnd, Rick_C.OTWorkDate, Rick_C.OTWorkStart, Rick_C.OTWorkEnd
FROM Rick_C
WHERE (((Rick_C.OTWorkStart) Between [RegWorkStart] And [RegWorkEnd])) OR
(((Rick_C.OTWorkEnd) Between [RegWorkStart] And [RegWorkEnd]));

--
KARL DEWEY
Build a little - Test a little


:

I would appreciate some help coming up with this solution...

I am working with the following fields, and the Data Type is: Date/Time

RegWorkDate
RegWorkStart
RegWorkEnd

OTWorkDate
OTWorkStart
OTWorkEnd

I need to ensure that the OT data does not fall within the same date and
times as the RegWork data.

For example:

If someone works regular hours on 01/01/2008 from 8:00 a.m. to 5:00 p.m.,
then the OTWorkDate + the OTWorkStart AND OTWorkEnd cannot fall within those
times.

Thank you in advance for your assistance.

Rick
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtract day and time w/o weekends or holidays 2
date based query fails to find records in given period 8
date based query fails to find records in given period 3
Calculate difference between hours 5
Calculating difference between times on 2 dates 4
Look up between 2 times over midnight - is there an easier way than recordset code? 20
Query Date issue 2
Time Calculation 1

Top