Format Expression as a percent

[Steve Stad]

Can you tell me how to format this expression in a Query as a percent.
LastNmPerc: ([CountOfEMP_LAST_NAME]/[CountOfID])

 

[Jerry Whittle]

LastNmPerc: Format([CountOfEMP_LAST_NAME]/[CountOfID],"0.00%")

 

[KARL DEWEY]

Remember to use it in a query that follows the one that did the counting.

Or you can do it this way --
LastNmPerc: Format(Count([EMP_LAST_NAME])/Count([ID]),"0.00%")

--
Build a little, test a little.

LastNmPerc: Format([CountOfEMP_LAST_NAME]/[CountOfID],"0.00%")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Can you tell me how to format this expression in a Query as a percent.
LastNmPerc: ([CountOfEMP_LAST_NAME]/[CountOfID])

 

[Marshall Barton]

Can you tell me how to format this expression in a Query as a percent.
LastNmPerc: ([CountOfEMP_LAST_NAME]/[CountOfID])

Because users should not see a query's datasheet, your
question is mostly irrelevant (you are the only one that
uses a query's datasheet to verify that the query does what
you want). Use the Format property of the report/form text
box used to display the query's data.

If it's important for you to see 75% instead of .75, set the
field's Format property to Percent or 0% At least this way
you don't convert the field's value from a number to text
and prevent it from being used in calculations.

 

[Steve Stad]

Marshall - Thank you for reply. It was most helpful and very correct, i.e.,
it is much better to format in the report. I have added additional 'iif'
logic (see below) to some fields in the query which will calculate a numeric
percentage if Emp_type = MIL or write the text 'CIV'. But how should/can I
format this field in the report. The logic works but I lost the Percent
formating in the report for this field.

MILBOS%: IIf([EMP_TYPE]="MIL",([CountOfEMP_MIL_BOS]/[CountOfID]),"CIV")

steve

 

[Marshall Barton]

Because the calculated field might contain "CIV", the field
must be a Text field. So, you still have the same problem
as before.

Is it really necessary to put the "CIV" in the query? I
mean, can't the report text box deal with that part of the
problem?

I can think if a couple a ways to approach this. One way
would be to change the field to return a negative number
instead of "CIV". Then the report text box's Format
property could be used to display CIV instead of a negative
precent:
0%;"CIV";0%

Another could be to have the query just calculate the
percent:
MILBOS%: CountOfEMP_MIL_BOS / CountOfID
And then use an expression in the report text box:
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV")

In general, I prefer using the format property for the same
reason as before, but it probably won't make much difference
which way you go.
--
Marsh
MVP [MS Access]

Marshall - Thank you for reply. It was most helpful and very correct, i.e.,
it is much better to format in the report. I have added additional 'iif'
logic (see below) to some fields in the query which will calculate a numeric
percentage if Emp_type = MIL or write the text 'CIV'. But how should/can I
format this field in the report. The logic works but I lost the Percent
formating in the report for this field.

MILBOS%: IIf([EMP_TYPE]="MIL",([CountOfEMP_MIL_BOS]/[CountOfID]),"CIV")

Because users should not see a query's datasheet, your
question is mostly irrelevant (you are the only one that
uses a query's datasheet to verify that the query does what
you want). Use the Format property of the report/form text
box used to display the query's data.

If it's important for you to see 75% instead of .75, set the
field's Format property to Percent or 0% At least this way
you don't convert the field's value from a number to text
and prevent it from being used in calculations.

 

[Steve Stad]

Marshall,

When you say 'use an expression in the report text box' do you mean the
control source property in the DATA tab. I tried the expression
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV") in the control source property but
got a circular error and then ran the report and it said 'error' in the
MILBOS field on the report.
I also tried 0%;"CIV";0% in the Format text box -- not sure I understand
changing the field to return a negative number instead of "CIV".

Because the calculated field might contain "CIV", the field
must be a Text field. So, you still have the same problem
as before.

Is it really necessary to put the "CIV" in the query? I
mean, can't the report text box deal with that part of the
problem?

I can think if a couple a ways to approach this. One way
would be to change the field to return a negative number
instead of "CIV". Then the report text box's Format
property could be used to display CIV instead of a negative
precent:
0%;"CIV";0%

Another could be to have the query just calculate the
percent:
MILBOS%: CountOfEMP_MIL_BOS / CountOfID
And then use an expression in the report text box:
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV")

In general, I prefer using the format property for the same
reason as before, but it probably won't make much difference
which way you go.
--
Marsh
MVP [MS Access]

Marshall - Thank you for reply. It was most helpful and very correct, i.e.,
it is much better to format in the report. I have added additional 'iif'
logic (see below) to some fields in the query which will calculate a numeric
percentage if Emp_type = MIL or write the text 'CIV'. But how should/can I
format this field in the report. The logic works but I lost the Percent
formating in the report for this field.

MILBOS%: IIf([EMP_TYPE]="MIL",([CountOfEMP_MIL_BOS]/[CountOfID]),"CIV")

