Date Question - microsoft.public.access.formscoding

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

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" wrote: > 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 > > > >

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" wrote: > 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" wrote: > > > 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 > > > > > > > >

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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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 > > > > > > > > > > > >

Thank you very much Dale... "Dale Fye" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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 > > > > > > > > > > > > > > > >

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" wrote: > Thank you very much Dale... > > "Dale Fye" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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" wrote: > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > >

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" wrote: > 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" wrote: > > > Thank you very much Dale... > > > > "Dale Fye" wrote: > > > > > 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" wrote: > > > > > > > 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" wrote: > > > > > > > > > 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" wrote: > > > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > >

Similiar Articles:

Date format question - Regional Setting? - microsoft.public.access ...
Hi all, I am running a mde file on multiple XP workstations with Access 2002. What is really weird is on some machines, everything works well, but o...

MS Date Time Picker 6.0 question - microsoft.public.excel ...
Just wondering, is it safe to assume that the Microsoft Date/Time Picker 6.0 Control will always start up with the current date??? ...

Question about estimated vs actual - microsoft.public.project ...
Task Duration vs Actual Effort vs Actual start date - microsoft ... Question about estimated vs actual - microsoft.public.project ... Task Duration vs Actual Effort vs ...

Using Date and time in YYYYMMDDHHMMSS format automatically ...
I have a question about using date and time in the following format: yyyymmddhhmmss, i.e. 20070328144801 I have a column in a table called ScLoadTi...

How can I reference a value from a previous record? - microsoft ...
(The previous record would be determined using the Inspection Date field) My question is: How can I reference the value from the previous inspection record, in order to ...

Sendobject Question - microsoft.public.access.forms
I am trying to use a datepicker to set number of days until an email reminder is sent. I am a little over my head. I am trying to use something like: If me.date ...

Query for previous week, Sun thru Sat, from date field - microsoft ...
... Post Question Groups About ... DateAdd("d",1-WeekDay(Date())-7,Date()) And DateAdd("d",1-WeekDay(Date())-1,Date ...

MailItem Find Method question - microsoft.public.outlook.program ...
Test for a null date - microsoft.public.access.reports MailItem Find Method question - microsoft.public.outlook.program ... If the Subject is "Test", the following code ...

VBA-- add one year , base on the date of another textbox ...
I have textbox1 and textbox2The user will enter the DATE into textbox1 (for example: 2007-03-25 or 2007-09-18 etc)My question is After the user inpu...

date - regional setting - microsoft.public.access.formscoding ...
Date format question - Regional Setting? - microsoft.public.access ... Hi all, I am running a mde file on multiple XP workstations with Access 2002.

The Best Questions For A First Date « OkTrends
First dates are awkward. There is so much you want to know about the person across the table from you, and yet so little you can directly ask.

The 45 best speed dating questions you can ask a prospective date ...
Speed dating is the hot new alternative on the dating scene, compared to barhopping and online dating. In the present world of speed and frenzy, speed dating is the ...

Start With A Date - 25 Questions
Speed dating is a new, fun, exciting, and no pressure way to meet other singles in your age and/or interest group. What makes speed dating different from the typical ...

Dating Questions
Have you got any dating questions you want answered? Then visit our site and check out loads of free articles, tips and advice.

First Date Questions - LoveToKnow: Advice women can trust
First date questions can help you find out whether you want to go on a second date with someone. First impressions are everything, so you want to make sure you ask ...

7/24/2012 6:20:14 PM