Conditional Formatting based on column number

trishcollins

Registered User.
Local time
Today, 00:49
Joined
Mar 23, 2011
Messages
42
Is it possible to apply conditional formatting on a field in a report based on the column number relative to other columns. ie Jan is in column 2, Feb is in column 3, Mar is in Column 4? The conditional formatting is to identify the fields which are "future" and only estimates, based on the fiscal year filter for the report and the current date.

Here is the condition formatting I want to apply, but I want the number to increase relative to the what column it's in.

([Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]=DatePart("yyyy",Now()) And DatePart("m",Now())<1)

The "1" currently stands for Jan. Right now, I would have to go in a replace the 1 with a 2 for conditional formatting to apply to the Feb column, etc.

Instead, I would like to replace the "1" with a variable that pulls the column position number, so I don't have to edit each individual conditional formatting (which given I sum the data three times, is a lot of work). For example, if the variable was called "column_number", then I would repace the "1" with "column_number -1" to get the right number relative to the month.

Is this possible?

Thanks in advance...Trish :)
 
You could try:

([Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]=DatePart("yyyy",Now()) And DatePart("m",Now())<Month([DateField]))
 
You could try:

([Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]=DatePart("yyyy",Now()) And DatePart("m",Now())<Month([DateField]))

It's hard to explain, but there is no "datefield" to pull the month from -- that would just make it SO easy.

I am creating a report that uses three dates, a start date (when the service became active), an end date (if the service has been canceled). The end date field can actually be blank. And the fiscal year that the user has requested via the form.

The query decides if the monthly recurring charge for the service should be including in the column of the report or not. In the report, I have created 13 columns, the first 12 sum the amount if the query has deemed it to be part of the report, and the last column is when I simply add all the columns together.

Here is the query for the Monthly Recurring Charge (MRC) just for Jan:

PHP:
MRC_Jan: IIf((IsNull([FiscalYearEndDate]) And [FiscalYearStartDate]<[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]) Or (IsNull([FiscalYearEndDate]) And [FiscalYearStartDate]=[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] And [FiscalYearStartMonth]<=1) Or ([FiscalYearEndDate]=[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] And [FiscalYearEndMonth]>=1),[MRC],0)

In the query, there is one of these fields for each month. However, if the word "Jan" appears in the MRC name, maybe there is a way to extract it, turn it into a numberic value and use it to my advantage. Thoughts?
 
Create a function to resolve "Jan", "Feb", ... etc and use that function in the conditional formatting.
 
Create a function to resolve "Jan", "Feb", ... etc and use that function in the conditional formatting.

That's what I need to do, but no idea how to do it. I need to extract a portion of a fieldname from a query. Once I extract that, I can easily resolve the months with a number.

Can you provide some insight?
 
Some aircode
Code:
Public Function GetMonthInt(strField As String) As Integer
    Select Case strField
        Case "Jan"
            GetMonthInt = 1
        Case "Feb"
            GetMonthInt = 2
    End Select
End Function
... so on.

To call:
Code:
[FONT=Calibri][SIZE=3]([Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]=DatePart("yyyy",Now()) And DatePart("m",Now())<[B][COLOR=red]GetMonthInt([Field])[/COLOR][/B])[/SIZE][/FONT]
 
Sorry, I am a bit confused. I understand the resolution part, but I am not sure what put in "field" variable. I want the field name to be used, but don't want to have to change it manually, so putting in GetMonthInt([MRC_Jan]) doesn't really work. I need to extract the month (Jan, Feb, Mar) from the field NAME I gave it in the query before I can resolve it. Am I missing something?
 
I would need to see your db (i.e. a stripped down version) to get a better idea what you're doing.
 
Okay, the name of the field I created in the query is "MRC_Jan" (MRC_Feb, MRC_Mar, etc.). The field is an expression which determines if the MRC should be included in that month for each service instance, and if so, [MRC_Jan] = [MRC], if not then [MRC_Jan]=0.

I then use [MRC_Jan] as the control source for the Jan column of the report. The Feb column uses [MRC_Feb] as the control source, the Mar column the [MRC_Mar] as the control source, etc. I then sum based on various groupings using the control source =sum([MRC_Jan]). This is repeated for all 12 months.

If I can actually extract the last three characters from relative control source, and then resolve it against a the month number, store than number to a variable, I can then use that variable to replace the "1" in the conditional formating.

Does that make sense?
 
Getting the last three characters is not a problem - you can use the Right() function. But how do you reference the name of the field?

I guess you can use the ControlSource property. So,

Right([TextboxName].[ControlSource], 3)
 
Getting the last three characters is not a problem - you can use the Right() function. But how do you reference the name of the field?

I guess you can use the ControlSource property. So,

Right([TextboxName].[ControlSource], 3)

Thanks. That's what I was trying to find out. Will give that a try and see if it works and let you know.
 
Okay, finally got around to creating the public function, now looking at the conditional formatting. Does the TextBoxName need to be the actual name, or can it default to the textbox you are in. If it needs to be the actual textbox name, we are back to square one, as I would have to change this for each instance, which equates to minimum of 52 changes to the conditional formatting (0ne detail line with 13 fields, and three summary lines, each with 13 sums).

I also realized that to apply the same conditonal formatting where I am extracting the short month from the controlsource, I would need to find the starting position of the "_" first, and then extract the next three characters, as the control source may include =sum([MRC_Apr]) and therefore using the right function to extract the last three characters would be incorrect. Looking at the syntax:

Mid([TextboxName].[ControlSource], inStr([TextboxName].[ControlSource],"_"),3)

Thoughts?
 
Here's a different approach that doesn't use conditional formatting. Instead, the code just runs at the point before you want to format your row of text boxes. So you could put it in the On Open event of your report if all the rows have the same colour pattern:

Code:
For Each ctrl In me.Controls
	If Left(ctrl.name,4) = "MRC_" Then
		If Month(DateValue("1 " & Right(ctrl.name,3) & " 2000")) < DatePart("m",Now()) Then
			ctrl.Forecolor = vbRed
		Else
			ctrl.Forecolor = vbBlue
		End If
	End If
Next ctrl

You'll need to add the fiscal year test also.

hth
Chris
 
Well, there you have it. A different approach from Chris :)

You might need to replace Left() = "MRC_" with an Instr() test because you mentioned that some of the control names might contain =Sum(). Or you could do a:
Code:
Select Case Mid([[COLOR=red]TextboxName[/COLOR]].[ControlSource], inStr([[COLOR=red]TextboxName[/COLOR]].[ControlSource],"_"),3)
 
I gave up and finally just put conditional formatting on the cells. No matter which way I spliced it, I seemed to have to reference the actual cell name. It's all working now, although it took a bit of time to finally get them all formatted :)
 

Users who are viewing this thread

Back
Top Bottom