Adding Fields in a Form

[Guest]

I'm trying to add the numbers from 12 fields in a form. The formula im using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Guest]

Are these 12 fields text fields or number fields?

 

[Guest]

text fields

Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I'm trying to add the numbers from 12 fields in a form. The formula im using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[RoyVidar]

I'm trying to add the numbers from 12 fields in a form. The formula im using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

This looks a bot like a spreadsheet approach. If these are actual
fields, and not results of calculations in a query, you should
probably consider a bit of normalization.

Here, since it doesn't seem Null is a problem, you could probably
do something like

=Val([06-07])+Val([07-08])...

 

[Douglas J. Steele]

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text fields

Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I'm trying to add the numbers from 12 fields in a form. The formula im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Guest]

There in lies your problem. You can't add up text and expect to get a total


I got this from the post "Adding on a form: 5+1 = 51 ????"

=Clng([06-07])+Clng([07-08])+Clng([08-09])+Clng([09-10])+Clng([10-11])+Clng([11-12])+Clng([12-13])+Clng([13-14])+Clng([14-15])+Clng([15-16])+Clng([16-17])+Clng([17-18])


--

The 11th day of every month:

http://truthaction.org/forum/index.php

text fields

Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I'm trying to add the numbers from 12 fields in a form. The formula im using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Guest]

Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels, field
name is Total. How do I do this?

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text fields

Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The formula im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Douglas J. Steele]

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text fields

:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Guest]

Im going to use this calculated value as part of a report. the report will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08 etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in this?

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text fields

:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Douglas J. Steele]

Base the report on the query, not the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Im going to use this calculated value as part of a report. the report
will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08
etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in
this?

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as this
in
your table accomplishes three things: it wastes disk space; it wastes
time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and
no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

:

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes might
be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text fields

:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The
formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting
the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Douglas J. Steele]

Alternatively, if you're determined to use the table rather than a query,
you can set the ControlSource of a text box on your report to the same
calculation you used on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Base the report on the query, not the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Im going to use this calculated value as part of a report. the report
will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08
etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in
this?

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as
this in
your table accomplishes three things: it wastes disk space; it wastes
time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields
is
subsequently edited, you will have data in your table WHICH IS WRONG,
and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and
use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

:

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes
might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text fields

:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The
formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting
the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[Guest]

Thanks, got the reports working now, used the query.

Alternatively, if you're determined to use the table rather than a query,
you can set the ControlSource of a text box on your report to the same
calculation you used on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Base the report on the query, not the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Im going to use this calculated value as part of a report. the report
will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08
etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in
this?

:

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as
this in
your table accomplishes three things: it wastes disk space; it wastes
time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields
is
subsequently edited, you will have data in your table WHICH IS WRONG,
and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and
use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

:

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes
might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text fields

:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


:

I'm trying to add the numbers from 12 fields in a form. The
formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting
the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

 

[John W. Vinson]

The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08 etc,
stored to table.

That's another problems, as Roy suggested!

Storing data (a time range) in fieldnames IS INCORRECT design.
Storing numeric data in text fields is a bad idea too.

I'd strongly suggest having one *RECORD* per hour, rather than one field per
hour, in a table structure such as

MachineNumber <link to table of machines>
TimeStart Date/Time <e.g. #8/15/2007 10:00:00>
Production <Long Integer, Double, or Decimal number as appropriate>

You can then use a Totals query to sum up production for all machines over a
date/time range, or to sum up all timesteps for a single machine, or any other
combination. Your "wide-flat" design is much less flexible!

John W. Vinson [MVP]

 

[Guest]

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three. Having one record per hour would result in 168 records being
created everyday

 

[John W. Vinson]

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]

 

[Guest]

This is the first time ive used access for anything like this. How would I
produce reports for production levels on each machine and over a shift with
the information being spread over lots of different records rather than just
one? There are daily, weekly and monthly reports.

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]

 

[Douglas J. Steele]

You can use a cross-tab query to present your data in the same way you're
currently storing it.

That way, you get the best of both worlds: the data is stored correctly so
that you can manipulate it as easily as you need to, but you can still
present it as desired.

And just in case you need more ammunition for why to store it John's way
rather than your way, write me a query to tell me how many machines produced
fewer than n units an hour for 3 consecutive hours. It's pretty
straight-forward to do it using John's approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the first time ive used access for anything like this. How would
I
produce reports for production levels on each machine and over a shift
with
the information being spread over lots of different records rather than
just
one? There are daily, weekly and monthly reports.

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a
number,
will take up less space as you can specify the number of characters, in
this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname
where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a
way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access
can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]