Text Data to Numeric for Report

[Guest]

I've been away from Access for a while and I'm having a problem with using
numeric and non-numeric data in a Text field.

My Access table (tblProjects) was exported from an Excel file. Six exported
fields (Capital, AnnualizedSavings, BudgetedSavings2005, BudgetedSavings2006,
BudgetedSavings2007, RevisedSavings2007) contain numeric data to represent
dollar monetary values. However, several of these same fields also contain
non-numeric values (N/A, TBD, CA) to indicate unknown or unassigned dollar
values ("Not Applicable", "To Be Determined", "Cost Avoidance"). To properly
capture all the numeric and non-numeric data, these fields were imported into
the Access table as Text.

I now need to properly format and display the data from these six fields in
a report. Additionally, I need to keep running subtotals and final totals
for these fields that have a numeric value.

If one of these fields does not contain one of the non-numeric values (N/A,
TBD, or CA), then the report must format and display the data as Currency
(showing $ symbol and decimal places). These same numeric value fields must
also calculate running subtotals/final totals for the report.

My problem is how to handle the fields with the non-numeric values (N/A,
TBD, CA) in regards to display formatting and running totals on the report.

For example, the Control Source for the BudgetedSavings2006 field is the
following statement:

=IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),"0")

The report's design view is indicating a "circular reference" error for this
field. The report preview is showing a value of "0" for all the records for
this field, even the ones that have a valid numeric value.

Can someone guide me to the correct way to resolve these issues for the
report?

 

[Marshall Barton]

I've been away from Access for a while and I'm having a problem with using
numeric and non-numeric data in a Text field.

My Access table (tblProjects) was exported from an Excel file. Six exported
fields (Capital, AnnualizedSavings, BudgetedSavings2005, BudgetedSavings2006,
BudgetedSavings2007, RevisedSavings2007) contain numeric data to represent
dollar monetary values. However, several of these same fields also contain
non-numeric values (N/A, TBD, CA) to indicate unknown or unassigned dollar
values ("Not Applicable", "To Be Determined", "Cost Avoidance"). To properly
capture all the numeric and non-numeric data, these fields were imported into
the Access table as Text.

I now need to properly format and display the data from these six fields in
a report. Additionally, I need to keep running subtotals and final totals
for these fields that have a numeric value.

If one of these fields does not contain one of the non-numeric values (N/A,
TBD, or CA), then the report must format and display the data as Currency
(showing $ symbol and decimal places). These same numeric value fields must
also calculate running subtotals/final totals for the report.

My problem is how to handle the fields with the non-numeric values (N/A,
TBD, CA) in regards to display formatting and running totals on the report.

For example, the Control Source for the BudgetedSavings2006 field is the
following statement:

=IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),"0")

The report's design view is indicating a "circular reference" error for this
field. The report preview is showing a value of "0" for all the records for
this field, even the ones that have a valid numeric value.

The circular reference is because the text box has the same
name as a record source field. Change the name of the text
box to something ele such as txtBudgetedSavings2006

Set the Control Source for the txtBudgetedSavings2006 text
box to something more like:

=IIf(IsNumeric([BudgetedSavings2006]),Format(Val([BudgetedSavings2006],Currency)),[BudgetedSavings2006])

To calculate the total use a text box in the group and/or
report footer with an expression like:

=Sum(IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),0))

 

[Guest]

Thank you Marsh, that addressed my problem perfectly!

One note, instead of Format in your expression, I used FormatCurrency, as
follows:

=IIf(IsNumeric([BudgetedSavings2006]),FormatCurrency(Val([BudgetedSavings2006])),[BudgetedSavings2006])

I'm using Access 2003 and when I used your expression as written I received
an error. Changing Format to FormatCurrency resolved the error.

Thank you very, very much for your timely response!

I've been away from Access for a while and I'm having a problem with using
numeric and non-numeric data in a Text field.

