Way to allow different formats for different records in same field? 1

[kellbell]

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?

'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!

 

[Melagan]

This is just a guess - not sure if it'll work; I'm assuming you're using this on a form or report.

If [Format] = "Currency" Then
  Me!myField.Format = "Currency"
Elseif [Format] = "Percentage" Then
  Me!myField.Format = "Percent"
End If

..just a shot in the dark. I can't even test it right now.

~Melagan
______
"It's never too late to become what you might have been.

 

[CautionMP]

kellbell,
Why put the format in the table (where it should never be seen by the user)?

[tab][tt]TableDef[/tt] = Table = Data
[tab]Output = Display = Format

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.

If I am following this correctly you should be able to use something like this in a query:
[tt]
[tab]SELECT Format$([tblFinStmtMetrics].[Value], [tblMetrics.[Format])
[tab]FROM tblFinStmtMetrics
[tab]INNER JOIN tblMetrics
[tab]ON [tblMetrics].[MetricName] = [tblFinStmtMetrics].[Metric];[/tt]

Just a thought,
CMP

 

[MajP]

For a report I used the on print event.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Select Case Me.strFormat
    Case "Decimal1"
      Me.dblMetricValue.Format = "0.0"
    Case "Decimal2"
      Me.dblMetricValue.Format = "0.00"
    Case "Currency"
      Me.dblMetricValue.Format = "Currency"
    Case "Percentage"
      Me.dblMetricValue.Format = "Percent"
  End Select
End Sub

my table has this data:
[/code]
strMetricName dblMetricValue strFormat Units
Velocity 20 Decimal1 MPH
Percent Complete 0.2 Percentage
Total Cost 200 Currency Dollars
Length 2 Decimal2 Inches
[/code]

Here is the output

strMetricName	intMetricValue	strFormat  Units
Velocity          20.0	   Decimal1	         MPH
Percent Complete  20.00%   Percentage
Total Cost        $200.00	Currency	         Dollars
Length             2.00	Decimal2	         Inches

 

[MichaelRed]

kellbell, please explain a bit more. Is t he output in a report or form? If so, the same value should appear in the same place (control) each time. The control should include the format as part of the properties? If a form / report is built dynamically, you can and should set the controls properties when the control is instantiated, and thereafter not have a need to individually format the values.




MichaelRed

 

[kellbell]

Thank you very much to everyone for the input! I very much appreciate different ideas to solve the same problem.

To answer your question MichaelRed, the output is in a report. I'm not sure if I completely understand how to set the control properties when the control is instantiated; however, the code provided by MajP appears to work quite well.

My original thought (and thus my original code attempt) was that since a report will default to use the format of the data in the underlying table, that if I could get the format in the underlying table to be what I wanted the output on the report to look like, I would have my problem solved. However, I don't think that Access will allow one field multiple formats in the table. However, as the code from MajP illustrates, I can change the format of the control depending on each record's [Format] value, which accomplishes the same thing I was trying to do.

Thanks again to everyone who offered advice!

 

[MajP]

Kellbel,
After I posted, I realized that having an alias for the format name was extra work. So if you have a field then you can just put the format string in it. The code then becomes

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   Me.dblMetricValue.Format = Nz(Me.strFormat, "Standard")
End Sub

The Null to zero function keeps from throwing an error when you forget to define the formatting for a record

 

[MichaelBiddulph]

Hi Guys - I'm just a novice here but I want to do the same thing on a form - have some values as percentages, some as numbers and some as text. How would I do this?

Michael

 

[MajP]

I have two fields on my form
dblMetricValue : the value
strFormat: a recognized format string for the value
then I have a calculated field
=fncMetricFormat([dblMetricValue],[strFormat])

In a standard module I have a public function

Public Function fncMetricFormat(varVal As Variant, strFormat As Variant) As String
fncMetricFormat = Format(Nz(varVal, 0), Nz(strFormat, "Standard"))
End Function

strFormat field should probably be a combobox filled with acceptable formats.

 

[MichaelBiddulph]

MajP

Thanks for that - it works but doesn't quite do what I want to do. What I'm seeking to do is to enter the Format value (from combobox) then enter a value and display the value correctly formated in the field I entered it. Hope that makes sense! All help gratefully received!

Michael

 

[MajP]

Using the above function. I made a combo called "cboFormat". The values of the combo are format strings: Standard,Percent,Currency, etc. The form is single form view:

Private Sub cboFormat_AfterUpdate()
Me.dblMetricValue.Format = Me.cboFormat
End Sub

As I change the combo the format changes for my field.

However, unless you save the specific format for each record in a table, I am not sure what this gives you. If I come back to this record, there is no history of the format. Can you be more specific on what you are trying to accomplish? Just so you know the formatting is not saved in the field only displayed on the control. In other words you might see $1.00 displayed but there is not a "$" saved.

 

[MichaelRed]

I haven't checked on VB in a while, but MS Access includes conditional formatting for both reports and forms. Usually, VB objects are more robust than Ms Access, so I would expect the VB objects to also support this (conditional formatting).



MichaelRed

 

[MichaelBiddulph]

MajP

I'm trying to maintain a series of target values for each month of the year and the actual values achieved each month. The record has a detailed description of what is to be achieved and target, actual and variance fields for each month of the year. Some of the targets are percentages and some are numerical values. The actual values stored could be numerical as long as they display in the correct format so 20.00% could be stored as 20.

Incidentally, while your code worked as described, if you put in 20 and wanted it formated as % it gave 2000.00%

Any help?

Michael

 

[MajP]

Just like the original post, you are going to have to add a field for each record that holds the format for that field or at least something that identifies that a certain record is a percentage while another is numeric. So in your example you will need a field like dblTarget which is the target value as a double. Then you will need another field called something like strFormat which contains the format string for that record. This field can be bound to a combo. If you only use percents and decimals then you only need to choose percent, the default is Standard.

AFAIK .2 = 20%, and 20 is 2000%.

 

[MichaelBiddulph]

MajP

I have some code that basically divides the number entered by 100 to get around the 20 being displayed as 2000% which I know works but I can't get it to work with the calculated field in your code above. Any idea where I should trigger the code - doesn't work for AfterUpdate which is where I've used it before.

Michael