Preventing negative total time

In my form I have ID#, Date, Timein and Timeout, total time.

I am using the medium time format. I want to prevent the total time from 
being a negative time. Example is: time in 8:00 am and time out time 3:00 pm 
total time should be 7 hrs, however if a person enters 8:00 am for time in 
and 3:00 am for time out I will receive a negative number. I want to alert 
the person who is entering the time that you cannot enter this time as it is 
before the time in.

Sorry for the confusion.

Tom



0
Tom
1/15/2008 7:57:21 PM
access.forms 6864 articles. 2 followers. Follow

6 Replies
708 Views

Similar Articles

[PageSpeed] 22

Tom

From your description, you have separate fields for date and time (and 
separate timein and timeout fields).  Why?

Access offers a date/time datatype.  Why not use that and let Access record 
the fact that the start time was 8am YESTERDAY, and the stop time was 3am 
TODAY.  When you do the math on date AND time, you get the correct answer.

When you do the math on YOUR time values, you get negative values!

And by the way, there is rarely a good reason for storing the "totaltime" 
(which is a calculated value).  What happens if someone realizes the start 
or stop time for a record was entered incorrectly and fixes it?  Now your 
"total time" is no longer correct!  Use a query to calculate the difference 
instead of trying to store it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
> In my form I have ID#, Date, Timein and Timeout, total time.
>
> I am using the medium time format. I want to prevent the total time from 
> being a negative time. Example is: time in 8:00 am and time out time 3:00 
> pm total time should be 7 hrs, however if a person enters 8:00 am for time 
> in and 3:00 am for time out I will receive a negative number. I want to 
> alert the person who is entering the time that you cannot enter this time 
> as it is before the time in.
>
> Sorry for the confusion.
>
> Tom
>
>
> 


0
Jeff
1/15/2008 9:10:41 PM
"Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
> In my form I have ID#, Date, Timein and Timeout, total time.
>
> I am using the medium time format. I want to prevent the total time from 
> being a negative time. Example is: time in 8:00 am and time out time 3:00 
> pm total time should be 7 hrs, however if a person enters 8:00 am for time 
> in and 3:00 am for time out I will receive a negative number. I want to 
> alert the person who is entering the time that you cannot enter this time 
> as it is before the time in.
>
> Sorry for the confusion.
>
> Tom
>
>
>


Private Sub Form_BeforeUpdate(Cancel As Integer)
    MsgBox "Time in can't be less than time out"
    Cancel = True
End Sub

Could also be done with a table validation rule.

-- 
Brendan Reynolds 

0
Brendan
1/15/2008 9:18:53 PM
"Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message 
news:4F4A720F-F32D-4A1C-84ED-B29FFDBA947A@microsoft.com...
> "Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
> news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
>> In my form I have ID#, Date, Timein and Timeout, total time.
>>
>> I am using the medium time format. I want to prevent the total time from 
>> being a negative time. Example is: time in 8:00 am and time out time 3:00 
>> pm total time should be 7 hrs, however if a person enters 8:00 am for 
>> time in and 3:00 am for time out I will receive a negative number. I want 
>> to alert the person who is entering the time that you cannot enter this 
>> time as it is before the time in.
>>
>> Sorry for the confusion.
>>
>> Tom
>>
>>
>>
>
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>    MsgBox "Time in can't be less than time out"
>    Cancel = True
> End Sub
>
> Could also be done with a table validation rule.
>
> -- 
> Brendan Reynolds


Oops! Left out the test ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.TimeIn < Me.TimeOut Then
        MsgBox "Time in can't be less than time out"
        Cancel = True
    End If

End Sub

-- 
Brendan Reynolds 

0
Brendan
1/15/2008 9:25:18 PM
Thanks Brendon,

I will give it a try.


"Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message 
news:64F9CD0F-4E4B-40D3-8FB1-D7E9B8C21BFB@microsoft.com...
> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message 
> news:4F4A720F-F32D-4A1C-84ED-B29FFDBA947A@microsoft.com...
>> "Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
>> news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
>>> In my form I have ID#, Date, Timein and Timeout, total time.
>>>
>>> I am using the medium time format. I want to prevent the total time from 
>>> being a negative time. Example is: time in 8:00 am and time out time 
>>> 3:00 pm total time should be 7 hrs, however if a person enters 8:00 am 
>>> for time in and 3:00 am for time out I will receive a negative number. I 
>>> want to alert the person who is entering the time that you cannot enter 
>>> this time as it is before the time in.
>>>
>>> Sorry for the confusion.
>>>
>>> Tom
>>>
>>>
>>>
>>
>>
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>    MsgBox "Time in can't be less than time out"
>>    Cancel = True
>> End Sub
>>
>> Could also be done with a table validation rule.
>>
>> -- 
>> Brendan Reynolds
>
>
> Oops! Left out the test ...
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
>    If Me.TimeIn < Me.TimeOut Then
>        MsgBox "Time in can't be less than time out"
>        Cancel = True
>    End If
>
> End Sub
>
> -- 
> Brendan Reynolds 


0
Tom
1/16/2008 2:46:56 PM
Thanks for the info. There are times when people forget to put in their time
and therefore have to go back and enter the info. I do track what times
those entries have been placed in the form. I do agree that I store data
that I could receive from a query and that is something that I need to work
on.

"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:eqEBXs7VIHA.1164@TK2MSFTNGP02.phx.gbl...
> Tom
>
> From your description, you have separate fields for date and time (and 
> separate timein and timeout fields).  Why?
>
> Access offers a date/time datatype.  Why not use that and let Access 
> record the fact that the start time was 8am YESTERDAY, and the stop time 
> was 3am TODAY.  When you do the math on date AND time, you get the correct 
> answer.
>
> When you do the math on YOUR time values, you get negative values!
>
> And by the way, there is rarely a good reason for storing the "totaltime" 
> (which is a calculated value).  What happens if someone realizes the start 
> or stop time for a record was entered incorrectly and fixes it?  Now your 
> "total time" is no longer correct!  Use a query to calculate the 
> difference instead of trying to store it.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
> news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
>> In my form I have ID#, Date, Timein and Timeout, total time.
>>
>> I am using the medium time format. I want to prevent the total time from 
>> being a negative time. Example is: time in 8:00 am and time out time 3:00 
>> pm total time should be 7 hrs, however if a person enters 8:00 am for 
>> time in and 3:00 am for time out I will receive a negative number. I want 
>> to alert the person who is entering the time that you cannot enter this 
>> time as it is before the time in.
>>
>> Sorry for the confusion.
>>
>> Tom
>>
>>
>>
>
> 


0
Tom
1/16/2008 2:47:33 PM
I was able to make it work when I used ELSE instead of cancel. Cancel did 
not work. The message does show up, however you can continue to add the info 
even after the message pops up. Is there a way to prevent them from going 
any further in the form?

Thanks, Tom

"Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message 
news:64F9CD0F-4E4B-40D3-8FB1-D7E9B8C21BFB@microsoft.com...
> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message 
> news:4F4A720F-F32D-4A1C-84ED-B29FFDBA947A@microsoft.com...
>> "Tom Tripicchio" <fuzzytrip@prodigy.net> wrote in message 
>> news:ulN3ME7VIHA.5984@TK2MSFTNGP06.phx.gbl...
>>> In my form I have ID#, Date, Timein and Timeout, total time.
>>>
>>> I am using the medium time format. I want to prevent the total time from 
>>> being a negative time. Example is: time in 8:00 am and time out time 
>>> 3:00 pm total time should be 7 hrs, however if a person enters 8:00 am 
>>> for time in and 3:00 am for time out I will receive a negative number. I 
>>> want to alert the person who is entering the time that you cannot enter 
>>> this time as it is before the time in.
>>>
>>> Sorry for the confusion.
>>>
>>> Tom
>>>
>>>
>>>
>>
>>
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>    MsgBox "Time in can't be less than time out"
>>    Cancel = True
>> End Sub
>>
>> Could also be done with a table validation rule.
>>
>> -- 
>> Brendan Reynolds
>
>
> Oops! Left out the test ...
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
>    If Me.TimeIn < Me.TimeOut Then
>        MsgBox "Time in can't be less than time out"
>        Cancel = True
>    End If
>
> End Sub
>
> -- 
> Brendan Reynolds 


0
Tom
1/16/2008 5:33:36 PM
Reply:

