Format Problem

[Guest]

I am using a DSum function in a query to to perform a calculation on values
in another query. Thanks to Steve for helping me resolve an earlier problem I
had with this, but now I have a new problem. I want the result to be
displayed as a percentage, however, I have no format options for this field
either in the query or in the form that is bound to the query (I guess this
is because it is a domain function, but I'm not sure). I modified the DSum as
follows;

((DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" & [BinID])*100)
& "%")

This does work, except that in cases where the calculation results in zero
all I get is the % sign and nothing else. I tried adding nz but no luck. I
would like it to display either 0% or nothing at all if the calculation
results in zero.

Any ideas?
Thanks in advance for any help.

 

[Allen Browne]

By appending the percent sign, the value is no longer a number. It is now a
piece of text, and the numbers won't add up correctly.

Instead use:
=DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" & [BinID])

Then set the Format *property* of this text box on your form to:
Percent

The fields in your query also have a Format property (Alt+Enter for
properties box) if you needed to do it that way.

 

[Guest]

Allen, thanks for your reply. You have a great website by the way. I've used
it a lot. I have tried to set the format through the properties sheet, but
there are no options in the format line for this field either in the form or
the query (I lose those options as soon as I make it a DSum expression)

What I'm trying to do is display a value that represents how full a
particular bin location is. I want to display it on the same form (a
continuous form) that is used to add new bin locations. I have a query
(qryFillPercent) that performs all the calculations I need, but I can't base
my form directly on this query because I lose the ability to add new records
since it is not a simple select query. I tried using a DSum function as the
control source of an unbound text box on my form, which worked fine except
that it displays #Error in the last line of the form (the new record line).
Someone else in this forum suggested that I add the DSum to a simple query,
then base my form on that query and use the query field as the control source
of the text box. That solved my #Error problem, but now I have my format
problem.

If all this makes any sense, maybe you or someone else has some other ideas
on how I could approach this. Maybe I'm going about it the wrong way.

Thanks for your help.

By appending the percent sign, the value is no longer a number. It is now a
piece of text, and the numbers won't add up correctly.

Instead use:
=DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" & [BinID])

Then set the Format *property* of this text box on your form to:
Percent

The fields in your query also have a Format property (Alt+Enter for
properties box) if you needed to do it that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using a DSum function in a query to to perform a calculation on values
in another query. Thanks to Steve for helping me resolve an earlier
problem I
had with this, but now I have a new problem. I want the result to be
displayed as a percentage, however, I have no format options for this
field
either in the query or in the form that is bound to the query (I guess
this
is because it is a domain function, but I'm not sure). I modified the DSum
as
follows;

((DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" &
[BinID])*100)
& "%")

This does work, except that in cases where the calculation results in zero
all I get is the % sign and nothing else. I tried adding nz but no luck. I
would like it to display either 0% or nothing at all if the calculation
results in zero.

Any ideas?
Thanks in advance for any help.

 

[Allen Browne]

If Access does not offer the numeric formats (such as Percent), it does not
understand the data type as numeric. You need to typecast the expression.

Try something like this (one line):
=CDbl(Nz(DSum("[SumOfFootPrint]/[Area]",
"qryFillPercent","BinID =" & Nz([BinID],0)),0))

Explanation:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for your reply. You have a great website by the way. I've
used
it a lot. I have tried to set the format through the properties sheet, but
there are no options in the format line for this field either in the form
or
the query (I lose those options as soon as I make it a DSum expression)

What I'm trying to do is display a value that represents how full a
particular bin location is. I want to display it on the same form (a
continuous form) that is used to add new bin locations. I have a query
(qryFillPercent) that performs all the calculations I need, but I can't
base
my form directly on this query because I lose the ability to add new
records
since it is not a simple select query. I tried using a DSum function as
the
control source of an unbound text box on my form, which worked fine except
that it displays #Error in the last line of the form (the new record
line).
Someone else in this forum suggested that I add the DSum to a simple
query,
then base my form on that query and use the query field as the control
source
of the text box. That solved my #Error problem, but now I have my format
problem.

If all this makes any sense, maybe you or someone else has some other
ideas
on how I could approach this. Maybe I'm going about it the wrong way.

Thanks for your help.

By appending the percent sign, the value is no longer a number. It is now
a
piece of text, and the numbers won't add up correctly.

Instead use:
=DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" & [BinID])

Then set the Format *property* of this text box on your form to:
Percent

The fields in your query also have a Format property (Alt+Enter for
properties box) if you needed to do it that way.

I am using a DSum function in a query to to perform a calculation on
values
in another query. Thanks to Steve for helping me resolve an earlier
problem I
had with this, but now I have a new problem. I want the result to be
displayed as a percentage, however, I have no format options for this
field
either in the query or in the form that is bound to the query (I guess
this
is because it is a domain function, but I'm not sure). I modified the
DSum
as
follows;

((DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" &
[BinID])*100)
& "%")

This does work, except that in cases where the calculation results in
zero
all I get is the % sign and nothing else. I tried adding nz but no
luck. I
would like it to display either 0% or nothing at all if the calculation
results in zero.

 

[Guest]

That worked! Thank you very much. I'll check your link when I get time to get
better idea of how exactly that works.

Thanks again for your time.

If Access does not offer the numeric formats (such as Percent), it does not
understand the data type as numeric. You need to typecast the expression.

Try something like this (one line):
=CDbl(Nz(DSum("[SumOfFootPrint]/[Area]",
"qryFillPercent","BinID =" & Nz([BinID],0)),0))

Explanation:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for your reply. You have a great website by the way. I've
used
it a lot. I have tried to set the format through the properties sheet, but
there are no options in the format line for this field either in the form
or
the query (I lose those options as soon as I make it a DSum expression)

What I'm trying to do is display a value that represents how full a
particular bin location is. I want to display it on the same form (a
continuous form) that is used to add new bin locations. I have a query
(qryFillPercent) that performs all the calculations I need, but I can't
base
my form directly on this query because I lose the ability to add new
records
since it is not a simple select query. I tried using a DSum function as
the
control source of an unbound text box on my form, which worked fine except
that it displays #Error in the last line of the form (the new record
line).
Someone else in this forum suggested that I add the DSum to a simple
query,
then base my form on that query and use the query field as the control
source
of the text box. That solved my #Error problem, but now I have my format
problem.

If all this makes any sense, maybe you or someone else has some other
ideas
on how I could approach this. Maybe I'm going about it the wrong way.

Thanks for your help.

By appending the percent sign, the value is no longer a number. It is now
a
piece of text, and the numbers won't add up correctly.

Instead use:
=DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" & [BinID])

Then set the Format *property* of this text box on your form to:
Percent

The fields in your query also have a Format property (Alt+Enter for
properties box) if you needed to do it that way.

I am using a DSum function in a query to to perform a calculation on
values
in another query. Thanks to Steve for helping me resolve an earlier
problem I
had with this, but now I have a new problem. I want the result to be
displayed as a percentage, however, I have no format options for this
field
either in the query or in the form that is bound to the query (I guess
this
is because it is a domain function, but I'm not sure). I modified the
DSum
as
follows;

((DSum("[SumOfFootPrint]/[Area]","qryFillPercent","BinID =" &
[BinID])*100)
& "%")

This does work, except that in cases where the calculation results in
zero
all I get is the % sign and nothing else. I tried adding nz but no
luck. I
would like it to display either 0% or nothing at all if the calculation
results in zero.