Number Formatting

[bharlow]

It's been a long time since I've been out to this board and I'm glad to see it's still going strong.

My question....

Is there a way to use an if then statement to format numbers differently in each row of data returned in a report? For example, I have created a report that returns 25 rows of data for each store but some rows are percentages based on calculations and some are currency. Conditional formatting does not change number formats as far as I can tell.

Thanks

 

[Uncle Gizmo]

The problem is that the underlying data is numeretic so you could format them as either percentage or currency but only the whole set of data.

The real question is how do you determine which ones should be formatted as currency and which ones should be formatted as percentage?

 

[bharlow]

Each row has a format designation of A or B based on what number format it should be with A being currency and B being percent. It looks like this...

Item FormatType JAN FEB MAR
Sales A 100 100 100
Margin B 50 50 50

It should look like...

Item FormatType JAN FEB MAR
Sales A 100 100 100
Margin B 50% 50% 50%

Thanks for you help.

 

[scalextric59]

Try something like this on Detail_Format

Add a textbox for the field that stores the format and set its visible property to 'No'. Suppose that the textbox name is 'fmt'...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.fmt = "A" Then
Me.<numeric_field>.Format = "General Number"
Else
Me.<numeric_field>.Format = "Percent"
End If

End Sub

 

[bharlow]

scalextric59....thank you very much for your help! This site has always proved to be a valuable resource of knowledge.

Here is the code I used...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.fmt = "A" Then
Me.[1].Format = "#,##0"
Me.[2].Format = "#,##0"
Me.[3].Format = "#,##0"
Me.[Q1].Format = "#,##0"
Else
Me.[1].Format = "0.0%"
Me.[2].Format = "0.0%"
Me.[3].Format = "0.0%"
Me.[Q1].Format = "0.0%"
End If


It worked Perfect