Similar Artilces:

Sending Emails Multiple Times Before Its Delivered
Yesterday at different times during the day, I forwarded numerous emails (approx. 7) to one address, using MSN and Gmail in Live Mail 14.0.8089.0726. All the emails were a page, or less, in length and some had attachments no larger than .99 mb. Although all the addresses were the same, inserted from my contact list, some of the emails were delivered and some were returned with the following: "This is an automatically generated Delivery Status Notification. Delivery to the following recipients failed." Some of these emails were finally delivered after emailing them ...

Crystal Reports-totalling a Summary
Have a question on totalling a summary field in Crystal Reports, I am using the Minimum, Maximum and average functions to summarize purchase order line items for a bill of materials. I would also like to total these min, max and averages for all the items on a bill of materials but I cant figure out how to make crystal do this. maybe it isnt possible? I get the message that I cant total on a running total field or summary field. any suggestions? -- Doug Doug you may want to go on over to http://www.tek-tips.com/threadminder.cfm?pid=149 I go to this forum and others on tek-tips....

Different Total for end of day sales report
I am currently using RMS 1.2 on a Windows XP/SP2 computer. My problem is when i run various reports at the end of the day like Tender Summary and Detailed Sales report to see the total sales for the day, neither one matches my physical count. Can someone explain to me why i am getting this kind of result? And what i can do to correct it or get the right information? Or maybe i need a custom report to get an accurate total? Thank you in advance. Tender summary and detailed sales reports will have different totals since one (tender summary) includes all monies taken in, including taxes,...

How to display the TOTAL of a sub form on the parent Form?
on my sub form (subDetails) I have a totals column. I then Total the totals column in the footer of the subform in a field named (txtOrderTotal) I want to display that total on the Parent form (frmOrders) (I do not want to just show the footer of the sub form due to placment issues) So on my parrent form I have a text box with this code in the control source =Forms!subDetails.txtOrderTotal BUT all I get is "#NAME?!" I have doubled checked my spelling. Am I going about this wrong? Any help here will be appreciated. Thanks in advance Dave On Fri, 24 Aug 2007 10:38:35 -0700...

How to include the Grand Total Column in a pivot table chart?
Hi, I have a simple pivot table with two columns A and B against several rows of responses. The pivot table also shows the Grand Total Column. I can graph the pivot table quickly and simply with the chart wizard (a bar chart in this instance) and it will display a bar for column A and a bar for B grouped per row response. Great. I want to add what is effectively the third column in the pivot - Grand total column, so I would have 3 bars per row response; A, B and Grand Total. I can then compare A and B to the total bar. How do I add/show the grand total in the pivot chart? Thanks! Yo...

Total or Sum Cell Location Always Just Below Latest Row Entry. Possible?
I would like to have the location of the cell bearing the total of a column of figures vary with the number of figures in that total. EXAMPLE: I place an entry in row 1. The total appears just below it in row 2. I add an entry in row 2. The total of row 1 and 2 then moves to the cell just below the row 2 entry (row 3), and so on. If the total has a label to its left, I would like that label to move as the total moves. I'm sure this is possible, I just can't figure a way to do it! Help much appreciated! Thanks in advance! Frank For column A: in cell A2, put the form...

Negative Data labels disappear
I can only see negative data labels when I increase the maximum of the x-scale. Why is this happening and what can I do to avoid it? Cause if I increase the x-scale too much than the bars are very small and do not look nice. Thanks in advance! Hi, Are all the values negative, or did you mean Minimum scale value. Also is that the Y (vertical) axis? How about changing the position of the data labels so that it is within the bar/column. Cheers Andy Anthi wrote: > I can only see negative data labels when I increase the maximum of the > x-scale. Why is this happening and what can I ...

Time Sheet
I have a time sheet with four fields: Time In (C2), Lunch Out (D2), Lunch In (E2), Time Out (F2) I'm trying to create a formula in cell G2 to calculate the regular hours and another formula in H2 for the overtime hours, but have been unsuccessful. Current fields are formatted as Number | Custom | h:mm AM/PM for C2 thru F2 and are formatted as Number | Custom | h:mm for G2 and H2. Can anyone help me. Hi, Look into CPearson Web it has all the working with overtime Hs http://www.cpearson.com/excel/overtime.htm "Kathy" wrote: > I have a time sheet wi...

