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
1029 Views

Similar Articles

[PageSpeed] 11

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

Input mask
Can anyone please help me with setting an input mask for a cell phone field in the format +00 00 111 2222 or +00 00 111 222. Would really appreciate some guidance on this. ...

Office 2004 on Intel works, for a while, then apps will not launch
Hello all! I searched previous posts extensively before posting. But my problem does not seem to come from corrupted fonts or preferences. Problem : Office apps work fine after a reboot, but eventually, I become unable to relaunch them. I have to reboot again to get them to work. Details : MacBook Pro, 1gig of ram. Office 2004 11.2.6. OS 10.4.7. I'll open Entourage, work a bit, close it, and then I can't open it anymore. The splash screen does not even appear completely (white rectangle). Console gives : ***** Date/Time: 2006-08-09 12:03:11.089 -0400 OS Version: 10.4.7 (...

Excel 12, insert chart does not work. Exel restart. Why?
When I insert chart and select chart data, MS Office encounters a problem and need to close. Another thing: On chart tools the desings are empty. Any solution? In article <A678D3D8-566D-46A1-8A19-5F85D34BB2C8@microsoft.com>, tujauja@discussions.microsoft.com says... > When I insert chart and select chart data, MS Office encounters a problem and > need to close. > Another thing: > On chart tools the desings are empty. > Any solution? > Office 12 is still in beta. You should have received information from MS about where and how to post questions about it. -- Rega...

Why does Autoexpand in combo box not work with accented characters?
To quote the Access online help: You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box. This works fine when I use the normal English alphabet, but fails if I type an accented character as the first character of a name in a foreign language such as Hungarian. Is there any way to make AutoExpand ...

Access 2007 ControlSource code in Report causing shut down
The following code worked successfully in Access 2002 & 2003 but for some reason, the lines that contain ControlSource are causing Access 2007 to shut down and error out. Any suggestions you have would be greatly appreciated. In short, I’m using a combo box on a form “ReportCenter” to change the GroupLevel and textbox values on a report. If I comment out the lines with ControlSource the code runs successfully, if any one of them are uncommented, Access 2007 shuts down and restarts. 'Group 1 If Forms![ReportCenter]!T2GroupCheck1 = True And Forms![ReportCenter]!T2GroupDescendC...

java srcipt not working, getting about blank page
i have windows xp recently i have been getting a blank page behind the page im trying to open, mainly pogo games or playsite games, i have all new updates from microsoft installed, but i cant change calender set up either, what di i need to do This newsgroup is for questions related to Microsoft Publisher. You would be better off posting your question to the appropriate group. -- JoAnn Paules MVP Microsoft [Publisher] "the big bud man 8" <the big bud man 8@discussions.microsoft.com> wrote in message news:BED6D77A-DD80-48B8-A58A-1FC98613ED2A@microsoft.com... >i hav...

XP-SP3 How can I be sure to keep it.
I currently have a Dell Reinstallation CD for XP Pro - SP2. I recently had to use it and was able to reinstall Windows without any problems. After installation I used the Windows Update site to get all the current updates. I believe I've read here that after July, 2010 SP2 will no longer be supported. If I have use my OEM disk after July will I still be able to download Service Pack 3 from Windows Update? or should I go ahead and download the iso file from http://www.microsoft.com/downloads/details.aspx?FamilyId=2FCDE6CE-B5FB-4488-8C50-FE22559D164E&displaylang=en and c...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

Physical Inventory Does Not Work With Matrix Items.
The physical inventory feature in RMS is not usable with matrix type items. It is impossible to filter the inventory count down to a managable number of items for a matrix items. The items I sell consist of a model name, a size, and a color. The matrix lookup code (model name) is not available as a filter. When I try to filter the physical inventory list I get thousands of irrelevant items mixed in with what I want to inventory. I would suggest that the item lookup code be added as a filter for the physical inventory. I would also suggest that when you make a matrix inactive that it ...

Custom Tool bar button on Contacts/Leads/Account Forms Intermittently not working.
Hello CRM gurus, I have a weird issue with a clients web CRM 3 client. I have modified the 'isv.config.xml' and 'isv.confg' files to add a custom button to the contacts/leads/account forms. What seems to be happen, at times (seems to be random) the picture of the button doesn't show up, but the text does and the you cannot click on the button anymore. It only happens to one client PC, as the rest of the client PCs work without any problems. I have run out of ideas as to why this is occurring. Are there any log files that I can look at? Any help on this problem will be g...

autocomplete addresses not working
I have just set up an account and imported my contacts via a windows csv file, now when I start a new mail and start to type in the 'To' field the address isn't automatically recognised. I have the settings correct in Advanced e mail settings. Can this be restored? You will restore it when you send enough messages to populate your autocompletion cache. That's what Autocomplete uses, not your Contacts. Autoresolution (Automatic Name Checking) uses your Contacts Data. -- Russ Valentine [MVP-Outlook] "Headtheball" <noanswer@hotmail.com> wrote in message ...

Message Size Calculation
Someone asked me how to calculate the storage used by email message: 1. If a email message which is 100K with a 500K attachment, send to 100 people - in total, how much storage is used? Anyone know how to do the calculation? :) It will take 600K plus some small amount of space (_my guesstimate_ 100byte) for each recipient, assuming all recipient mailboxes are on the same server and in the same database. April wrote: > Someone asked me how to calculate the storage used by > email message: > > 1. If a email message which is 100K with a 500K > attachment, send to 100 peopl...

