Conditional formatting in a report

[Alireza355]

Dear all,

I have a report with some fields in it. I want the allignment of Field1 be Left-alligned if Field2 is >0 and Right-alligned if Field3 is >0

Note: It is not possible for both Field2 and Field3 to be >0 at the same time.

Is this possible????????????

[Alireza355]

I also want to do the same in a subform.

Does anyone have any idea how?

[ADezii]

@Alireza355

Is this possible????????????

It is very possible, and it would be done in the Format() Event of the Report's Detail Section, as in:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const conALIGN_GENERAL = 0
Const conALIGN_LEFT = 1
Const conALIGN_RIGHT = 3
 
If Me![Field2] > 0 Then
  Me![Field1].TextAlign = conALIGN_LEFT
ElseIf Me![Field3] > 0 Then
  Me![Field1].TextAlign = conALIGN_RIGHT
Else        'a Catch-All, align General
  Me![Field1].TextAlign = conALIGN_GENERAL
End If
End Sub

[NeoPa]

Are you sure this works ADezii?

I would have thought this would change the format of the control a number of times before the page is formatted. Whichever was the last to run would determine the eventual display of the control for all records of that page.

Essentially the format of the control is associated with the control (of which there is only one instance).

My answer would be that it's not possible, unless you formatted the data of each record with a Format() function call. There are various drawbacks to this method (Controlling the format is not straightforward; The result becomes a string and no longer numeric; etc), but I see no other way working.

[OldBirdman]

If Field1 is a calculated value, then NeoPa's idea would work. It wouldn't matter if a numeric became a string. The font would be best if constant-width characters were used. So assuming a width for Field1 of 10 characters, then

Field1 = String(10 - Len(Format(Field2 + Field3, "#########0")), " ") & Location

[ADezii]

@NeoPa

Are you sure this works ADezii?

Actually, I'm 100% sure that it DOESN'T WORK! Oops, and sorry for the misinformation, guys. Nice pickup, NeoPa, and thanks.

[Alireza355]

I got it working this way in my report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
If Field1 > 0 Then
Text137.TextAlign = 3
End If
 
If Field2> 0 Then
Text137.TextAlign = 1
End If
 
End Sub

But now I'm wondering how to do it in a subform........ Any idea? (:

[NeoPa]

Ali.

Please review posts #4 & #6.

That cannot work correctly, even though it may seem to in some circumstances.

[FishVal]

@NeoPa
;)

Actually it does work corrrectly.

Regards,
Fish.

[ADezii]

@FishVal
Hello Fish, you mean that I was Right, then Wrong, then Right again, all in the same Thread? (LOL)

[FishVal]

@ADezii
Did you enjoy that?
Do you want a second go? LOL.

[NeoPa]

@FishVal
Can you explain?

[FishVal]

@NeoPa
There is nothing to explain.
It works (at least in Access 2003). Try and see.

[NeoPa]

Nothing to explain :S

Looking more closely, it does seem like using the Detail_Format() event procedure renders the stated problem irrelevant. As this event occurs for each detail record displayed, there is no possibility of incorrect carry-over (see post #4).

Well done for pointing out that it works anyway. You're obviously spot on there Fish.

[Alireza355]

What I wrote in post number 7 IS working PERFECTLY.

But now, I want to do the same in a subform, and can't ):

[NeoPa]

@Alireza355
Yes. That's the conclusion we've all reached now Ali. I was mistaken earlier.
@Alireza355
You will need to help us here a little.

If that works in the main form, why is putting the same code in the form you use in your subform not working for you? It seems very straightforward. It makes it hard to know what the problem is. Perhaps if you explain what you've tried and what results you saw that indicate that it's not working we could help.

[Alireza355]

The code I gave to you earlier:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
 
If Field1 > 0 Then 
Text137.TextAlign = 3 
End If 
 
If Field2> 0 Then 
Text137.TextAlign = 1 
End If 
 
End Sub

I am using this code in the On Format event of the Detail section of a REPORT.

I haven't been able to do anything in a form or subform. What I want to do is to right-allign or left-align text in a textbox of a subform in datasheet view, based on another textbox of the same subform being >0.

The code I am using in the On Format event of the Detail section of my report is working perfectly, but I don't know where to put it in my subform or textboxes to make it work in my subform :(

[NeoPa]

Ali,

I missed your changing from reports to forms. This is an entirely different question, and there is no way, other than using a limited set of conditional formats, to do this with forms (main or sub).

I was thinking mainly about forms when I posted what I did in post #4. This may not be correct for reports, but it certainly is for forms.

On another point, please make sure to use CODE tags in future whenever posting code. It is a requirement of using these forums. You've been using the forum for long enough to know and I've certainly had to edit a number of your posts.

[mshmyob]

What version of Access are you using? I can probably make it work in AC2007 but not earlier versions.

cheers,

[Alireza355]

I am using access 2003.

I have noticed that conditional formatting is limited to text color and background color, text bold and italic but NOT text allignment.

[NeoPa]

@Alireza355
Very good point Ali. I'm sorry if I misled you :(

I'm afraid that I still see no viable alternative, other possibly, than returning a formatted string and trying to ensure this fits as expected into the control it's to be displayed in. A bit of a kludge, but the best I can think of certainly.

[Alireza355]

Thanks anyway.

I wish you the very best.

Best Regards,
Alireza355
Isfahan, Iran