Total Hours
Is there a way in excel to convert total hours. I'm trying to convert 8:07 to 8:25, 8:23 to 8:50, 8:37 to 8:45 and 8:55 to 9:00. Is this possible to do in excel. Floyd I'm sure it's possible, but I see no rhyme or reason for the "conversions" you're making. Do the converted values remain XL times? If so, why would 8:23 convert to 8:50, but 8:37 convert to 8:45? Please explain. In article <P6mdnUhULe8YkAyiRVn-jw@comcast.com>, "Floyd Forbes" <floydforbes@comcast.net> wrote: > Is there a way in excel to convert total hours. I'm...

calculated total in query
I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. However, if qty1 has some number but qty2 is blank, my total is blank, instead of just show the sum. In Access You need to use the NZ function to force a ZERO. Or use an IIF clause. Total: Nz(Qty1,0) + Nz(Qty2,0) or Total: IIF(Qty1 is Null,0,Qty1) + IIF(Qty2 is Null,0,Qty2) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Song wrote: > I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. > However, if qty1 has some number b...

How do I prevent the wrong entry in a field with multiple conditio
I have a form with a sequence of the same blank fields, which the user fills from another form control. The idea is that they select what they consider is the correct address, and the control sends the data to the next available empty field on the Target Form. All works well; except for when the user selects the incorrect entry, the action still takes place, and the incorect entry is placed in the empty field, and due to the nature of the action, the sequence moves to the next available empty field. I need a way of dealing with the incorrect entry. What I want to happen is nothing....

How to prevent Internet Mail Service updating all receipients' email addresses
I've had to put another Exchange 5.5 server (ExchangeB) in my organization. We already have one Exchange 5.5 (ExchangeA). ExchangeA is having major problems and I'm looking at taking this offline. It currently acts as the bridgehead server and has the Internet Mail Service connected configured accordingly. Both Exchange servers have the same Home Site and are synchronizing and replicating between each other correctly. Mailboxes have successfully been migrated from ExchangeA to ExchangeB and the client machines are recognising this and re-configuring themselves accordingly when logging...

Excel automatically changes the formatting of the cell to "Time"
Hello, I have a long column of numbers [dates in the YY:DD format]. I wanted to replace ":61" to ":59". Even though the cells are initially formatted as Text, as soon as I make the change, Excel changes the formatting to Time, and the cell with the change now has text ":59:00" in it. Is it possible to force Excel to keep the cells formatted as Text? Thank you! Sam, If the cells are truly formatted as text (Format - Cells - Number - Text), the formatting should never change, and you should always see exactly what you've typed. Give us an example...

Accept Propose time icon missing
Hello When sending between outlook2007 and outlook2003 and vise vera the accept propose icon is missing the from toolbar, the body in text has New Meeting Time Proposed thanks On May 5, 3:42=A0pm, totalnet32 <totalne...@hotmail.com> wrote: > Hello > > When sending between outlook2007 and outlook2003 and vise vera the > accept propose icon is =A0missing the from toolbar, this only happens whe= n someone Proposed a new time. the body in text has New Meeting Time Prop= osed > > thanks ...

Same time and date for every note in a case
Hi, I have a problem in CRM 3 where the date and time for every note in a particular case is exactly the same, despite being added into the case over a number of days. Does anyone have any idea what may cause this? Thanks. ...

How do I create a rule for a mailbox with no single user, which works all the time
I work for a small UK charity. We are running Exchange 2003 and we have separate email addresses for each member of staff and for several areas of work which small groups of staff work on together. Our main email address for the organisation begins info@..., but Yahoo does not allow us to use that email address for our subscription to email discussion groups (an anti-spam measure). I wanted to use one of the other 'area of work' email addresses (library@...) for sending messages to a Yahoo group we are a member of, and to set up a rule in Outlook to forward messages from the Yahoo g...

