446,395 Members | 1,412 Online
Need help? Post your question and get tips & solutions from a community of 446,395 IT Pros & Developers. It's quick & easy.

# Table field not being populated by function

 P: 8 I have a module with the following function (modified from raskew's "age" function): Expand|Select|Wrap|Line Numbers Function fAge(dteStart As Variant, dteEnd As Variant) As Variant   If IsNull(dteStart) = True Then Exit Function   Dim intHold   As Integer Dim dayHold   As Integer        'correctly return number of whole months difference    'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement    'that returns -1 if true, 0 if false    intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))        'correctly return number of days difference    If Day(dteEnd) < Day(dteStart) Then       dayHold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)    Else       dayHold = Day(dteEnd) - Day(dteStart)    End If      fAge = LTrim(str(intHold \ 12)) & "" & LTrim(str(intHold Mod 12)) & " months " & LTrim(str(dayHold)) & " days "   End Function Essentially, when one inputs DateA, Field1 will calculate the difference between Now() and DateA (and display it as "X months X days"). I reference this module in the control source property of Field1: =fAge([DateA],Date()). However, while it works perfectly when viewing the form (i.e. upon entering DateA, Field1 displays the correct result), sometimes the Field1 will not populate with the result in the table, so when I go to Report1 that contains Field1, it is empty (despite refreshing, saving, and even restarting). Help?? Aug 13 '07 #1
7 Replies

 Expert Mod 10K+ P: 12,396 I have a module with the following function (modified from raskew's "age" function): Expand|Select|Wrap|Line Numbers Function fAge(dteStart As Variant, dteEnd As Variant) As Variant   If IsNull(dteStart) = True Then Exit Function   Dim intHold   As Integer Dim dayHold   As Integer        'correctly return number of whole months difference    'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement    'that returns -1 if true, 0 if false    intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))        'correctly return number of days difference    If Day(dteEnd) < Day(dteStart) Then       dayHold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)    Else       dayHold = Day(dteEnd) - Day(dteStart)    End If      fAge = LTrim(str(intHold \ 12)) & "" & LTrim(str(intHold Mod 12)) & " months " & LTrim(str(dayHold)) & " days "   End Function Essentially, when one inputs DateA, Field1 will calculate the difference between Now() and DateA (and display it as "X months X days"). I reference this module in the control source property of Field1: =fAge([DateA],Date()). However, while it works perfectly when viewing the form (i.e. upon entering DateA, Field1 displays the correct result), sometimes the Field1 will not populate with the result in the table, so when I go to Report1 that contains Field1, it is empty (despite refreshing, saving, and even restarting). Help?? By setting Field1's control source to an expression, it doesn't update to a table. You'll have to either set the control source of the Field1 to a field in the table and populate it in the AfterUpdate event of DateA or calculate it as needed with a query. Aug 13 '07 #2

 P: 8 By setting Field1's control source to an expression, it doesn't update to a table. You'll have to either set the control source of the Field1 to a field in the table and populate it in the AfterUpdate event of DateA or calculate it as needed with a query. Would I (more specifically COULD I) still use the fAge function in the AfterUpdate portion (btw, duh, I should have known about the control source problem ... still pretty new to databases)? It was hard enough finding some code that would display correctly. I hope I don't have to start over on calculating the date. Aug 13 '07 #3

 Expert Mod 10K+ P: 12,396 Would I (more specifically COULD I) still use the fAge function in the AfterUpdate portion (btw, duh, I should have known about the control source problem ... still pretty new to databases)? It was hard enough finding some code that would display correctly. I hope I don't have to start over on calculating the date. Yes, you can call any function/sub that is within scope of the form for the AfterUpdate. Aug 13 '07 #4

 P: 8 Yes, you can call any function/sub that is within scope of the form for the AfterUpdate. Thanks for your help in this. Is there a way to, each time the form loads or closes, to recalculate? Perhaps to better phrase, I want the user to be able to print a report showing how much time has elapsed between DateA and the current date, so of course Field1 must be recalculated when loading the report to reflect today's date instead of, say, yesterday's or whichever date the record was last updated . I am only using one form for both the entering of the information, and buttons to call up various reports with certain filtering. I guess I'm thinking of some sort of "global" update if that makes sense. Aug 14 '07 #5

 P: 8 Thanks for your help in this. Is there a way to, each time the form loads or closes, to recalculate? Perhaps to better phrase, I want the user to be able to print a report showing how much time has elapsed between DateA and the current date, so of course Field1 must be recalculated when loading the report to reflect today's date instead of, say, yesterday's or whichever date the record was last updated . I am only using one form for both the entering of the information, and buttons to call up various reports with certain filtering. I guess I'm thinking of some sort of "global" update if that makes sense. Think I may have figured out a solution using an Update Query. Hopefully that does the trick. Aug 14 '07 #6

 Expert Mod 10K+ P: 12,396 Usually, with a calculated field, we calculate as needed rather than store it in the tables. Aug 14 '07 #7

 P: 8 Definitely see the wisdom of that now. Aug 15 '07 #8