Not sure why this code isn't working - calculates datediff

Hello, I have a subform in Access 2003 with these fields, type of day, 
OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the 
total day taken for that particular vacation request, so if a  person took 
12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate as 
1.  What's happening is that it does calculate but all of the rows calculate 
to the same number.  If I put the cursor in the second row and that row 
happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2 
calculates as 2.  Then if I put the cursor back in the first row, everything 
calculates as 1.  I realize I have the even on "Got Focus" but I've tried it 
with every possible trigger and it always calculates the same way.  Any help 
would be appreciated.
Private Sub txtTotalOLPTaken_GotFocus()
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)
  MsgBox dStart & " " & dEnd
  Me.txtTotalOLPTaken.Value = dTaken
  
End If

End Sub
0
Utf
12/16/2009 6:27:01 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
826 Views

Similar Articles

[PageSpeed] 17

"Joanne" <Joanne@discussions.microsoft.com> wrote in message 
news:6D93204B-9B8A-487C-BEE2-1E733BC6665B@microsoft.com...
> Hello, I have a subform in Access 2003 with these fields, type of day,
> OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the
> total day taken for that particular vacation request, so if a  person took
> 12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate 
> as
> 1.  What's happening is that it does calculate but all of the rows 
> calculate
> to the same number.  If I put the cursor in the second row and that row
> happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2
> calculates as 2.  Then if I put the cursor back in the first row, 
> everything
> calculates as 1.  I realize I have the even on "Got Focus" but I've tried 
> it
> with every possible trigger and it always calculates the same way.  Any 
> help
> would be appreciated.
> Private Sub txtTotalOLPTaken_GotFocus()
> 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)
>  MsgBox dStart & " " & dEnd
>  Me.txtTotalOLPTaken.Value = dTaken
>
> End If
>
> End Sub


If you have an unbound text box on a continuous form, it always has the same 
value for all records.  Use a calculated text box instead, with its 
ControlSource set to something like this:

    =IIf(Type_of_Day In("Ill","Vacation"), DateDiff("d", OLP_Begin_Date1, 
OLP_End_Date1), 0)

Please note: the above line will have been broken onto multiple lines by the 
newsreader, but it should be entered all on one line in the ControlSource 
property.

Alternatively, you could create a calculated field in the form's 
RecordSource query, and bind the text box to it.

