Update a date on open?

  • Thread starter Thread starter Clay
  • Start date Start date
C

Clay

I have a database that keeps track of test scores and
eligibility for promotions. Eligibility for promotion is
based upon test score and years of service.

For years of service, only FULL YEARS count. The
person's start date is entered, and on that day the
following year, the NumYears count should increase by 1.

The problem is, the NumYears count only updates when you
enter a date. So, for instance, if my start date was
June 14, 2002, I should have 2 years of service today.
But, in the database - it is only showing 1 year of
service, unless I re-enter the date of June 14th.

Is there some code I can add to the database or Main menu
to "refresh" all of the Years of Service counts -- or
should I "refresh" all of the dates?

Any suggestions?

Thanks,
Clay
 
Clay said:
I have a database that keeps track of test scores and
eligibility for promotions. Eligibility for promotion is
based upon test score and years of service.

For years of service, only FULL YEARS count. The
person's start date is entered, and on that day the
following year, the NumYears count should increase by 1.

The problem is, the NumYears count only updates when you
enter a date. So, for instance, if my start date was
June 14, 2002, I should have 2 years of service today.
But, in the database - it is only showing 1 year of
service, unless I re-enter the date of June 14th.

Is there some code I can add to the database or Main menu
to "refresh" all of the Years of Service counts -- or
should I "refresh" all of the dates?

Any suggestions?

Thanks,
Clay
Click to expand...

[Years of Service] is a derived value that is always calculable based on
the start date and the current date, and so it shouldn't be stored in
the database at all. Rather, it should be computed when needed, either
as a calculated field in a query (which may be the recordsource for a
form or report) or as a calculated control on a form (that is, a control
that has an expression as its controlsource). Essentially, this is an
"age" function, and I use the function Arvin Meyer wrote for this
purpose:

'----- start of code -----
Function fncAge(DOB, Optional vDate)

' Author: Arvin Meyer, 5/15/97
' Notes: Age calculated as of vDate, or as of today if vDate is missing
' Optional parameter not supported in Access 1 or 2
' Arguments:
' DOB (Variant)
' vDate (Optional) (Variant)
' Returns:
' Age in years, for a person whose Date Of Birth is DOB

If Not IsDate(vDate) Then vDate = Date
If IsDate(DOB) Then
fncAge = DateDiff("yyyy", DOB, vDate) _
+ (DateSerial(Year(vDate), Month(DOB), Day(DOB)) > vDate)
' The following line added by Dirk Goldgar for DataGnostics to
force
' minimum age of zero even when vDate precedes DOB .
If fncAge < 0 Then fncAge = 0
Else
fncAge = Null
End If

End Function
'----- end of code -----

With this function saved in a standard module, you can calculate [Years
of Service] as

=fncAge([StartDate])
 
It Full Years is calculated by an expression and not
stored. The problem, once again, is that it will
not "refresh" automatically when the form is open. It
only updates in the "after update" event of the Start
Date.

Is it possible to have each of these start dates
be "refreshed" upon opening the form?

-----Original Message-----
I have a database that keeps track of test scores and
eligibility for promotions. Eligibility for promotion is
based upon test score and years of service.

For years of service, only FULL YEARS count. The
person's start date is entered, and on that day the
following year, the NumYears count should increase by 1.

The problem is, the NumYears count only updates when you
enter a date. So, for instance, if my start date was
June 14, 2002, I should have 2 years of service today.
But, in the database - it is only showing 1 year of
service, unless I re-enter the date of June 14th.

Is there some code I can add to the database or Main menu
to "refresh" all of the Years of Service counts -- or
should I "refresh" all of the dates?

Any suggestions?

Thanks,
Clay
Click to expand...

[Years of Service] is a derived value that is always calculable based on
the start date and the current date, and so it shouldn't be stored in
the database at all. Rather, it should be computed when needed, either
as a calculated field in a query (which may be the recordsource for a
form or report) or as a calculated control on a form (that is, a control
that has an expression as its controlsource). Essentially, this is an
"age" function, and I use the function Arvin Meyer wrote for this
purpose:

'----- start of code -----
Function fncAge(DOB, Optional vDate)

' Author: Arvin Meyer, 5/15/97
' Notes: Age calculated as of vDate, or as of today if vDate is missing
' Optional parameter not supported in Access 1 or 2
' Arguments:
' DOB (Variant)
' vDate (Optional) (Variant)
' Returns:
' Age in years, for a person whose Date Of Birth is DOB

If Not IsDate(vDate) Then vDate = Date
If IsDate(DOB) Then
fncAge = DateDiff("yyyy", DOB, vDate) _
+ (DateSerial(Year(vDate), Month(DOB), Day (DOB)) > vDate)
' The following line added by Dirk Goldgar for DataGnostics to
force
' minimum age of zero even when vDate precedes DOB .
If fncAge < 0 Then fncAge = 0
Else
fncAge = Null
End If

End Function
'----- end of code -----

With this function saved in a standard module, you can calculate [Years
of Service] as

=fncAge([StartDate])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Would an Update Query work? I could just update the
Start Dates with the same Start Dates upon opening the
subform. Would this work?

-----Original Message-----
I have a database that keeps track of test scores and
eligibility for promotions. Eligibility for promotion is
based upon test score and years of service.

For years of service, only FULL YEARS count. The
person's start date is entered, and on that day the
following year, the NumYears count should increase by 1.

The problem is, the NumYears count only updates when you
enter a date. So, for instance, if my start date was
June 14, 2002, I should have 2 years of service today.
But, in the database - it is only showing 1 year of
service, unless I re-enter the date of June 14th.

