Erratic Report Field Format

[richaluft]

Hi:
Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").

If the report is opened using command DoCmd.OpenReport "Invoice
1500-1", then all fields are formatted correctly, and TotalCharges
might show a correct result as 2800 00.
However, I sometimes use the following command sequence:
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:=""
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:="Copy"
DoCmd.OpenReport "Invoice 1500-1"
When I print using this sequence, the third line prints these fields
improperly as , for example, "2800", which amounts to $28.00 on the
invoice instead of $2800.00.

Can someone provide a remedy for this erratic performance?
TIA, Richard

 

[Duane Hookom]

The Replace() function according to Help (and my experience) "Returns a
string in which a specified substring has been replaced with another
substring a specified number of times."

You are treating the result of Replace() as if it is numeric. I don't know
what your values look like before or after the Replace() but if you want the
value to be treated as a numeric, try wrapping it in Val() or CDbl() or
something.

 

[richaluft]

The Replace() function according to Help (and my experience) "Returns a
string in which a specified substring has been replaced with another
substring a specified number of times."

You are treating the result of Replace() as if it is numeric. I don't know
what your values look like before or after the Replace() but if you want the
value to be treated as a numeric, try wrapping it in Val() or CDbl() or
something.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCPhttp://www.access.hookom.net/UCP/Default.htm

Hi:
Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places =  2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").

If the report is opened using command DoCmd.OpenReport "Invoice
1500-1", then all fields are formatted correctly, and TotalCharges
might show a correct result as 2800 00.
However, I sometimes use the following command sequence:
            DoCmd.OpenReport "RptSelfPayNon-Par", openargs:=""
            DoCmd.OpenReport "RptSelfPayNon-Par", openargs:="Copy"
            DoCmd.OpenReport "Invoice 1500-1"
When I print using this sequence, the third line prints these fields
improperly  as , for example, "2800", which amounts to $28.00 on the
invoice instead of $2800.00.

Can someone provide a remedy for this erratic performance?
TIA, Richard- Hide quoted text -

- Show quoted text -

Duane;
Your reply helped me identify that my problem lies in the fact that
Replace() works correctly as long as my field entry doesn't have .00
for the cents portion of the entry. As long as the field has a
positive 'cents' value, it functions. All the rest of what I wrote is
irrelevant.
Now, to try to convert my Currency field into a string--------.
Regards, Richard

 

[Duane Hookom]

Can I ask why you would want to change a currency field into a string?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The Replace() function according to Help (and my experience) "Returns a
string in which a specified substring has been replaced with another
substring a specified number of times."

You are treating the result of Replace() as if it is numeric. I don't know
what your values look like before or after the Replace() but if you want the
value to be treated as a numeric, try wrapping it in Val() or CDbl() or
something.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCPhttp://www.access.hookom.net/UCP/Default.htm

Hi:
Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").

If the report is opened using command DoCmd.OpenReport "Invoice
1500-1", then all fields are formatted correctly, and TotalCharges
might show a correct result as 2800 00.
However, I sometimes use the following command sequence:
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:=""
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:="Copy"
DoCmd.OpenReport "Invoice 1500-1"
When I print using this sequence, the third line prints these fields
improperly as , for example, "2800", which amounts to $28.00 on the
invoice instead of $2800.00.

Can someone provide a remedy for this erratic performance?
TIA, Richard- Hide quoted text -

- Show quoted text -

Duane;
Your reply helped me identify that my problem lies in the fact that
Replace() works correctly as long as my field entry doesn't have .00
for the cents portion of the entry. As long as the field has a
positive 'cents' value, it functions. All the rest of what I wrote is
irrelevant.
Now, to try to convert my Currency field into a string--------.
Regards, Richard

 

[Marshall Barton]

Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").