If Statement with Time
I am trying to write IF THEN Statement using time numbers (i.e. 16:00, 13:30, 6:30). It doesn't appear to be working. Here is a sample =IF(AND(E4<17,E4>4),"NP", "P") where E4 is a cell that contains the military time. I keep only getting a value of P returned even when it should be NP. I tried using 17:00 and 4:00 in the statement and got an error -- Court Hi try =IF(AND(E4<17/24,E4>4/24),"NP", "P") or =IF(AND(E4<TIME(17,0),E4>TIME(4,0)),"NP", "P") -- Regards Frank Kabel Frankfurt, Germany "Co...

Time Required to Save a File
I have a large Excel file that takes about 15 seconds to save if I have two other files open that the first file is linked to. If I do not have these two other files open it takes several minutes to save the first file. Does anyone know what is causing this, and if it can be fixed? I do not always remember to open the other two files so I am frequently stuck waiting for the first file to save. Thanks, John Do you have calculation set to manual? There's an option to recalculate before you save. Tools|options|calculation tab If you have manual calculation set, you could turn off ...

Cannot login into OWA 2003 Sp2. After 3 time login I get access denied. Insufficient permissions.
I installed at several customers of mine a SBS 2003 server with exchange for OWA solutions and several common Exchangeservers 2003 (SP2) at other customers. I also installed at my home address a Exchange Server (2003 SP2) on a Win2K3 Machine. When I try to access from my home address (Default ADSL) to one of my customers, I get no errors. When I try to access from one of my bigger customers (with ISA server), I can get OWA from my home address, but not from my other customers. The same error comes up insufficient permissions. Have to logon 3 times and cannot connect. The left window pane with...

Excell
Formula to count if two conditions. I want to count column b when = "CAS" and if the number in column J is a negative number. I have tried =COUNTIF(B7:B21,"=CAS")=SUM(J7:J21,""<0) but it returns a "false" read. Column "b" are all words and Column "J" is numbers, but sometimes a negative number and I only want it to count if it is "0" or Less (negative) =SUMPRODUCT((B7:B21="CAS")*(J7:J21<0)) -- Gary''s Student - gsnu201002 "linda" wrote: > Formula to count i...

Time-Line Chart
I'm trying to create a "time-line" chart on both sides of the X axis. For example, item '1' lasts (3) seconds long and is 'positive' (above X axis). Item '2' lasts (2) seconds and is 'negative' (below X axis). Item '3' lasts (5) seconds and is 'positive'. Every time I graph this the lengths of the items are graphed vertically instead of horizontally. Any suggestions? *** ***** _________________ ** Thanks, Zack Hi, Zack, Try using a bar chart as the chart type. (Note: If you don't want your timeline...

Run-time Error '-2147217900 (80040e140': HELP!!
I receive the above error when I syn my offline database on one machine. Have disabled any firewalls and checked administrative rights. The rest of the error states "SQL Server blocked access to STATEMENT 'openrowset.........." Thanks Open Sql Server Surface Area Configuration, click on Surface Area Configurationi for Features at the bottom of the screen, Check the Enable OPENROWSET and OPENDATASOURCE support box. Then try to sync the database. "Tony" wrote: > I receive the above error when I syn my offline database on one machine. Have > disabled any f...

Windows Installer loads every time I open Excel 2007
Hi all, I'm facing the following problem: when I open Excel 2007 the windows installer loads every time... is not going to give me any error, but it's something annoying... does anyone has any solution??? thanks Vit p.s. this happens also with other office 2007 applications Try clicking "Help" and then "Detect and Repair..." That worked for me when I had the same problem. ...

Run time error 3077
I am getting a run time error 3077 whenever I select a record from the combobox with an apostophe as part of the text in the combo box. Can anyone tell me what the apostrophes do in the code below and if there is a good error handling feature to prevent this so I can have company names with apostrphes in the combo box without getting the run time error? Listed below is the code: Private Sub CboCompanyLookup_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Client Name] = '...

cursor moving to top of screen all the time
Hi, I have a Toshiba Sat Pro M10 laptop with a cursor problem. Every few minutes the cursor moves to the top of the screen and sometimes the bottom of the screen. It stays there and I can move it back up/down but it fights against me and goes back to the original top or bottom position. I am able to move it left or right only. Then after a couple of minutes it then seems to free it's self and all is well again. I have plugged a USB mouse into the machine but this makes no difference. I have also reformatted and reinstalled the OS (XP Pro) but again exactly the same issue ...