Multiple Formats 1

[sabloomer]

I tried to post this yesterday, so I am sorry if this is repeated.


I have a report that is built off of a temp table. I load the table with sales history as well as forecast and budget info. In addition, I add some calculated variance information. I then use a cross-tab query to put the months along the top and the data types down the side. In the past this worked great! This year they are asking me to add some lines for variances as a percent. In the past the report had all of the fields formatted as currency, because they all were. Now I need some way of tell the text boxes in my reports that when the data type is "Variance Percent" don't format the number as currency, but instead as a percent.

I have looked at conditional formatting, but I can only change font attributes, not attributes related to the control. IS there any way to do this besides having a field contain "$" or "%" and concatenate it with a formula on the report? That sounds like such a major pain, seing that I would also have to insert the comma into the string to make it look like a number.

Thank you to anyone that can help shed some light on this issue.

sabloomer

 

[dhookom]

Where are you applying the format? I would suggest that you only format controls, not columns/fields in a query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[sabloomer]

Duane,

Thank you for the quick response. I do not apply any formatting to the table or the query built on that table. All formatting is applied to the text boxes on the report.

Thanks,

sabloomer

 

[dhookom]

You can use code in the On Format event of a report section to change almost any property of any control in the section.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[sabloomer]

I have not used that before. Can you explain how to use the On Format event to format a text box(es) in my "Detail" section? I can not find any examples in the Microsoft help.

Thanks,

sabloomer

 

[dhookom]

While in design view of a report, you can view the properties dialog for a particular report section. Find the On Format event and click the builder button [...]. Select code builder to view something that looks like:

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

End Sub

You can enter your own code in the middle to modify properties:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If Me.txtType = "currency" Then
       Me.txtOne.Format = "Currency"
     Else
       Me.txtOne.Format = "Percent"
   End If
End Sub

The txtType would be your control that determines what you need for the format value. You need to change the control names and possible add your other controls.



Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[sabloomer]

If I could give you more then one star I would! From what I have read it looks like I can change the calculation in that text box as well. That should allow me to adjust the controls that I have set up to give me totals for the quarters.

Thank you for all of your help,

sabloomer

 

[dhookom]

I don't think you can change the control source once printing has started. I would question any specification that required this. You can also make controls visible or not which might be a viable option for you.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]