If I've understood correctly, you do not need the event procedure you posted 
at all.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
12/16/2009 7:36:29 PM
Hi joanne,
please use # signs with the dates so that access could identify that these 
are dates.otherwise Access would think you are dividing 12 by 4 and dividing 
the result with 2009. so, replace the following line
dTaken = DateDiff("d", dStart, dEnd)
with
dTaken = DateDiff("d", #dStart#,#dEnd#).
Try this and see
from
chanakya
Baruva


0
Utf
12/17/2009 2:55:02 PM
"chanu" <chanu@discussions.microsoft.com> wrote in message 
news:794EAACF-69FF-40BB-82B9-0EB6A1869783@microsoft.com...
> Hi joanne,
> please use # signs with the dates so that access could identify that these
> are dates.otherwise Access would think you are dividing 12 by 4 and 
> dividing
> the result with 2009. so, replace the following line
> dTaken = DateDiff("d", dStart, dEnd)
> with
> dTaken = DateDiff("d", #dStart#,#dEnd#).
> Try this and see


I'm sorry, but this is wrong.  If dStart and dEnd are variables holding Date 
values, then it would be wrong to try to enclose them in the "#" delimiter, 
which is used only for date literals.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
12/17/2009 7:59:25 PM
=?Utf-8?B?Sm9hbm5l?= <Joanne@discussions.microsoft.com> wrote in
news:6D93204B-9B8A-487C-BEE2-1E733BC6665B@microsoft.com: 

> Hello, I have a subform in Access 2003 with these fields, type of
> day, OLPEndDate, OLPBeginDate. At the end of each row, I want to
> calculate the total day taken for that particular vacation
> request, so if a  person took 12/1/2009-12/2/2009 then the last
> field "TotalOLPTaken" should calculate as 1.  What's happening is
> that it does calculate but all of the rows calculate to the same
> number.  If I put the cursor in the second row and that row 
> happens to have 12/4/2009-12/6/2009, then even the row with
> 12/1-12/2 calculates as 2.  Then if I put the cursor back in the
> first row, everything calculates as 1.  I realize I have the even
> on "Got Focus" but I've tried it with every possible trigger and
> it always calculates the same way.  Any help would be appreciated.
> Private Sub txtTotalOLPTaken_GotFocus()
> 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)
>   MsgBox dStart & " " & dEnd
>   Me.txtTotalOLPTaken.Value = dTaken
>   
> End If
> 
> End Sub
Your problem is that you are calculating a value for display in an 
unbound control. In a continuous form all instances of an unbound 
control take on the value of the current record.

Move the calculation to the query that underlies the form, 




-- 
Bob Quintal

PA is y I've altered my email address.
0
Bob
12/17/2009 10:50:48 PM
Reply:

Similar Artilces:

Error Code 0x80072EFF
I had some problems wit Trojan.Gadja.Injected that ruined the file Userinit.exe. I ran sfc /Scannow and then MalwareBytes and everything dissapeared. But I still can't access the Windows Update site, neither through the Icon in Windows or through the Microsoft Webpage. Everything that has Windows update int the address-feild opf IE8 ends up on a page where it sais it can't be shown. I notice that ther is a warning down to the left in IE8. Error on page. Soem Java script error perhaps. The Windows Defender gives me the same erroro code when I try to update the files. ...

Explorer.exe stopped working and no longer works.
Hi, im having an issue here. one day my explorer.exe stopped working and would not load. Even task manger wont launch. When I go to Windows\exeplorer.exe and launch it manualy it starts but crashes immedialty. Even after reboot it crashes upon startup. Ive checked the regedit and the shell area says explorer.exe like it should. I dont know whats going on but its fustrating so far im able to use the comandprompt to do things like taskkill, msconfig, and regedit. Also when i click a link after using google search, the listings all redirect me to a random ad site. So im assuming all thi...

Does CRM 4.0 work well with Office 2003?
Hi all, We're looking at upgrading from CRM 3 to 4.0. Most of our users are at MS Office 2003 and we'd like to keep that way for now. I understand 4.0 is supposed to be "compatible" with MS Office 2003 but I've been researching the issues. Are there any known compatibility issues between CRM 4.0 and MS Office 2003? Will using 2003 instead of 2007 cause any loss of functionality in CRM? (Such as disabling the tracking token.) Thank you, Mohamed Both Outlook 2003 and 2007 works fine with Microsoft CRM 4.0. We have a mix of Outlook versions being used everyday for the l...

Code to copy cells from multiple worksheets
I have a workbook that has about 100 sheets. I need to copy cells c5, e5, and m5 from each sheet into a summary sheet. Can anyone provide me with a macro or point me where to look for the correct code that will automate this process? I expect that I will be adding about 50 additional sheets before I am done. Thanks. Hi Chad This code will copy cells C5, E5, and M5 from each sheet of your workbook (starting from sheet 2) into the first sheet (to col A, B & C, starting in row1). If you want to start copying in row 2 of your summary sheet, set j =2 ======= Sub CopyCellsToSheet1() Ap...

Like criteria on a combo box ot working the way I want... Please help!
All, Below is a the standard code I use in a combo box. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.Form.AllowAdditions = False The query used to find the data is... SELECT qryLister.llListingID, qryLister.llTerritoryName FROM qryLister WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory Name:]+"*")); All works fine for the first look-up. But in order to refresh the combo box so it allows one to &...

Outlook 2007 on Vista: Toolbars and Menus not working
Hi everyone, I'm running MS CRM3.0 through Outlook 2007 on my Vista Business machine. I've run the updates on the server before installing the client. CRM displays ok within Outlook but requires a username/password before opening. The URL is listed in the Trusted Sites of Internet explorer. I've tried entering the URL as http://servername:port but it just lists as http://servername. The CRM menu opens and the buttons on the toolbar highlight and open. However, when I click on something (e.g. Options in the Menu bar) nothing happens. Also, when opening an outlook contact, I used...

the date format is not working ,sort by date doesn't work.
I have this Excel spreadsheet, I took the year from one column, and the month from another column and put it in one column together, it looks like AUG 1989, but when i try to sort by Date it doesn't. I'm sure the format is incorrect. Rosa How did you combine the month and year? Did you Concatenate them? Sounds like these are now treated as text. Try Data>Text to Columns>Next>Next>Column Data Format>Date>MDY and Finish. Gord Dibben Excel MVP On Mon, 12 Sep 2005 14:16:02 -0700, "Rosa Campos" <RosaCampos@discussions.microsoft.com> wrote: >I...