Because users should not see a query's datasheet, your
question is mostly irrelevant (you are the only one that
uses a query's datasheet to verify that the query does what
you want). Use the Format property of the report/form text
box used to display the query's data.

If it's important for you to see 75% instead of .75, set the
field's Format property to Percent or 0% At least this way
you don't convert the field's value from a number to text
and prevent it from being used in calculations.

.

 

[Marshall Barton]

When you say 'use an expression in the report text box' do you mean the
control source property in the DATA tab. I tried the expression
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV") in the control source property but
got a circular error and then ran the report and it said 'error' in the
MILBOS field on the report.

The expression looks good, but I'll bet the name of the text
box is MILBOS% (or EMP_TYPE). If so, change the name of the
text box to something else (eg. txtMILBOS).

I also tried 0%;"CIV";0% in the Format text box -- not sure I understand
changing the field to return a negative number instead of "CIV".

For this approach, the query field would be:
MILBOS%: IIf([EMP_TYPE] = "MIL", CountOfEMP_MIL_BOS /
CountOfID, -.1)
--
Marsh
MVP [MS Access]

Because the calculated field might contain "CIV", the field
must be a Text field. So, you still have the same problem
as before.

Is it really necessary to put the "CIV" in the query? I
mean, can't the report text box deal with that part of the
problem?

I can think if a couple a ways to approach this. One way
would be to change the field to return a negative number
instead of "CIV". Then the report text box's Format
property could be used to display CIV instead of a negative
precent:
0%;"CIV";0%

Another could be to have the query just calculate the
percent:
MILBOS%: CountOfEMP_MIL_BOS / CountOfID
And then use an expression in the report text box:
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV")

In general, I prefer using the format property for the same
reason as before, but it probably won't make much difference
which way you go.

Marshall - Thank you for reply. It was most helpful and very correct, i.e.,
it is much better to format in the report. I have added additional 'iif'
logic (see below) to some fields in the query which will calculate a numeric
percentage if Emp_type = MIL or write the text 'CIV'. But how should/can I
format this field in the report. The logic works but I lost the Percent
formating in the report for this field.

MILBOS%: IIf([EMP_TYPE]="MIL",([CountOfEMP_MIL_BOS]/[CountOfID]),"CIV")


:
Because users should not see a query's datasheet, your
question is mostly irrelevant (you are the only one that
uses a query's datasheet to verify that the query does what
you want). Use the Format property of the report/form text
box used to display the query's data.

If it's important for you to see 75% instead of .75, set the
field's Format property to Percent or 0% At least this way
you don't convert the field's value from a number to text
and prevent it from being used in calculations.

.

 

[Steve Stad]

Thanks again Marshall -- the 0%;"CIV";0% formatting and redoing the
expression in the query worked!!!!

When you say 'use an expression in the report text box' do you mean the
control source property in the DATA tab. I tried the expression
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV") in the control source property but
got a circular error and then ran the report and it said 'error' in the
MILBOS field on the report.

The expression looks good, but I'll bet the name of the text
box is MILBOS% (or EMP_TYPE). If so, change the name of the
text box to something else (eg. txtMILBOS).

I also tried 0%;"CIV";0% in the Format text box -- not sure I understand
changing the field to return a negative number instead of "CIV".

For this approach, the query field would be:
MILBOS%: IIf([EMP_TYPE] = "MIL", CountOfEMP_MIL_BOS /
CountOfID, -.1)
--
Marsh
MVP [MS Access]

Because the calculated field might contain "CIV", the field
must be a Text field. So, you still have the same problem
as before.

Is it really necessary to put the "CIV" in the query? I
mean, can't the report text box deal with that part of the
problem?

I can think if a couple a ways to approach this. One way
would be to change the field to return a negative number
instead of "CIV". Then the report text box's Format
property could be used to display CIV instead of a negative
precent:
0%;"CIV";0%

Another could be to have the query just calculate the
percent:
MILBOS%: CountOfEMP_MIL_BOS / CountOfID
And then use an expression in the report text box:
=IIf(EMP_TYPE = "MIL", [MILBOS%], "CIV")

In general, I prefer using the format property for the same
reason as before, but it probably won't make much difference
which way you go.

Steve Stad wrote:
Marshall - Thank you for reply. It was most helpful and very correct, i.e.,
it is much better to format in the report. I have added additional 'iif'
logic (see below) to some fields in the query which will calculate a numeric
percentage if Emp_type = MIL or write the text 'CIV'. But how should/can I
format this field in the report. The logic works but I lost the Percent
formating in the report for this field.

MILBOS%: IIf([EMP_TYPE]="MIL",([CountOfEMP_MIL_BOS]/[CountOfID]),"CIV")


:
Because users should not see a query's datasheet, your
question is mostly irrelevant (you are the only one that
uses a query's datasheet to verify that the query does what
you want). Use the Format property of the report/form text
box used to display the query's data.

If it's important for you to see 75% instead of .75, set the
field's Format property to Percent or 0% At least this way
you don't convert the field's value from a number to text
and prevent it from being used in calculations.
.

.