By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,395 Members | 1,412 Online
Bytes IT Community
+ Ask a Question
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
  1. Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
  2.  
  3. If IsNull(dteStart) = True Then Exit Function
  4.  
  5. Dim intHold   As Integer
  6. Dim dayHold   As Integer
  7.  
  8.  
  9.    'correctly return number of whole months difference
  10.    'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
  11.    'that returns -1 if true, 0 if false
  12.    intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
  13.  
  14.  
  15.    'correctly return number of days difference
  16.    If Day(dteEnd) < Day(dteStart) Then
  17.       dayHold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
  18.    Else
  19.       dayHold = Day(dteEnd) - Day(dteStart)
  20.    End If
  21.  
  22.    fAge = LTrim(str(intHold \ 12)) & "" & LTrim(str(intHold Mod 12)) & " months " & LTrim(str(dayHold)) & " days "
  23.  
  24. 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
Share this Question
Share on Google+
7 Replies


Rabbit
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
  1. Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
  2.  
  3. If IsNull(dteStart) = True Then Exit Function
  4.  
  5. Dim intHold   As Integer
  6. Dim dayHold   As Integer
  7.  
  8.  
  9.    'correctly return number of whole months difference
  10.    'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
  11.    'that returns -1 if true, 0 if false
  12.    intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
  13.  
  14.  
  15.    'correctly return number of days difference
  16.    If Day(dteEnd) < Day(dteStart) Then
  17.       dayHold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
  18.    Else
  19.       dayHold = Day(dteEnd) - Day(dteStart)
  20.    End If
  21.  
  22.    fAge = LTrim(str(intHold \ 12)) & "" & LTrim(str(intHold Mod 12)) & " months " & LTrim(str(dayHold)) & " days "
  23.  
  24. 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

Rabbit
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

Rabbit
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

Post your reply

Sign in to post your reply or Sign up for a free account.