Calculating the difference between two fields

Hello,
I have a subform in a table which has the following controls:
Type of Day....Begin Date.....End Date....Total Days

There may be several rows of information in these controls.  The form is 
used to calculate the number of vacation days a person has taken.  I have 
written some code that uses that DateDiff function to give me the difference 
between Begin Date and End Date.  The result is supposed to go into the TOTAL 
Days control.  My problem is that it appears that the calculation is 
performed only once. So for instance, if John Doe has as his first row Begin 
Date 3/13/2007 and End Date 3/15/2007, the Total Days calculates 2 days, 
which is correct, but then on the next line if he has 4/15/2007 as the begin 
date and 4/21/2007 as the end date, it still give me "2" as the total days.  
Here is the code I wrote:

Private Sub Form_Load()
Dim dTaken
Dim dStart
Dim dEnd
dEnd = Me.OLP_End_Date1.Value
dStart = Me.OLP_Begin_Date1.Value

If Me.Type_of_Day.Value = "Ill" Or Me.Type_of_Day.Value = "Vacation" Then
  dTaken = DateDiff("d", dStart, dEnd)
  Me.Text22.Value = dTaken

End If
End Sub


0
Utf
7/3/2007 7:34:00 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1369 Views

Similar Articles

[PageSpeed] 21

Your code only looks at the data from the first row.

Realistically, though, you don't want to do that in the form's Load event. 
Instead, since you don't need to store the calculated value, the Total Days 
text box doesn't need to be bound, so you can put the calculation as the its 
ControlSource property.
-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Joanne" <Joanne@discussions.microsoft.com> wrote in message 
news:DEAEDF96-E468-4FE0-9563-AC129939E751@microsoft.com...
> Hello,
> I have a subform in a table which has the following controls:
> Type of Day....Begin Date.....End Date....Total Days
>
> There may be several rows of information in these controls.  The form is
> used to calculate the number of vacation days a person has taken.  I have
> written some code that uses that DateDiff function to give me the 
> difference
> between Begin Date and End Date.  The result is supposed to go into the 
> TOTAL
> Days control.  My problem is that it appears that the calculation is
> performed only once. So for instance, if John Doe has as his first row 
> Begin
> Date 3/13/2007 and End Date 3/15/2007, the Total Days calculates 2 days,
> which is correct, but then on the next line if he has 4/15/2007 as the 
> begin
> date and 4/21/2007 as the end date, it still give me "2" as the total 
> days.
> Here is the code I wrote:
>
> Private Sub Form_Load()
> Dim dTaken
> Dim dStart
> Dim dEnd
> dEnd = Me.OLP_End_Date1.Value
> dStart = Me.OLP_Begin_Date1.Value
>
> If Me.Type_of_Day.Value = "Ill" Or Me.Type_of_Day.Value = "Vacation" Then
>  dTaken = DateDiff("d", dStart, dEnd)
>  Me.Text22.Value = dTaken
>
> End If
> End Sub
>
> 


0
Douglas
7/3/2007 7:43:43 PM
I do have it as an unbound text box in the form and I have VBA code.  
Sometimes there is only one row and sometimes there might be 7 or 8 or more 
rows, depending on how many vacation days an employee has taken.  How can I 
code a text box that may or may not be there?  What I'm trying to do is if 
there are 7 rows of days, then each row will be calculated separately.  But 
as I said, the text box only shows up if the row shows up.  I hope this 
clarifies it.

"Douglas J. Steele" wrote:

> Your code only looks at the data from the first row.
> 
> Realistically, though, you don't want to do that in the form's Load event. 
> Instead, since you don't need to store the calculated value, the Total Days 
> text box doesn't need to be bound, so you can put the calculation as the its 
> ControlSource property.
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "Joanne" <Joanne@discussions.microsoft.com> wrote in message 
> news:DEAEDF96-E468-4FE0-9563-AC129939E751@microsoft.com...
> > Hello,
> > I have a subform in a table which has the following controls:
> > Type of Day....Begin Date.....End Date....Total Days
> >
> > There may be several rows of information in these controls.  The form is
> > used to calculate the number of vacation days a person has taken.  I have
> > written some code that uses that DateDiff function to give me the 
> > difference
> > between Begin Date and End Date.  The result is supposed to go into the 
> > TOTAL
> > Days control.  My problem is that it appears that the calculation is
> > performed only once. So for instance, if John Doe has as his first row 
> > Begin
> > Date 3/13/2007 and End Date 3/15/2007, the Total Days calculates 2 days,
> > which is correct, but then on the next line if he has 4/15/2007 as the 
> > begin
> > date and 4/21/2007 as the end date, it still give me "2" as the total 
> > days.
> > Here is the code I wrote:
> >
> > Private Sub Form_Load()
> > Dim dTaken
> > Dim dStart
> > Dim dEnd
> > dEnd = Me.OLP_End_Date1.Value
> > dStart = Me.OLP_Begin_Date1.Value
> >
> > If Me.Type_of_Day.Value = "Ill" Or Me.Type_of_Day.Value = "Vacation" Then
> >  dTaken = DateDiff("d", dStart, dEnd)
> >  Me.Text22.Value = dTaken
> >
> > End If
> > End Sub
> >
> > 
> 
> 
> 
0
Utf
7/6/2007 2:08:04 PM
Not sure what you mean by having VBA code.

