Hi,
I am working on a database that's main purpose is to output financial metrics. My problem is that not all metrics are formatted the same. For example, Advertising as a % of Gross Profit is a percentage, while Gross Profit per unit is Currency (with no decimal points). I have a table that lists the metrics to be used in the report and has a field [Format] that states what format the metric should be in.
I have tried using code to assign a different format to the metric depending on whether or not the metric is a percentage or a dollar as seen below; however, all this does is have all values as the same format depending on which metric the loop ended on last.
Does anyone know if there is any way to allow different formats for different records in the same field? Or am I looking at having to modify my table design?
Thanks in advance!
I am working on a database that's main purpose is to output financial metrics. My problem is that not all metrics are formatted the same. For example, Advertising as a % of Gross Profit is a percentage, while Gross Profit per unit is Currency (with no decimal points). I have a table that lists the metrics to be used in the report and has a field [Format] that states what format the metric should be in.
I have tried using code to assign a different format to the metric depending on whether or not the metric is a percentage or a dollar as seen below; however, all this does is have all values as the same format depending on which metric the loop ended on last.
Does anyone know if there is any way to allow different formats for different records in the same field? Or am I looking at having to modify my table design?
Code:
'need to somehow set format so that it is different depending on the metric
Dim fldAmount As Field
Dim tblTableObject As TableDef
Dim prpForProp As Property
Dim strFormat As String
strFormat = Nz(rsMetrics("[Format]"), "Standard")
Set tblTableObject = db.TableDefs("tblFinStmtMetrics")
Set fldAmount = tblTableObject.Fields("Value")
If strFormat = "Currency (no decimals)" Then
Set prpForProp = fldAmount.CreateProperty("Format", dbText, "$#,###")
fldAmount.Properties.Delete ("Format")
fldAmount.Properties.Append prpForProp
ElseIf strFormat = "Percent" Then
Set prpForProp = fldAmount.CreateProperty("Format", dbText, "percent")
fldAmount.Properties.Delete ("Format")
fldAmount.Properties.Append prpForProp
End If
Thanks in advance!