Conditions on form calculated textbox

Hello,

In a form called Schedule (based on the table Schedule) I have the following
textboxes that are bound to the table Schedule:

Time In, Time Out, Time Off, Anomaly and Overtime

I also have an unbound textbox called HRS that gives me the result of the
following equation:

=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime]

The above result gives me the daily hours that someone worked.

The formating is decimal, as an example.
16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0

I also added another figure for lunch the hour
[16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0] - 1.0 = 9.0

The issue is that some employees work a variable schedule and may not take
the lunch hour.  Would anyone know how to set up a condition to substract or
not count the lunch hour based on the amount of hours worked.

The idea is:

If [Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] >5 Then -1 Or
=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] <5 Then 0

Thank you.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1

0
Chris75
11/25/2009 7:32:29 PM
access.gettingstarted 618 articles. 1 followers. Follow

3 Replies
569 Views

Similar Articles

[PageSpeed] 28

=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] -
IIF([Time Out]-[Time In]-[Time Off]-[Anomaly]+[Overtime]>5,1,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Chris75 via AccessMonster.com wrote:
> Hello,
> 
> In a form called Schedule (based on the table Schedule) I have the following
> textboxes that are bound to the table Schedule:
> 
> Time In, Time Out, Time Off, Anomaly and Overtime
> 
> I also have an unbound textbox called HRS that gives me the result of the
> following equation:
> 
> =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime]
> 
> The above result gives me the daily hours that someone worked.
> 
> The formating is decimal, as an example.
> 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0
> 
> I also added another figure for lunch the hour
> [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0] - 1.0 = 9.0
> 
> The issue is that some employees work a variable schedule and may not take
> the lunch hour.  Would anyone know how to set up a condition to substract or
> not count the lunch hour based on the amount of hours worked.
> 
> The idea is:
> 
> If [Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] >5 Then -1 Or
> =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] <5 Then 0
> 
> Thank you.
> 
0
John
11/25/2009 8:22:25 PM
Chris,

I don't know if you are still in the design process but I am not sure your 
table is set up in a way that would best serve the purpose of time card 
reporting.  A normalized setup would be each time record with the employee, 
date, type of record (regular, vacation, etc), time in and time out.  Then, 
the time between punches would be added for the total time for that day.  
Overtime would be calculated at a difference between the hours for the day 
and the standard.  If an employee punched out for lunch and back in on 
return, the sum of the records would automatically reflect that.  You could 
also do the lunch out time based on the total time spent (i.e. 8.5 hours 
means .5 hours of lunch).  You could either calculate 
the time in reports or run a utility that would append the lunch record as a 
negative time entry.  The same could be done for the overtime to recategorize 
the earnings but still leave the actual punches in tact.

There are many ways to achive a specific need in this case, the import part 
is that you are not restricting your data to a certain number of fields.  
Relational databases are designed to avoid such an issue.


"Chris75 via AccessMonster.com" wrote:

> Hello,
> 
> In a form called Schedule (based on the table Schedule) I have the following
> textboxes that are bound to the table Schedule:
> 
> Time In, Time Out, Time Off, Anomaly and Overtime
> 
> I also have an unbound textbox called HRS that gives me the result of the
> following equation:
> 
> =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime]
> 
> The above result gives me the daily hours that someone worked.
> 
> The formating is decimal, as an example.
> 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0
> 
> I also added another figure for lunch the hour
> [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0] - 1.0 = 9.0
> 
> The issue is that some employees work a variable schedule and may not take
> the lunch hour.  Would anyone know how to set up a condition to substract or
> not count the lunch hour based on the amount of hours worked.
> 
> The idea is:
> 
> If [Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] >5 Then -1 Or
> =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] <5 Then 0
> 
> Thank you.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1
> 
> .
> 
0
Utf
11/25/2009 9:09:02 PM
Thank you very much John!

John Spencer wrote:
>=[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] -
>IIF([Time Out]-[Time In]-[Time Off]-[Anomaly]+[Overtime]>5,1,0)
>
>John Spencer
>Access MVP 2002-2005, 2007-2009
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> Hello,
>> 
>[quoted text clipped - 26 lines]
>> 
>> Thank you.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1

0
Chris75
11/26/2009 10:21:18 AM
Reply:

Similar Artilces:

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

faxing form
Is there a way to fax the form page via the fax modem on my computer? Create a report based on your form then fax the resulting report. Take a look at http://support.microsoft.com/Default.aspx?kbid=231797 hopefully it is what you are looking for. -- Hope this helps, Daniel Pineault If this post was helpful, please rate it by using the vote buttons. "He cries for help" wrote: > Is there a way to fax the form page via the fax modem on my computer? "He cries for help" <Hecriesforhelp@discussions.microsoft.com> wrote in message news:252D07EA-5886-4B0C-99...

reference to master form
Hello there I have master form and inside there is subform. If an event procedure uccur on subform, how can i get refence to the master form in order to change there things when the name of the master form is diffrent always? Roy Look in Access HELP for the "Parent." property. Your code will look something like: Me.Parent.... from within the subform. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Roy Goldhammer" <roy@ho...

