1. Set these properties for the text box:
Format Standard
Alternatively, use:
Format Fixed
Decimal Places 2
2. Yes. To add a day:
DateAdd("d",1, [TIME-FINISH])
You can figure out the IIf() part.
3. The Indexes dialog has 3 columns, and multiple rows.
3.1 Below any existing indexes, you enter 2 rows.
3.2 On the first row, your index name can be anything - typically the
combination of names so it's meaningful.
3.3 Beside that, enter your first field name, which I think is
DATE-START.
3.4 On the next row, leave the IndexName column blank, and choose your
2nd
field name in the Field Name column.
The blank index name indicates that this field is part of a multi-field
index, i.e. the index covers both fields in combination.
4. Into the Control Source property of the text box.
Hi Allen...
1. The decimal format did work, however I would like to minimize the #
of
decimal places to 2. How can I get it to display 2 decimal places (i.e.
2.25)?
2. In the case of a tech working on a task from 11:30 pm to 1:30 am,
would
I use the DateAdd function? What is the exact layout of this function
and
would I use it in the query or in the form?
3. I was confused with this answer; I did what you had instructed, but
it
would not let me save the table because I had a blank space in the
Field
Name
section (the line where you had said to leave the name blank). What
field
exactly would I enter the (StartTimeEndTime DATE-START) and then the
TIME-FINISH text?
4. I added the text box for summing the minutes to my Form Footer, but
I'm
wondering where the "=Sum([Minutes])" formula goes.
--
Joe
:
1. Decimal format? As in 2.95 hours or something? Divide the number of
minutes by 60.
2. If you have a date and time (not just a time), it works. If not,
you
will
need to use an IIf() expression to add 1 day to the end time if it is
less
than the start time and you want to assume that this means it wrapped
past
midnight. Use DateAdd() to add 1 days.
3. To block the user from being able to enter 2 records with the same
start-time and end-time:
a) Open the table in design view
b) Open the Indexes box (toolbar)
c) On a blank row enter:
StartTimeEndTime DATE-START
and set the Unique property (lower pane of the dialog) to Yes.
Then on the next line, leave the name blank and enter the 2nd field:
TIME-FINISH
d) Save the table.
(Note that this may not work perfectly, due to floating point errors.)
4a) To sum the minutes in a form, add a text box to your Form Footer,
and
enter:
=Sum([Minutes])
b)You can do the same in a Report Footer.
c) In a query, depress the Total button on the Toolbar.
Access adds a Total row to the grid.
Under the Minutes field, choose Sum.
--
Allen, it seems to have worked, but I have a few more questions:
first,
how
can I display the duration time in decimal format? Second, when
there
is
a
start/finish time of 22:30-01:30 for example, it displays a negative
number;
how can I format it to reflect a more accurate time? Thirdly (when
I'm
finally able to finish the previous 2 problems), how can I create
rows
that
do not duplicate the original row?
Lastly, how do I go about summing the minutes?
Sorry for all the questions...this database has been giving me a
hard
time.
--
Joe
:
If your table has fields named DATE-START and TIME-FINISH, Access
should
understand what you are talking about.
The fact that it pops up a parameter dialog means it cannot find
those
names. Perhaps the table is not in the query. Perhaps the spelling
is
different (e.g. a space.) Or perhaps they are calculated fields
that
are
not
available at the time Access needs them.
Allen, I inserted it into an existing query, and when I went to
run
it,
it
prompted an "Enter Parameter Value: Date-Start" then an "Enter
Parameter
Value: Time-Finish". Is there a way I can draw the dates already
in
the
query to use in figuring the difference in time without being
prompted?
Should I have a Total row?
--
Joe
:
Whatever you like.
Allen, should this be in a new query or in an existing query
that contained the previously mentioned fields?
--
Joe
:
Sorry: there should be a comma between the field names:
Minutes: DateDiff("n", [DATE-START], [TIME-FINISH])
message
Allen, after I deleted the "Duration" field, I entered the
Minutes:
DateDiff("n", [DATE-START] [TIME-FINISH]) formula into a
query,
but
I
got
an
error message saying, "The expression you entered contains
invalid
syntax.
You may have entered an operand without an operator". How
can
this
be
fixed?
--
Joe
:
Set the Default View property of your form to:
Continuous
Now you put only *one* row of controls in the form's
Detail
section.
When you run the form, it repeats the row for every
record.
Remove the Time-Duration field from your table.
Instead, create a query, and type this into a fresh column
in
the
Field
row:
Minutes: DateDiff("n", [DATE-START] [TIME-FINISH])
You can then sum the minutes and do whatever you want.
More details about there in:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html
message
I'm currently working on the following form (from a
Field
List):
(these are in form header section)
TECHNICIAN-START DATE-START TIME-FINISH TIME-DURATION
TECHNICIAN-START DATE-START TIME-FINISH TIME-DURATION
TECHNICIAN
TECHNICIAN
TECHNICIAN
TECHNICIAN
TECHNICIAN
etc.
(all these are text boxes in Detail section)
This is my problem: How do I get the fields below the
initial
line
to
not
duplicate any of the data from the initial line
(TECHNICIAN-START
DATE-START
TIME-FINISH TIME-DURATION)? Also, how do I create a
total
duration
time?