You seem to have a gross misperception of what a control's
Format and DecimalPlaces properties do. Those formatting
properties are only used after all calulations are completed
and then only for display purposes. When you used Replace,
it operates on the **value**, which has not be sujected to
any formatting. Furthermore, the value must be converted to
a string before Replace can do anything. This means that a
value with .00 will be converted to a string such as 123
without a decimal point and thus you see what you call a
problem. After Replace does its thing the value is a text
string and all numeric formatting will be ignored.

The txtCharges text box should not have any formatting and
use an expression like this to calculate your oddly
formatted string for display:
=Replace(Format(Charges, "$0.00"), ".", " ")
Note: The text box name must be different from field name.

The footer text box, [Sum Charges], with no formatting,
could use an expression like this to display the total:
=Replace(Format(=Sum(Charges), "$0.00"), ".", " ")

Because the values of bot those text boxes are text strings,
neither on can be used in other calculations.

 

[richaluft]

Can I ask why you would want to change a currency field into a string?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCPhttp://www.access.hookom.net/UCP/Default.htm

The Replace() function according to Help (and my experience) "Returns a
string in which a specified substring has been replaced with another
substring a specified number of times."
You are treating the result of Replace() as if it is numeric. I don't know
what your values look like before or after the Replace() but if you want the
value to be treated as a numeric, try wrapping it in Val() or CDbl() or
something.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCPhttp://www.access.hookom.net/UCP/Default.htm
:
Hi:
Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").
If the report is opened using command DoCmd.OpenReport "Invoice
1500-1", then all fields are formatted correctly, and TotalCharges
might show a correct result as 2800 00.
However, I sometimes use the following command sequence:
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:=""
DoCmd.OpenReport "RptSelfPayNon-Par", openargs:="Copy"
DoCmd.OpenReport "Invoice 1500-1"
When I print using this sequence, the third line prints these fields
improperly as , for example, "2800", which amounts to $28.00 on the
invoice instead of $2800.00.
Can someone provide a remedy for this erratic performance?
TIA, Richard- Hide quoted text -
- Show quoted text -

Duane;
Your reply helped me identify that my problem lies in the fact that
Replace() works correctly as long as my field entry doesn't have .00
for the cents portion of the entry. As long as the field has a
positive 'cents' value, it functions. All the rest of what I wrote is
irrelevant.
Now, to try to convert my Currency field into a string--------.
Regards, Richard

Duane:
I don't Want to change the field into a string, but on the report, the
currency values must be expressed as #### ##
with a 'space' rather than a decimal. Thus, I've been using the
Replace function on a currency value, which, as I said, seems to work
fine aslong as there is a value present (other than 00) in the 'cents'
portion of the value.

 

[richaluft]

Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
=Replace([ChargesApproved],"."," ").
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a field "TotalCharges",
where Format = Fixed, Decimal places = 2, and
controlsource =Replace([INVOICE1500subform].[Report]!
[Sumcharges],"."," ").

You seem to have a gross misperception of what a control's
Format and DecimalPlaces properties do. Those formatting
properties are only used after all calulations are completed
and then only for display purposes. When you used Replace,
it operates on the **value**, which has not be sujected to
any formatting. Furthermore, the value must be converted to
a string before Replace can do anything. This means that a
value with .00 will be converted to a string such as 123
without a decimal point and thus you see what you call a
problem. After Replace does its thing the value is a text
string and all numeric formatting will be ignored.

The txtCharges text box should not have any formatting and
use an expression like this to calculate your oddly
formatted string for display:
=Replace(Format(Charges, "$0.00"), ".", " ")
Note: The text box name must be different from field name.

The footer text box, [Sum Charges], with no formatting,
could use an expression like this to display the total:
=Replace(Format(=Sum(Charges), "$0.00"), ".", " ")

Because the values of bot those text boxes are text strings,
neither on can be used in other calculations.

Marsh;
Thanks for solution to problem. Since $ also has to be eliminated,
the format specs are "0.00" and that remedies the problem
Happy holidays!