Conditional Number Format in Continuous Forms Design Layout 1

[kjv1611]

I realize this is probably a long-shot. I imagine if it's possible at all, it'll only be via VBA, and even then, I'm not sure it's possible.

Is there any way to have "conditional formatting" for the number format, based on either the value of a given field? So if I want the "Amount" field to be a percent in only one circumstance, but otherwise be a currency field?

I realize this sounds crazy, but it's something I'd like to do if possible.

Thanks for any thoughts/suggestions.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

No. Also sounds like a non normalized table. you will not be able to do this in VBA either. The only way to do this would be to have a seperate display control. You will not be able to enter a number and then in the same control apply different format. You could enter a value in a control and then next to it show it as either percent or currency. That control would be a calculated control

 

[kjv1611]

Thanks, yeah, that's what I figured. It's actually a combined query. I realize it sounds crazy, but it's a method we have for summarizing some different but related pieces of data in the same table. I have the query (set of queries) working correctly, and I spit it out to Excel for reporting, but wanted to show it on a UserForm the same way. Oh well, it's not required, just an idea.

Thanks for bursting my bubble in as nice a way possible.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

But if you are not entering data, but only displaying the data you can then do this. You can build a function and then display the calculated field.

 

[kjv1611]

Hmm, I'll have to think about doing that, then. The data is being entered via another database form. The location where I want the different formatting is in a form that will just be for vieweing (historically, just done without this, but I thought it'd be nice to build a "dashboard" screen that includes different items including this small query. It's a series of queries to get everything to line up the way it shows in the end.

Since it's a query, and I'm showing it in a form in "Continuous Forms" format, I'm wondering if I can just stick a calculated field on there. I wouldn't be able to add it to the same location. I suppose I could show that separately instead? Or is there a way to add a field to a Continuous form in such a way that it'll "cover" the desired value/field in one record?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

Something like this

Public Function FormatPercentOrCurrency(val As Variant, FormatType As Variant) As Variant
  If Not IsNull(val) And Not IsNull(FormatType) Then
     If FormatType = "Currency" Then
       FormatPercentOrCurrency = FormatCurrency(val, 2)
     ElseIf FormatType = "Percent" Then
       FormatPercentOrCurrency = FormatPercent(val, 1)
     Else
       FormatPercentOrCurrency = val
     End If
  End If
End Function

SELECT 
 tblData.TypeFormat, 
 tblData.NumberValue, 
 FormatPercentOrCurrency([NumberValue],[typeFormat]) AS Display
FROM tblData;

[tt]
TypeFormat	NumberValue	Display
Percent	        0.45           45.0%
Currency        100.75         $100.75
Percent	        0.09           9.0%
Currency	54.25          $54.25
[/tt]

 

[kjv1611]

Awesome! That's really pulling the pieces together! Thanks a ton for the suggestion. Unfortunately, I don't have time to test that out just now. I've got several deadlines looming that require my attention (unrelated). Most likely,t he earliest I'll be able to look back at this will be the 17th or 18th. So assuming I don't forget, and don't have more pressing items those days, I'll get back to this item.

Thanks again for the suggestion, and particularly for going into such detail. Makes it easy to see that it very well should work.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

If you wanted to just have the one column there is a way you can still type into that field. So assume I just have a single control, and initially bound to the Display field.

Private Sub txtBxDisplay_Enter()
  Me.txtBxDisplay.ControlSource = "NumberValue"
End Sub

Private Sub txtBxDisplay_Exit(Cancel As Integer)
  Me.txtBxDisplay.ControlSource = "Display"
End Sub

when you enter the field it changes the control source to the value field. Of course all the records will display without any formatting. One you leave it changes the controlsource back.