RE: updated numbers in report
- From: inge <inge@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 1 Dec 2008 23:04:10 -0800
HI clifford,.
Thanx alot for your suggestions, but i finally found a way.
The input is the same as i descibed, cause some officers
have experience from other types of vessels and im looking
for a special type of vessel. The officers know their experience
when the are comming onboard, and the database makes out the
rest from there. So there is the reason for having this (5,3 etc)
numbers instead of a date.
I did use this datediff function and added some. i used the days and made out
the answer in general numbers so it matches the input number format.
i used 30 days a month, so over a ten years period it will be approx 40 days
wrong.
in this case that is ok, cause by that time the officer have most probably
changed vessel. hehe
But i have one more question, its about a textbox. I have a form with 11
comboboxes that will be connected to a criteria in 11 queries. These will
make out the information in 4 different reports. On this form i was thinking
to have this textbox
for making an input to the database. (one textbox for one combobox) the
input will be the date for this time experience record. is that possible? The
textbox will be located alongside with the combobox where the belonging
officer selection is. But this combobox only makes the input for the
belonging query for the report. i have tried this form.myform.textbox but it
will not make the input to the query. in the query, the date is now comming
from the database, and i have to use the inputform to change this date on all
onboard. The Comboboxes get the list from the datebase. and i used the
form.myform.combox to get this into the criteria. Is it becouse i used the
criteria for the combobox, the textbox wouldnt work? I didnt change any other
settings in the textbox or the daterecord in the query. so they are very
plain.
brgds
inge
"Clifford Bass" wrote:
Hi Inge,.
I would suggest that you only store the dates when something happens,
such as the start of being on board and the end of being on board. I would
not go the route of updating the 5,3, 7,3 etc. every so often since they are
out-of-date by, anywhere from the next day to the next 30 days. Well, once a
particular event has completed, they would become static for that event. But
then you still have the problem of how to add them up with other like events
so that you can get the total time for those same events combined. Plus,
will someone remember to correct them when they are correcting erroneous
dates? Better to calculate them on the fly, whether for display on a form
(never hand entered) or for display on a report. Since this is a bit of an
unusual form of time elapsed, you might want to use a custom VBA function
that you can call with the dates whenever you want to compute the value.
Maybe something like:
Public Function ElapsedTime(ByVal dtStart As Date, ByVal dtEnd As Date) _
As String
Dim intMonths As Integer
Dim intTotalMonths As Integer
Dim intYears As Integer
intTotalMonths = DateDiff("m", dtStart, dtEnd)
intYears = intTotalMonths \ 12 ' Using \ for integer result
intMonths = intTotalMonths - intYears * 12
ElapsedTime = intYears & "," & intMonths
End Function
So in your query you might have something like (in design mode):
Years on Tanker: ElapsedTime([Date First Time Onboard], Date())
This is simplified due to your not giving a lot of detail of your
database design. I hope that you are recording each instance of service
separately in a child table. You would then need to do some kind of summary
query to get a total time on board or total time in a certain rank. In which
case you might want to sum up the total months using just the DateDiff()
function and then in your report or form calculate the year,month value. So
your query might look something like this:
select PersonID, Sum(DateDiff("m", [Rank_Start_Date], Nz([Rank_End_Date],
Date()))) As Months_Onboard
from Tours_of_Duty
group by PersonID
The Nz(..., Date()) allows for a blank (null) ending date, which means
the tour of duty is still in progress; therefore use the current date for the
end date. And on your report you might have a calculated field with this for
a control source:
=[Months_Onboard] \ 12 & "," & [Months_Onboard] - ([Months_Onboard] \ 12) * 12
And, if this is used in a lot of places, it would make sense to put it
into a function.
Public Function ElapsedTime2(ByVal intMonths As Integer) As String
Dim intYears As Integer
intYears = intMonths \ 12
ElapsedTime2 = intYears & "," & intMonths - intYears * 12
End Function
Which would simplify the control source of the box to:
=ElapsedTime2([Months_Onboard])
Of course, you may want to be more fine tuned and calculate based on
total days instead of total months since the DateDiff() function with the "m"
option returns integers; no fractions. So someone who was onboard twice for
1 month and 25 days would get reported as being on board for a total of 2
months. Or maybe you could cause it to "round" by using the DateAdd()
function to add 15 days. It all depends on what your goal is.
Hope this helps,
Clifford Bass
"inge" wrote:
I have a database with many posts.
I use a form to make all the inputs.
I have queries to make information on a report linked
to the database, using a form with combobox for selecting what info to show.
in the database the user will make inputs looking like 5,3 or 7,3. theese
numbers
represents experience time before registration in the database. (months or
years)
Also i have two posts in the database showing dates, one representing the
date of first time registration and one representing date this time onboard.
how can i update the inputs looking like 5,3 or 7,3 using the input numbers
and date? example,.. when a year have ellaps after the first time
registration the 5,3 number should show 6,3 and the 7,3 number should show
8,3. or after 6 months 5,3 should be 5,8 etc.
post in database (named experience):
years in rank : 5,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
years on tanker : 7,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
Months onboard this tour of duty : 2,1 (this number i hoped to show
automatic calculated from access using the date this time onboard)
date first time onboard : an date(11.10.08) user input
date this time onboard : an date(11.11.08) user input
(theese two dates is only for making the update, and is not showing in the
report.
The dates is now only in the database, but i can move them if you have a
better sugestion.)
The number format is a requirement, how its going to show on the report.
(the queries are showing info from database from a name selected from the
combobox. Some info is as described and other is plain text or checkbox.
This
information shows on the report.)
So its all about having the input "numbers" for years in rank, years on
tanker,
and months onboard to be updated by access after the first time registration.
The date for this time onboard need to be updated by user every time the
person comes onboard.
If possible i want the calculated figures to be displayed in the report
only.
brgds
inge
- Follow-Ups:
- RE: updated numbers in report
- From: Clifford Bass
- RE: updated numbers in report
- References:
- RE: updated numbers in report
- From: Clifford Bass
- RE: updated numbers in report
- Prev by Date: RE: updated numbers in report
- Next by Date: RE: updated numbers in report
- Previous by thread: RE: updated numbers in report
- Next by thread: RE: updated numbers in report
- Index(es):
Relevant Pages
|