How can I refresh the master form from a subform?
Hello. On the After Update of the subform I am trying to refresh the Master form. I have tried things like Docmd.requery and Me.Refresh, but not sure what I am doing. Can you help me with this? Thanks. Iram Iram wrote: >On the After Update of the subform I am trying to refresh the Master form. I >have tried things like Docmd.requery and Me.Refresh, but not sure what I am >doing. Try using: Parent.Requery -- Marsh MVP [MS Access] ...

Modal Form from a child form
Hi, I have a little problem, I have a MDI form (Main Menu), this form open a child form (A), and I need to open a form B as modal form from form A. I can to this with showDialog(), but it open form B outside MDI form (Main Menu) and it is showed on the windows bar as another application opened. So the question is, there are any way to open a modal form from a child form inside a MDI form? Thanks El Alquimista wrote: > Hi, I have a little problem, I have a MDI form (Main Menu), this form > open a child form (A), and I need to open a form B as modal form from > form A. I c...

Calculating working hours #2
Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" <MohammedZenuwah@discu...

Data source for PivotTable-Form in ACCESS 2000
Hi, this drives me crazy, 4 years ago I defined in an ACCESS 2000 application a "PivotTable-Form". The resulting EXCEL table inclusive the "Data refresh" works perfect. I now want to update/change the query for the "Data Source" but cannot find which query is behind the "PivotTable-Form" or behind the resulting EXCEL spreadsheet. When editing the properties for the "PivotTable-Form" or the EXCEL spreadsheet the "Data Source" is always blank. Question: Where does Access 2000 or EXCEL 2000 hide the respective data source (Query)...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

Stop Buttons showing when opening up form
I have a button on my Main Start up page that when click makes these buttons visible, but when I open up my DB they automatically show on start up, is it possible they not be visible till I click ckbHelp.......Thanks for any Help.....Bob Private Sub ckbHelp_Click() If ckbHelp = True Then cmbHelpNewHorse.Visible = True cmbHelpActFinHorse.Visible = True End If If ckbHelp = False Then cmbHelpNewHorse.Visible = False cmbHelpActFinHorse.Visible = False End If End Sub Bob, In design view, set the property pf the *button* Visible=No Regards/Jacob "Bob" <xxx@xx.xx> wrote i...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

Cannot view any form in crm 3.0
We have just installed CRM 3.0 on MS server 2003. I have created an account which has a system administrator role assigned to it. The issue is that when I open CRM web (on the server) when I select account, contact, activities, calendar etc... I get the following error: An error has occured. For more information, contact your system administrator. Now if I open CRM web using an account which has restricted access mode ticked, I don't get any errors, everything opens fine. The problem is that with an restricted access code i can't do much. I have tried assigning different roles ...

Bug with controls location and scrollbars in a Windows Form
Hi, I've noticed a bug with WinForm and scrollbars, and I would like to know if there is a workaround ... Bug description : ------------------------ 1) create a win form, and add 3 buttons : * FirstButton : upper left corner of the winform * SecondButton : location = (0 ; 600) * ThirdButton : location = (100, 600) 2) Set : ThirdButton.Visible = false 3) Set the Form size to 100 x 100 ... and set : AutoScroll = true 4) Add the following event handler to the Click event of FirstButton and SecondButton ... private vo...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...

how would you calculate the number of hits to your website
Hi ,' can some one tell to me the answer of this question On a website, how would you calculate the number of hits to your website ...

Minimizing form
Hi How can I minimize the current form? Thanks Regards DoCmd.Minimize in a command button code or other event. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "John" <John@nospam.infovis.co.uk> wrote in message news:uBcRcE73HHA.3600@TK2MSFTNGP02.phx.gbl... > Hi > > > > How can I minimize the current form? > > > > Thanks > > > > Regards > > ...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

Tax calculation for Quotes, Orders, Invoices
Although CRM v3.0 allows tax to be keyed in to an Order, it does not offer any automation in calculating tax for an order based on taxable line items and shipping location. Entering orders is a repetitive and time-consuming event, and so this calculation should be offered as a feature. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based N...

Calculating Date Fields
I have a document that is filled with FormFields and users go from field to field filling the fields for a final document. I have been reading up on 'calculated dates' but have not seen a scenario like this. Three of my formfields are "Date1", "Date2" and "Date180." What I am trying to figure out is how to evaluate/compare Date1 and Date2 to see which is the earliest and then provide in Date180 the date 180 days from the earliest date. (Unless the form is blank there will always be a Date1, but there may not be a Date2 and if Date2 exist...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...

Form pages
Is it possible to have a two page form and only print the first page or second page when needed.? Depending on your design, you should be able to use the File/Print dialog box selection to print 1 of 2, or 2 of 2 pages. But, forms are made for data entry, and viewing... not printing. Better that you develop a rptPageOne and a rptPageTwo and select either for printing from your form. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." &quo...

Conditional Concatenate
Hi all. Its been a while since I was last here. I'm wondering if its possibl to devise a formula which concatenates a sentence in column B each tim there's an occurence in column A ? Thanks T -- Message posted from http://www.ExcelForum.com One way: B1: =IF(A1<>"",A1 & " starts this sentence.","") copy down as far as necessary. In article <twaccess.16eioe@excelforum-nospam.com>, twaccess <<twaccess.16eioe@excelforum-nospam.com>> wrote: > I'm wondering if its possible > to devise a formula which concaten...