My Access table (tblProjects) was exported from an Excel file. Six exported
fields (Capital, AnnualizedSavings, BudgetedSavings2005, BudgetedSavings2006,
BudgetedSavings2007, RevisedSavings2007) contain numeric data to represent
dollar monetary values. However, several of these same fields also contain
non-numeric values (N/A, TBD, CA) to indicate unknown or unassigned dollar
values ("Not Applicable", "To Be Determined", "Cost Avoidance"). To properly
capture all the numeric and non-numeric data, these fields were imported into
the Access table as Text.

I now need to properly format and display the data from these six fields in
a report. Additionally, I need to keep running subtotals and final totals
for these fields that have a numeric value.

If one of these fields does not contain one of the non-numeric values (N/A,
TBD, or CA), then the report must format and display the data as Currency
(showing $ symbol and decimal places). These same numeric value fields must
also calculate running subtotals/final totals for the report.

My problem is how to handle the fields with the non-numeric values (N/A,
TBD, CA) in regards to display formatting and running totals on the report.

For example, the Control Source for the BudgetedSavings2006 field is the
following statement:

=IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),"0")

The report's design view is indicating a "circular reference" error for this
field. The report preview is showing a value of "0" for all the records for
this field, even the ones that have a valid numeric value.

The circular reference is because the text box has the same
name as a record source field. Change the name of the text
box to something ele such as txtBudgetedSavings2006

Set the Control Source for the txtBudgetedSavings2006 text
box to something more like:

=IIf(IsNumeric([BudgetedSavings2006]),Format(Val([BudgetedSavings2006],Currency)),[BudgetedSavings2006])

To calculate the total use a text box in the group and/or
report footer with an expression like:

=Sum(IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),0))

 

[Marshall Barton]

That's a good alternative. The error was because I should
have put quotes around "Currency".
--
Marsh
MVP [MS Access]

Thank you Marsh, that addressed my problem perfectly!

One note, instead of Format in your expression, I used FormatCurrency, as
follows:

=IIf(IsNumeric([BudgetedSavings2006]),FormatCurrency(Val([BudgetedSavings2006])),[BudgetedSavings2006])

I'm using Access 2003 and when I used your expression as written I received
an error. Changing Format to FormatCurrency resolved the error.

Thank you very, very much for your timely response!

I've been away from Access for a while and I'm having a problem with using
numeric and non-numeric data in a Text field.

My Access table (tblProjects) was exported from an Excel file. Six exported
fields (Capital, AnnualizedSavings, BudgetedSavings2005, BudgetedSavings2006,
BudgetedSavings2007, RevisedSavings2007) contain numeric data to represent
dollar monetary values. However, several of these same fields also contain
non-numeric values (N/A, TBD, CA) to indicate unknown or unassigned dollar
values ("Not Applicable", "To Be Determined", "Cost Avoidance"). To properly
capture all the numeric and non-numeric data, these fields were imported into
the Access table as Text.

I now need to properly format and display the data from these six fields in
a report. Additionally, I need to keep running subtotals and final totals
for these fields that have a numeric value.

If one of these fields does not contain one of the non-numeric values (N/A,
TBD, or CA), then the report must format and display the data as Currency
(showing $ symbol and decimal places). These same numeric value fields must
also calculate running subtotals/final totals for the report.

My problem is how to handle the fields with the non-numeric values (N/A,
TBD, CA) in regards to display formatting and running totals on the report.

For example, the Control Source for the BudgetedSavings2006 field is the
following statement:

=IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),"0")

The report's design view is indicating a "circular reference" error for this
field. The report preview is showing a value of "0" for all the records for
this field, even the ones that have a valid numeric value.

The circular reference is because the text box has the same
name as a record source field. Change the name of the text
box to something ele such as txtBudgetedSavings2006

Set the Control Source for the txtBudgetedSavings2006 text
box to something more like:

=IIf(IsNumeric([BudgetedSavings2006]),Format(Val([BudgetedSavings2006],Currency)),[BudgetedSavings2006])

To calculate the total use a text box in the group and/or
report footer with an expression like:

=Sum(IIf(IsNumeric([BudgetedSavings2006]),Val([BudgetedSavings2006]),0))