Calculation and Percentage formatting problem

J

Jimmy

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered =MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there a
property setting that can be changed so that if all fields aren't populated,
it will still add the data? I know I can just put 0.00, but I wanted to see
if this was possible.

Thanks in advance for the help!!
 
D

Dennis

I can't answer your first issue, but for the second, you can use use the NZ()
function for each field. That way, if the field is empty, it will return a
zero (assuming the field is defined as a numeric).
 
A

Al Campagna

Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
M

MikeJohnB

Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Jimmy said:
I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
 
J

Jimmy

I couldn't get either one to work. I had it as follows in the control source
of the field:
=nz(Administrative Fees,0)+nz(Other Fees,0)

Is that not correct? I also add brakets too, but that didn't work

MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Jimmy said:
I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
M

MikeJohnB

Where are applying the equation?

In an unbound text box control?

Are they Field names?

Try

=NZ([Administrative Fees],0)+NZ([Other Fees],0)

The reason for this is your field name have spaces and therefore you have to
enclose then in the [Brackets]


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Jimmy said:
I couldn't get either one to work. I had it as follows in the control source
of the field:
=nz(Administrative Fees,0)+nz(Other Fees,0)

Is that not correct? I also add brakets too, but that didn't work

MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
J

Jimmy

Thank you very much. I had closed paranthesis after each field as well,
that's why it wouldn't work for me. Appreciate it!!!

MikeJohnB said:
Where are applying the equation?

In an unbound text box control?

Are they Field names?

Try

=NZ([Administrative Fees],0)+NZ([Other Fees],0)

The reason for this is your field name have spaces and therefore you have to
enclose then in the [Brackets]


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Jimmy said:
I couldn't get either one to work. I had it as follows in the control source
of the field:
=nz(Administrative Fees,0)+nz(Other Fees,0)

Is that not correct? I also add brakets too, but that didn't work

MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


:

Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
J

Jimmy

Actually, it isn't working. It'll add the first number but not the second.
It is a bound field linked to a table.

MikeJohnB said:
Where are applying the equation?

In an unbound text box control?

Are they Field names?

Try

=NZ([Administrative Fees],0)+NZ([Other Fees],0)

The reason for this is your field name have spaces and therefore you have to
enclose then in the [Brackets]


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Jimmy said:
I couldn't get either one to work. I had it as follows in the control source
of the field:
=nz(Administrative Fees,0)+nz(Other Fees,0)

Is that not correct? I also add brakets too, but that didn't work

MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


:

Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
J

Jimmy

I got it now. Sorry for the miscommunication.

MikeJohnB said:
Where are applying the equation?

In an unbound text box control?

Are they Field names?

Try

=NZ([Administrative Fees],0)+NZ([Other Fees],0)

The reason for this is your field name have spaces and therefore you have to
enclose then in the [Brackets]


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Jimmy said:
I couldn't get either one to work. I had it as follows in the control source
of the field:
=nz(Administrative Fees,0)+nz(Other Fees,0)

Is that not correct? I also add brakets too, but that didn't work

MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


:

Jimmy,
1. I think you want to enter 20 in a text control, and have it display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work. How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

MikeJohnB,
Thanks for picking up on the "typo..."
Regarding the Default of 0, I try to just stay strictly with the OP's
stated question.
In this case, the OP said...
I know I can just put 0.00 <
Click to expand...
"Put" in this instance could mean manual entry, or using the Default, so
I offered the NZ solution.

Your suggestion of a Default Value is perfectly legitimate.
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on
the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the
form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
Jimmy,
1. I think you want to enter 20 in a text control, and have it
display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Jimmy said:
I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about
an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work.
How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is
there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted
to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 
M

MikeJohnB

Thanks for your considerations Al, thought it would have worked but as you
say, the question was loaded in the manner in which your rightly answered it.
As soon as Jimmy posted his efforts, it was easy to pick up the space in the
field name.

Always a pleasure to learn from your Answers

Kindest Regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
MikeJohnB,
Thanks for picking up on the "typo..."
Regarding the Default of 0, I try to just stay strictly with the OP's
stated question.
In this case, the OP said...
I know I can just put 0.00 <
Click to expand...
"Put" in this instance could mean manual entry, or using the Default, so
I offered the NZ solution.

Your suggestion of a Default Value is perfectly legitimate.
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


MikeJohnB said:
Slight Typo from Al

NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0) is missing a close bracket on
the
second NZ (Only noted in case you cut and paste and then change the field
names.

NZ(Field1,0) + NZ(Field2,0) + NZ(Field3,0)

The reason for post is,

what does setting the default value of the field and the control on the
form
to 0 do?

In this case, if nothing is entered into the field or a control then the
value would be 0 not null?

Perhaps I am wrong Al?

Kindest regards and Happy New Year

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Al Campagna said:
Jimmy,
1. I think you want to enter 20 in a text control, and have it
display
20%?
Format the field for percentage, and on the AfterUpdate event...
(use your own control names)
MyPercentage = MyPercentage/100
**Understand that in doing so, your committing to always enter
percentages as 100 times their real value.

2. Use the NZ function...
NZ(Field1,0) + NZ(Field2,0 + NZ(Field3,0)
If any field is null, Access will give it a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have 2 small issues that I can't figure out. I have a field that is a
percentage and Access displays 1 as 100 percent. I saw something about
an
after update procedure, but it doesn't work. I entered
=MakePercent([Field
Name]) in the After Update properties of the field and it won't work.
How
do
I correct that?

The second issue is that I am adding up 3 fields to get a total. Is
there
a
property setting that can be changed so that if all fields aren't
populated,
it will still add the data? I know I can just put 0.00, but I wanted
to
see
if this was possible.

Thanks in advance for the help!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weighted % Complete calculation 1
problem calculating percentages in excel for mac 2
Refresh causes unbound fields to be cleared 6
Calculated Field in Form 6
Values calculated in a Form entered in the Table? 6
percentage once again 5
Form Field/Function problem 1
Calculation with a Blank or 0 Cell 3

Top