Is there some code I can add to the database or Main menu
to "refresh" all of the Years of Service counts -- or
should I "refresh" all of the dates?

Any suggestions?

Thanks,
Clay
Click to expand...

[Years of Service] is a derived value that is always calculable based on
the start date and the current date, and so it shouldn't be stored in
the database at all. Rather, it should be computed when needed, either
as a calculated field in a query (which may be the recordsource for a
form or report) or as a calculated control on a form (that is, a control
that has an expression as its controlsource). Essentially, this is an
"age" function, and I use the function Arvin Meyer wrote for this
purpose:

'----- start of code -----
Function fncAge(DOB, Optional vDate)

' Author: Arvin Meyer, 5/15/97
' Notes: Age calculated as of vDate, or as of today if vDate is missing
' Optional parameter not supported in Access 1 or 2
' Arguments:
' DOB (Variant)
' vDate (Optional) (Variant)
' Returns:
' Age in years, for a person whose Date Of Birth is DOB

If Not IsDate(vDate) Then vDate = Date
If IsDate(DOB) Then
fncAge = DateDiff("yyyy", DOB, vDate) _
+ (DateSerial(Year(vDate), Month(DOB), Day (DOB)) > vDate)
' The following line added by Dirk Goldgar for DataGnostics to
force
' minimum age of zero even when vDate precedes DOB .
If fncAge < 0 Then fncAge = 0
Else
fncAge = Null
End If

End Function
'----- end of code -----

With this function saved in a standard module, you can calculate [Years
of Service] as

=fncAge([StartDate])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Clay said:
It Full Years is calculated by an expression and not
stored. The problem, once again, is that it will
not "refresh" automatically when the form is open. It
only updates in the "after update" event of the Start
Date.
Click to expand...

If that's what you are seeing, then -- meaning no offense -- you are
doing it wrong. There are two ways, using this function, to get
YearsOfService to appear on the form. You don't need any code in the
AfterUpdate event of StartDate.

Method 1 -- a calculated control
---------------------------------
Put a text box named "txtYearsOfService" on the form. Set its
ControlSource proeprty to

=fncAge([StartDate])

(substitute the name of the field that holds the start date, as needed.)


Method 2 -- a calculated field in a query
----------------------------------------
Suppose your form is currently based on table "tblInfo", which has
fields named ID, StartDate, and others. Create a query based on that
form, with SQL like this:

SELECT
ID,
StartDate,
<... other fields ...,>
fncAge(StartDate) As YearsOfService
FROM tblInfo;

Now set your form's RecordSource property to this query. The calculated
YearsOfService field will now be in the form's RecordSource, and you can
add a text box bound directly to that field.
 
Clay said:
Would an Update Query work? I could just update the
Start Dates with the same Start Dates upon opening the
subform. Would this work?
Click to expand...

Doing it that way would require running the update query every day. Do
you really want to do that?
 
No offense taken at all. I very much appreciate the
help. I am about to be off of work today - but I'll be
here first thing in the morning to try it. I just hope
it won't make changing what exists already too much -
there are some important calculations of eligibility that
depend on the yrs. of service calculation. I hope this
isn't as bad as I'm thinking it is. If so - I may just
have to ask you for the daily update advice (query) --
until I can get it fixed and implemented.

Thanks for your time!
Is there another way I can get back to you? Will posting
here work for tomorrow - or another day?
-----Original Message-----
It Full Years is calculated by an expression and not
stored. The problem, once again, is that it will
not "refresh" automatically when the form is open. It
only updates in the "after update" event of the Start
Date.
Click to expand...

If that's what you are seeing, then -- meaning no offense -- you are
doing it wrong. There are two ways, using this function, to get
YearsOfService to appear on the form. You don't need any code in the
AfterUpdate event of StartDate.

Method 1 -- a calculated control
---------------------------------
Put a text box named "txtYearsOfService" on the form. Set its
ControlSource proeprty to

=fncAge([StartDate])

(substitute the name of the field that holds the start date, as needed.)


Method 2 -- a calculated field in a query
----------------------------------------
Suppose your form is currently based on table "tblInfo", which has
fields named ID, StartDate, and others. Create a query based on that
form, with SQL like this:

SELECT
ID,
StartDate,
<... other fields ...,>
fncAge(StartDate) As YearsOfService
FROM tblInfo;

Now set your form's RecordSource property to this query. The calculated
YearsOfService field will now be in the form's RecordSource, and you can
add a text box bound directly to that field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Clay said:
No offense taken at all. I very much appreciate the
help. I am about to be off of work today - but I'll be
here first thing in the morning to try it. I just hope
it won't make changing what exists already too much -
there are some important calculations of eligibility that
depend on the yrs. of service calculation.
Click to expand...

In that case, your best bet is to use a query that calculates the years
of service, as I described, and substitute that query for the table
itself in other queries and lookups.
I hope this
isn't as bad as I'm thinking it is. If so - I may just
have to ask you for the daily update advice (query) --
until I can get it fixed and implemented.
Click to expand...

Ouch. I hope it's not that bad.
Thanks for your time!
Is there another way I can get back to you? Will posting
here work for tomorrow - or another day?
Click to expand...

Posting here normally works fine, with the proviso that I only have a
varying, limited amount of time for volunteer work. Please reply in
this thread, so that I can be sure of seeing it -- I keep an eye on all
threads in which I am participating. If you have to post a new,
out-of-thread message that you really want me to see, use my name in the
subject and my newsreader will highlight it for me. But normally just
replying in the thread is sufficient.
 
Back
Top