M
mikeg710
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?
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?