What I'm suggesting is that the ControlSource for the text box be

=DateDiff("d", [BeginDate], [EndDate])

If you're trying to set the text box's value in VBA code, it will apply to 
all the text boxes when they're unbound.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Joanne" <Joanne@discussions.microsoft.com> wrote in message 
news:58FA5BB6-69D6-487C-9CF7-A15FE6E5A0E4@microsoft.com...
>I do have it as an unbound text box in the form and I have VBA code.
> Sometimes there is only one row and sometimes there might be 7 or 8 or 
> more
> rows, depending on how many vacation days an employee has taken.  How can 
> I
> code a text box that may or may not be there?  What I'm trying to do is if
> there are 7 rows of days, then each row will be calculated separately. 
> But
> as I said, the text box only shows up if the row shows up.  I hope this
> clarifies it.
>
> "Douglas J. Steele" wrote:
>
>> Your code only looks at the data from the first row.
>>
>> Realistically, though, you don't want to do that in the form's Load 
>> event.
>> Instead, since you don't need to store the calculated value, the Total 
>> Days
>> text box doesn't need to be bound, so you can put the calculation as the 
>> its
>> ControlSource property.
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Joanne" <Joanne@discussions.microsoft.com> wrote in message
>> news:DEAEDF96-E468-4FE0-9563-AC129939E751@microsoft.com...
>> > Hello,
>> > I have a subform in a table which has the following controls:
>> > Type of Day....Begin Date.....End Date....Total Days
>> >
>> > There may be several rows of information in these controls.  The form 
>> > is
>> > used to calculate the number of vacation days a person has taken.  I 
>> > have
>> > written some code that uses that DateDiff function to give me the
>> > difference
>> > between Begin Date and End Date.  The result is supposed to go into the
>> > TOTAL
>> > Days control.  My problem is that it appears that the calculation is
>> > performed only once. So for instance, if John Doe has as his first row
>> > Begin
>> > Date 3/13/2007 and End Date 3/15/2007, the Total Days calculates 2 
>> > days,
>> > which is correct, but then on the next line if he has 4/15/2007 as the
>> > begin
>> > date and 4/21/2007 as the end date, it still give me "2" as the total
>> > days.
>> > Here is the code I wrote:
>> >
>> > Private Sub Form_Load()
>> > Dim dTaken
>> > Dim dStart
>> > Dim dEnd
>> > dEnd = Me.OLP_End_Date1.Value
>> > dStart = Me.OLP_Begin_Date1.Value
>> >
>> > If Me.Type_of_Day.Value = "Ill" Or Me.Type_of_Day.Value = "Vacation" 
>> > Then
>> >  dTaken = DateDiff("d", dStart, dEnd)
>> >  Me.Text22.Value = dTaken
>> >
>> > End If
>> > End Sub
>> >
>> >
>>
>>
>> 


0
Douglas
7/6/2007 6:08:09 PM
Reply:

Similar Artilces:

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

No content in message field anymore
For the last two days when I open my email client I see no content in the message field. Each email is blank with an attachment paperclip in the upper right hand corner. When I click the paperclip I have the choice of a txt file (which will not open up) or an htm file which will open. This is driving me crazy and I don't want to get rid of windows mail...any suggestions on how to fix this? Which antivirus are you running? Some of them cause problems with Windows Mail over time. =20 Try running the various repair functions in the WMUtil program: http://www.oehelp.com/...

Calculated Field in form to table
Can you take a calculated field from a form and pull it in to a table?? ...

cmbo box on subform fills fields on form when chkbx is yes
I have a multi-tabbed form that details bid data ranging from pricing to contact info to departmental notes. Page two has a subform listing general contractors to whom we bid. One bid may have 10-15 contractors listed. When a purchase order is issued from one of these contractors I select a checkbox indicating that GC. I would like the opening page on this tabbed form to show the address and contact info for that GC when the checkbox is yes. Is this possible? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200803/1 On Mar 17, 3:04=A0p...