Outlook Express Delete not working
Windows XP Home Outlook Express won't let me delete all my deleted emails at one time. I can only do won at a time. In the EDIT, the (delete all deleted messages) isn't highlighted. Is there a quick fix for this? Hi Jack, Open Outlook Express. Click Tooks at the top then click Options. Click Maintenance. Check the first option. Click Apply then click OK. Close Outlook Express. Open Outlook Express. Check the folder. -- thecreator "Jack" <Audioman1@myway.com> wrote in message news:ehU1082ALHA.98...

Exchange 2007
Hallo. we have enabled recipient filtering for our organization as we are requested to reject with a SMTP connection error messages addressed to non existent recipients as follows: EMC - Organization Configuration - Hub Transport - Anti-spam - Recipient Filtering enabled Block messages sent to recipients not listed in the GAL Still, if I telnet on port 25 I get a 250 2.1.5 Recipient OK message to a RCPT TO:non_existent_user@mydomain.com We have 2 Exchange 2007 SP1 servers: both are HubTransport+ClientAccess+Mailbox+UnifiedMessaging Exch1 is Windows Server 2003 Ent R2 SP2 ...

Why my setting doesn't work?
I am trying to write a program similar to "ping".Like the example provided in MSDN,I call setsockopt function to set SO_RCVTIMEO value for my raw socket.And before I make a call to recvfrom,I call getsockopt to ensure that the value ihas been correctly set,and it has.But recvfrom does not return automatically when the specified timeout is exceeded,conversely ,it gets stuck permanently if no packet can be received. If anyone knows why ,could you please tell me what I should do to get the result I want?Thank you in advance for you help. ...

Spam Program that works on Public Folders
Hello, I currently use SpamNet for spam filtering, and it works great. However, some my messages get delivered to a Public Folder (Outlook 2003 / Exchange 2003) and those messages are not filted by SpamNet. Does anyone know of a Spam program that works on emails that show as posts in an Exchange Public Folder? Thanks, Gerald Try www.spambayes.org Gerald S schrieb: > > Hello, > > I currently use SpamNet for spam filtering, and it works great. > However, some my messages get delivered to a Public Folder (Outlook 2003 > / Exchange 2003) and those messages are not filte...

Calculate Value of Cell From Pick List Choice
I need a formula that will allow the automatic population of a cell or group of cells based on selection from a user defined pick-list. Example: Pick List Value - Books Clothes Games Account code for Books = 123 Clothes = 456 Games = 789 Product type? Account Code? (From pick list) (automatically populated from pick list) Thanks, Use VLOOKUP. For examples, see: http://www.contextures.com/xlFunctions02.html http://www.techonthenet...