not between operator not working
Hi, I am trying to query my data and I want to exclude a whole bunch of values. However when I use not between x and y, it still includes it. I am using MS Access 2007 Any idea? -- Have a nice day Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. Also some examples of the data that isn't being exluded. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Cricri" wrote: > Hi, I am trying to query my data and I want to exclude a whole bunch of...

COffLineABScanTask::Work
This morning one of my clients exchange servers was not responding and, since I was diagnosing over the phone, required a reboot to fix the problem. The box is an HP svr with raid 5 disks running sbs 2003 spanish. It serves as an exchange server and AD controller, nothing else. The key appears to be an event at 5:06, where it says one of the operator tasks is blocked in function COffLineABScanTask::Work. Exchange correctly executes its nightly tasks without any errors. At 4:40 it launches the MAD supervisor process, 25 minutes later it's blocked. When the users start at 8:30 exchange...

Problem with VBA code on command button
I have a form that has a combo box to select a month. The name of the combo box is txtmontha (I realise it would have been better to name it cmb???) The is a command button that when clicked should open a form where the date selected from the combo box is the same value as the date on the form. Here is the code behind the command button: Private Sub cmdopenrecord_Click() On Error GoTo Err_cmdopenrecord_Click Dim strqtr As String strqtr = Me.txtmontha.Value Forms!frmMain!SubForm1.SourceObject = "frmFDA" Forms!frmMain!SubForm1.Form.RecordSou...

Ctl-insert & ctl-delete now working in Outlook 2003
Hi, Ever since we upgraded to Outlook 2003 at work (from 2002) I have not been able to use the keyboard shortcuts for cut and paste that utilize the insert and delete keys. Namely, I can't copy with a ctl-insert and I can't cut with a ctl-delete. The other methods, including ctl-c, ctl- x and ctl-v do work. But I liked using the other keys better. Were these options "retired" for Outlook 2003? Thanks, Rob Excuse me! The Subject should read " Ctl-insert & ctl-delete NOT working in Outlook 2003". I will re-post. On Jun 6, 1:09 pm, AZ Rob <rmaye...@cox.n...

Kirk Allen Evans---? how do I use your code to update my xmlDocument?
Kirk, The other day you very kindly explained how the client/server thing works. May I ask just one more question? Could you give me an example of what code I would put in the client html to post back the user input to the server xmlDocument? You gave me: One way is to use the Request.Form collection to read the values directly out of the post. System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml")); if(!IsPostBack) { System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml&...

Not Sure
Hi, Can anyone help? If I use a Form selecting from the range "make" I want the next Form to be able to pick from the "model" range of that make. So if I picked Ford in the 1st form, the 2nd form will only give me the options of Mondeo LxTD or Mondeo Aspen. The other information I can already obtain using Vlookup() but I cannot find a way of inputting the "model" range into the Vlookup() In cell A1 the form will bring in the option picked (ford Or Vauxhall) What I need is in cell B2 I want to be able to pick from only the cars made by ford or vauxhall...

highlight cells not working with wireless mouse
Excel 2007- When highlight a cell using a wireless mouse, it highlights more than one cell. For example, I want to highlight cell D7; but it also highlights D8,9 and 10. I can't get it to highlight the one cell. Is this a problem with Excel 2007? No, it is a problem with your mouse. I have a wireless mouse and have no trouble selecting a single cell. Is that what 'highlight' means? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "cpajdc" <cpajdc@discussions.microsoft.com> wrote in message news:A1A4E886...

Form Locking Code Encrypting
Where would I go to find some software that I can use on my DB so as stop somebody copying it. I want to put an Encrypted code on one form that locks all Data on that and 2 other forms will not work unless there is the right code on the main form.................Thanks For any Help.....Bob rofl Access MDB is not secure; you should use SQL Server with Access Data Projects; it's not like someone can copy your database lol On Apr 29, 5:49 pm, "Bob" <x...@xx.xx> wrote: > Where would I go to find some software that I can use on my DB so as stop > somebody copy...

Do You Want To Know For Sure That You Are Going To Heaven? The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news
The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news is that you can know for sure that you are going to Heaven which is described in the Holy Bible as a beautiful place with no death, sorrow, sickness or pain. God tells us in the Holy Bible how simple it is to be saved so that we can live forever with Him in Heaven. "For if you confess with your mouth Jesus is Lord and believe in your heart that God raised Him from the dead, you WILL BE SAVED." (Romans 10:9) Over 2000 years ago God came from ...