No calculation on new machine
I run XP Home with Office 2002 I have just installed my programs to a new machine. Excel is behaving differently on the new machine: Observed so far: i) I cannot select a cell by moving with the arrow keys. ii) I can insert a calculation, SUM for instance, into a cell and I get a 0.00 result. iii) Format > Cells provides three options, Border, pattern and protection. The old machine additionally gave me number, alignment and font. What have I miised? Thanks, Robin Chapple Hi Robin, Check your status bar that you do not have SCRL on the right side to indicate Scroll Lock (there i...

Field Options and Combo Lists?
Wondering if anyone knows how to make this work. I have two fields in a form, both Combo Boxes. I want the Value List in the first box to determine what the Value List in the second box will be. For example, if the first box is all State Names, if you select California, the second box provides all of the City names for that state. However, if you change the first box to Utah, the second box provides all the City names for Utah. In the afterupdate event of the states combobox write code to adjust the rowsource of the city combobox. Something like: Me.combo_STATE.RowSource = &quo...

Automatically filling in "full name" field
I would like to add the "fullname" field to the Contact form. Is there someway to make this field automatically fill in with the information from "firstname" + "middlename" + "lastname" + "suffix"? If this can be automatically filled in, could the field later be manually changed? You will find that if you add the fullname field to the form, it will be greyed out, you can't type into it. It will be populated after you create a new contact and enter the first and last names and save the contact. And it will change if you change the f...

two line bar graph
i have a two line bar graph. one of the graphs is just a straight line that shows .1 below the avg, so it changes as diff data is entered. i need the label to move with the line as the line moves up or down. the way i have it now is by just using the text box which doesnt move as the line moves. is there some way to tie the label in with the line movement? Remove the textbox, then add a data label to one of the endpoints of the line series. The data label will move with the point. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/...

table and form not calculated in synch
Help says to open a piviot table but I am having trouble making my columns add up to total for each indivudual ID. The totals are being calculated only in th e form view which does not up date the original table and does not transfer to a report. How can I get a feild to calculate specific feilds so that I can run these reports? ...

Excluding Duplicates in One Field
Hi, I'm stuck. I have two fields, fName and fEmail. Many of the fEmail fields have duplicates because often people in the same house use the same e-mail address. I need to query the data so the result does not contain the duplicate e-mail addresses. For some reason, "unique values" still pulls up the duplicates. I know if I exclude one of the duplicate e-mail addresses, then I also end up exluding the name in the same record, but so long as the output has at least one of the names, along with every record for which there is a unique e-mail address...

Adding Values From Different Tabs
Is there a way to add values from different tabs on the same spreadsheet? For example A2 from tab 1 and A2 from tab 2 -- Flipkid2 ------------------------------------------------------------------------ Flipkid2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17984 View this thread: http://www.excelforum.com/showthread.php?threadid=398184 Assuming the name on Tab 1 is Sheet1 and Tab 2 is Sheet2, use thi formula: =Sheet1!A2+Sheet2!A2 note the name of the sheet is followed by "!" to indicate it is a shee name. HTH Bruc -- swatsp0 ------------...

Multi-field primary key, no dupes
I can select multiple fields and make a primary key indexed with no duplicates. But I just realized my criteria for "no dupes" holds only if another field is empty (no value). For example, if my primary key is: Shirts-Mens-Style102-Large-Green-05Jan08 then I can't enter another order for the same product on the same day. That's good - unless the order's been shipped and another one is needed. So no duplicates, but only as long as the ShipDate field for that record is empty. Can this be done easily? Or should I look into a different way to prevent duplicates? Ed ...

Use Outlook Client when domain user name different with email acco
Hello, I have a question about using Outlook Client. If the domain user login name is different with email account which belong to a same person, is it possible to install / login with the Outlook client? e.g domain user : degdom\apple email account: applewatson@deg.com One of my client is facing in this situation. I just want to have some preparation before install Outlook Client for him. Thanks for any help! Hi Huimc, It shouldn't be a problem if the AD user is the same used to start Outlook and connect to CRM. You may want to check the email adress in CRM if the ema...

Checking which fields changed
Hi Is it possible to identify individual fields changed by a user during editing on a form before or immediately after the form is saved? Thanks Regards On May 9, 4:25 pm, "John" <J...@nospam.infovis.co.uk> wrote: > Hi > > Is it possible to identify individual fields changed by a user during > editing on a form before or immediately after the form is saved? > > Thanks > > Regards I assume you mean what data was changed when you save the record? If so look at the OldValue property of your text boxes etc. Take a look at what Allen Browne has at ...

Help with Calculation #2
Hi, I have the following XLS with 3 sheets: Credits, Inventory & Debits. Credits sheet (Stock sold) columns: 'Product Code', 'Product' (Text field) & 'Quantity' Inventory fields: 'Product' (Text field), 'Company', 'Supplier', 'Product Code', 'Quantity', 'Cost Price' & 'Retail Price'. Debits Sheet (Stock bought) columns: 'Product Code', 'Product' (Text field), 'Quantity' & 'Total cost'. New stock entered as 'Product Code', 'Product', 'Quantity&#...

Change order of fields in a report to a custom arrangement
In a report, I want to list sections in a certain way that is not alphabetical or numerical. Is there a way to do this? Curerntly, it is alphabetical as below: **Staff Section** Chief of Staff Command Counsel G1 G2 G3 Public Affairs Special Staff ** I would like for the fields to be organized as below: ***Staff Section*** G1 G2 G3 Chief of Staff Command Counsel Special Staff Public Affairs Thanks! Glen Try going to Sorting and Grouping. It is one of your buttons on the command bar. You should be able to set it anyway you wish. -- Milton Purdy ACCES...

Calculate from another sheet
If i in a sheet have a1=1 , b1 =2 , c1 = 3 , d1 = 4, e1 = 5 and so on and in a2 = 100, B2 = 100, c2 = 100 , e2 =100 ans o on Then in another sheet i write maybe 4 then i want a cell there have a sum from 1 to 4 In this case from a2 to e2 if I write maybe 2 The a sum from a2 to b2 Can i do this? If 4 is A2 to E2, and 2 is A2 to B2, presumably 3 is A2 to about half way between C2 and D2? It sounds as if you want the OFFSET function; details in Excel help. -- David Biddulph "alvin Kuiper" <alvinKuiper@discussions.microsoft.com> wrote in message new...

OWA
OWA text field is always 'grayed out' - on new messages and replies. Small "x" button in upper left of text field seems to have no functionality. Subject field is OK. Bummer - do you have a question? If yes, then try asking it in an Exchange news group as Outlook Web Access is a function of Exchange, not Outlook. Microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted w...

two decimals - remove percentage sign
Hello everyone, I have percentages that i'm displaying in graphs with data labels. I'd like to remove the % sign so it is easier to view the data labels because the graphs are small, and i'd like to do it with custom formatting because there is quite a lot of data that i dont want to convert to whole numbers. I think the best way to do this is to somehow use custom formatting in format cells -->number-->custom to multiply by 100 and display it that way. is that possible? Thanks for the help! Adam ...

help to compare two list
I have a file with all the open orders for production and I have another file with orders which is already sent to production. I want to find out from the first list, which orders are not send for production. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Take a look at http://cpearson.com/excel/duplicat.htm In article <itty.vneun@excelforum-nospam.com>, itty <itty.vneun@excelforum-nospam.com> wrote: > I have a file with all the open or...

Deleting system locked fields
Does anyone know how I can get rid of the "Is revenue system calculated?" field on the Opportunities form? We always have user-provided values, and therefore have no need for this field, but I can't delete it. Anyone know how I can get around this? You can not delete those fields. But you can change the default value to "user provided". WIth some javascipt you should be able to hide it for the users. HTH, Frederic - Travi@ta www.microsoft-dynamics-crm.eu WiLLerZ schreef: > Does anyone know how I can get rid of the "Is revenue system > calculated?"...

Trying to configure two exchange accounts with Office 2007 and Exc
Hi, A few months ago at work we switched servers. We sent from the Exchange 2003 to 2007 and all computers had to be re-added to the domain. While we've had some problems here and there, there is one I have yet to solve. We have a computer running Vista, I think 32-bit, that we to configure two exchange accounts on. We use Office 2007 Enterprise edition. And while I can get both accounts working within the same view in Outlook 2007, I can't get them configured like I had before. One account is my own personal bus account and the other is a shared account. ...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

How to split a field into 2 fields?
I imported a spreadsheet from Excel and would like to split one field into two fields. Many of the records in Field1 have a note in parenthesis ( ). If a record has anything contained in parenthesis, I would like to move that data to Field2. What's the best way to accomplish this? To get the word inside the parenthesis try something like SecondWord: IIf(InStr([MyLeeter],"("),Mid([MyLeeter],InStr([MyLeeter],"(")+1,Len([MyLeeter])-InStr([MyLeeter],"(")-1),"") FirstWord: IIf(InStr([MyLeeter],"("),Left([MyLeeter],InStr([MyLeeter],&q...