Tab key press does not work when MS flex grid presend on CFormView Based views
When dynamically create a MS Flex Grid based grid control on a CFormView based view in MFC application, tab will stop working after one press which will put the last columns header of the grid control in focus and you could not tab anymore. You have to move the carrot to another control on the view manually using mouse, then the tab will start working again. This only happens with the .NET VS 2003 and 2005 compiler. Hi Dennis, I have performed some tests on this issue and could repro the problem in our side. In VS2003 and VS2005, if the MSFlexGrid control created dynamically withi...

OWA not working
We are migrating from exchange 5.5 to exchange 2003. We used the exdeploy tool to guide us through the migration..joined the exchange 2003 server to an existing 5.5 site, moved the mailboxes over to the exchange 2003 server using system manager and then logged in with each users account so outlook would detect the new server. The only thing that is not working is the OWA. On each computer the web address defaults back to the old mail server login page. What needs to be done? ...

Date Calculation
Hi All, I have a date conversion problem. 1. I've calculated the difference between dates from field#1 and field#2 2. I want to add 90 days to the above calculation. ( result example ( 07/21/2006 ) 3 On result from # 2 above, I want to indicate the 1st of the month. ( eg: 07/01/2006 ) Please Help. Thanks in advance. Hi, 1- DateDiff("d", pastDate, recentDate) That results into a number of days! not into a date 2- 90 + someDate will result into a date 90 days after the given date 3- Day( 90 + someDate) ...

launch excel attachment from outlook
problem: double clicking on excel attachments in outlook email results in an error message "the system cannot find the file specified" excel appears to start (new) - but file never opens file can be saved and subsequently opened from excel 2007. file cannot be launched by double clicking on it. thoughts? Try this Office button>Excel options Advanced>General...ignore other applications... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "chris B" <chrisB@discussions.microsoft.com> wrote in message news:D484E8CC-5788-48A2-9307-D6381796F2FE@m...

Out of Office Assistant Doesn't Work
Hello, I have one user who put their Out of Office assistant on, but it is not replying to any incoming messages. She has no rules setup in her Outlook. The config is Exchange 2003 and she has WinXP with Oulook 2003. Thanks, George Define not working. Not working for "anyone"? Do you get an Out of Office message if you send a message to her? By default, Out of Office to the Internet is disabled, but any internal users should get the OOF message. -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) ...

vba code #6
why does this code not input a formula into cells e5 to end of data Range("D6").Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1").Select Do Until Range("E5").Select ActiveCell.FormulaR1C1 = "=R2C8/(RC[-2]-RC[-4])" ActiveCell.Offset(-1, 0).Range("a1").Select Loop Hi agw, Try the following change: Do Until ActiveCell.Address = "$E$5" Ken Johnson It looks like you're using column D to get the last used cell. If that's true: with activesheet .range("E5:E" &am...

Selecting printer from code
hi I have buttons on my forms that cause various reports to be printed. But, some of the reports are to be printed on one printer, and some on another printer. Both are connected to the computer. How do I do this? It guess it would be OK for the code to pull up the Print window, but I'd like it best if the code can actually select the correct printer. (Lexmark E330 or Canon i550) Any clues appreciated. -- Thanks Sophie You can have two reports, one set to print to one specific printer, the other (duplicate) report set to print to the second printer. But, then you have to m...

Using code to insert different formulas
[Excel 2003] I have sixteen different conditions which can exist. These relate to whether certain aspects are TRUE or FALSE. When taken together I have 16 unique "cases". These appear in cells A1:A60 and are labeled Condition1, 2, 3 etc... Condition16 Depending on the condition in each cell in column A, I need certain formulas to operate in adjacent cells in colomn B and C For example for Condition1 in A1, I need the formula in B1 to be "=D1*E1" and the formula in C1 to be "F1*G1/H1" Each formula referes to cells in the same row, so perh...