Out of Office- not working externally
Hi, OoO suddendly stopped working to emails sent from external domains (ie OoO is only working within our own exchange domain). I then done the following: Using the Exchange System Manager, open Global Settings > Internet Message Formats, right click "default" (on the right hand panel). Select Properties > Advanced. Tick the "allow out of office responses" box. Click apply/ok. This box wasn't checked, but since I have checked it the problem still occurs. Does it take a while for it to be come effective? Anyone have any ideas? Thanks Hi, This should wor...

Textbox code for list of post
Hello I have Form name "Jamforelse" and inside this form contain post list. And one of the post is Bounden box and on this box code as the folowing: =getnum(Forms("Jamforelse"),"e",[e]) This code work correct when I open Form name "Jamforelse", but when I add this form "Jamforelse" inside another form that have name "form2" then this code =getnum(Forms("Jamforelse"),"e",[e]) not work. I try to change on it as the folowing, but didn't work =getnum(Forms("form2"!"Jamforels...

Not sure.
Ok, Kinda new a formuals for Execl 2003 / 2007 I have catalog numbers in column A B LNL FNM BLB TNT KBA BER these are cross reference numbers for a fuse company, what formula would you use that if you choose LNL and it would reference cell "B" FNM, Hi Old Guy I would use VLOOKUP, check it out in Excel Help. Regards John ( Old Guy too ) "Old Guy" <OldGuy@discussions.microsoft.com> wrote in message news:E10D6AF2-F61D-481D-99C4-1D06F3F57CF8@microsoft.com... > Ok, Kinda new a formuals for Execl 2003 / 2007 I have catalog numbers in > co...

CRM 4.0 State code and Status code error
Hello, I have upgraded a client from 3.0 to 4.0, and am getting errors whenever they try to reopen a closed opportunity. This occurs for any opportunity, regardless of whether it was closed before the upgrade or created new. It occurs with any Role, including Sys Admin. The Opportunity entity has been customized, but only using the built in customization features, no custom code at all. It was renamed to become "Proposals", however. Has anyone out there seen this error before? Any resolution? The generic error is: "State code or status code is invalid. Sate code is inva...

Out of office not working for e-mail alias
Hello all, I have a user that has set out of office through OWA. It is working for his primary SMTP account, but not for the alias. Any ideas on how I can get it to work for all SMTP addresses for a user. We are using Exhange 2000 SP3 On Fri, 22 Jul 2005 16:21:16 GMT, "Marc Baker" <mbaker@selectsa.com> wrote: >Hello all, > >I have a user that has set out of office through OWA. It is working for his >primary SMTP account, but not for the alias. Any ideas on how I can get it >to work for all SMTP addresses for a user. We are using Exhange 2000 SP3 > ...

Do You Want To Know For Sure That You Are Going To Heaven? The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news
The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news is that you can know for sure that you are going to Heaven which is described in the Holy Bible as a beautiful place with no death, sorrow, sickness or pain. God tells us in the Holy Bible how simple it is to be saved so that we can live forever with Him in Heaven. "For if you confess with your mouth Jesus is Lord and believe in your heart that God raised Him from the dead, you WILL BE SAVED." (Romans 10:9) Over 2000 years ago God came from ...

Trusted Code Settings.
Hi I was wondering if anyone could help me. We currently have moved to outlook xp and we have a visual basic 6 program that sends email.Since moving to outlook xp when the visual basic program sends mail these prompt box's appear which tell there is a program trying access mail and you have yes no options. I was on the microsoft site and found a security features administrive patch which we downloaded you have a couple of options on this you can set the security settings for each user manually which works fine when we do it the boxs dont pop up.But There is another option that ...

Works 6.0 is there a workbook function?
I had Windows ME and the version of excel that came with that had the workbook function and I loved it. But I bought a new computer that has XP and microsoft works 6.0 and I can't find a workbook function. Lapita I am not familiar with a WORKBOOK Function -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS lapita wrote: > I had Windows ME and the version of excel that came with > that had the workbook function and I loved it. But I > bought a new computer that has XP and microsoft works 6.0 > and I can't find a ...

Arrange code
Hello I have this code (below) which works perfect. Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex = 3 Then Range("A105").Interior.ColorIndex = 5 Range("A105").Value = IIf(Range("F102&quo...