Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms / March 2007

Tip: Looking for answers? Try searching our database.

Please help!

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
joet5402 - 15 Mar 2007 15:55 GMT
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?

Signature

Joe

Reply to this Message
Allen Browne - 15 Mar 2007 16:24 GMT
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

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm currently working on the following form (from a Field List):
>
[quoted text clipped - 14 lines]
> DATE-START
> TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration time?
Reply to this Message
joet5402 - 15 Mar 2007 16:42 GMT
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?
Signature

Joe

> Set the Default View property of your form to:
>     Continuous
[quoted text clipped - 30 lines]
> > DATE-START
> > TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration time?
Reply to this Message
Allen Browne - 15 Mar 2007 16:57 GMT
Sorry: there should be a comma between the field names:
   Minutes: DateDiff("n", [DATE-START], [TIME-FINISH])

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen, after I deleted the "Duration" field, I entered the Minutes:
> DateDiff("n", [DATE-START] [TIME-FINISH]) formula into a query, but I got
[quoted text clipped - 40 lines]
>> > TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration
>> > time?
Reply to this Message
joet5402 - 15 Mar 2007 17:11 GMT
Allen, should this be in a new query or in an existing query
that contained the previously mentioned fields?
Signature

Joe

> Sorry: there should be a comma between the field names:
>     Minutes: DateDiff("n", [DATE-START], [TIME-FINISH])
[quoted text clipped - 43 lines]
> >> > TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration
> >> > time?
Reply to this Message
Allen Browne - 15 Mar 2007 17:24 GMT
Whatever you like.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen, should this be in a new query or in an existing query
> that contained the previously mentioned fields?
[quoted text clipped - 50 lines]
>> >> > TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration
>> >> > time?
Reply to this Message
joet5402 - 15 Mar 2007 17:36 GMT
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?
Signature

Joe

> Whatever you like.
>
[quoted text clipped - 52 lines]
> >> >> > TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration
> >> >> > time?
Reply to this Message
Allen Browne - 15 Mar 2007 17:44 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 63 lines]
>> >> >> > duration
>> >> >> > time?
Reply to this Message
joet5402 - 15 Mar 2007 18:16 GMT
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.
Signature

Joe

> If your table has fields named DATE-START and TIME-FINISH, Access should
> understand what you are talking about.
[quoted text clipped - 71 lines]
> >> >> >> > duration
> >> >> >> > time?
Reply to this Message
Allen Browne - 16 Mar 2007 02:24 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen, it seems to have worked, but I have a few more questions:  first,
> how
[quoted text clipped - 97 lines]
>> >> >> >> > duration
>> >> >> >> > time?
Reply to this Message
joet5402 - 18 Mar 2007 16:21 GMT
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.

Signature

Joe

> 1. Decimal format? As in 2.95 hours or something? Divide the number of
> minutes by 60.
[quoted text clipped - 127 lines]
> >> >> >> >> > duration
> >> >> >> >> > time?
Reply to this Message
Allen Browne - 19 Mar 2007 00:56 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen...
>
[quoted text clipped - 160 lines]
>> >> >> >> >> > duration
>> >> >> >> >> > time?
Reply to this Message
joet5402 - 19 Mar 2007 19:07 GMT
Well, so far so good, but I have yet to solve the mystery of the duplicating
fields.  Let me see if I can give you a rough sketch of the form layout:

TECH   S.DATE   S.TIME   E.TIME  DURATION
_________________________________________
XXXX   XXXXXX   XXXXXX   XXXXXX  XXXXXXXX
XXXX   XXXXXX   XXXXXX   XXXXXX  XXXXXXXX
XXXX   XXXXXX   XXXXXX   XXXXXX  XXXXXXXX
XXXX   XXXXXX   XXXXXX   XXXXXX  XXXXXXXX
XXXX   XXXXXX   XXXXXX   XXXXXX  XXXXXXXX

...and so on.  From the 2nd row down to the last is the same duplicate
information as the first row.  This is probably the best way I can explain
it.  Sorry for the redundancy.
Signature

Joe

> 1. Set these properties for the text box:
>     Format                Standard
[quoted text clipped - 182 lines]
> >> >> >> >> >> > duration
> >> >> >> >> >> > time?
Reply to this Message
Allen Browne - 20 Mar 2007 01:56 GMT
Good. You have the calculation working.

There could be lots of things affecting the duplication: another table with
multiple records for each one here, something happening in the source query,
etc.

If you really can't solve it, and the rows are identical, you might work
around it by setting the query's Unique Values propert to True. (In the
Properties box.)

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Well, so far so good, but I have yet to solve the mystery of the
> duplicating
[quoted text clipped - 222 lines]
>> >> >> >> >> >> > duration
>> >> >> >> >> >> > time?
Reply to this Message
joet5402 - 20 Mar 2007 13:48 GMT
Well, almost--I haven't quite figured out how to incorporate
the DateAdd function into the query or where in the query to put it (to
figure out the exact calculations of PM-to-AM).

I opened up the Properties box in the query's Design View, and saw nothing
as far as any of the query's Unique Values properties.  Am I looking in the
right spot?
Signature

Joe

> Good. You have the calculation working.
>
[quoted text clipped - 232 lines]
> >> >> >> >> >> >> > duration
> >> >> >> >> >> >> > time?
Reply to this Message
SusanV - 15 Mar 2007 16:26 GMT
It looks as though you have multiple technicians who can each have multiple
tasks with the other info about each task. Is all the data in one table? It
shouldn't be - you should have one table for technicians, and another for
tasks, with a one-to-many relationship bound by technicianID or some such
field. The create your form based on the technician table, and include a
subform from the tasks table, joined also on technicianID.
Signature

hth,
SusanV

> I'm currently working on the following form (from a Field List):
>
[quoted text clipped - 14 lines]
> DATE-START
> TIME-FINISH TIME-DURATION)?  Also, how do I create a total duration time?
Reply to this Message
Joe Clark - 15 Mar 2007 16:32 GMT
On Mar 15, 10:55 pm, joet5402 <joet5...@discussions.microsoft.com>
wrote:
> 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?

Hello from another Joe!  So if I gather correctly, your form is a list
of tasks, each with these five data items.  And what's repeating
itself, exactly?  If it's the -labels- then you should delete them
from the "Detail" section and keep them only in "Form Header".
Everything that's in the detail section occurs once for each record.

If it's the data that's repeating, you have a more fundamental form
problem.  That might happen if you have multiple text boxes, each with
the same "name".  If you have a database table (for example "Tasks")
with these five fields, then the data can be the same for each record
in the table.  But if you add another text box to the "Detail" section
and bind it to a name that's not a table field, then enter data, that
data will probably be copied to all the other records, too.  It's
because all those boxes are "windows" to the same variable.
Reply to this Message
joet5402 - 15 Mar 2007 20:20 GMT
The 5 data labels I have are in the Form Header section--the data for these 5
labels are in the Details section.  My objective is to have enough spaces to
enter multiple "technicians" and the amount of time they spent on a
particular task (if that helps any).  
Signature

Joe

> On Mar 15, 10:55 pm, joet5402 <joet5...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 16 lines]
> data will probably be copied to all the other records, too.  It's
> because all those boxes are "windows" to the same variable.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.