By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Conditional formatting in a report

Alireza355
P: 86
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????????????
May 2 '09 #1
Share this Question
Share on Google+
21 Replies


Alireza355
P: 86
I also want to do the same in a subform.

Does anyone have any idea how?
May 2 '09 #2

ADezii
Expert 5K+
P: 8,679
@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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2. Const conALIGN_GENERAL = 0
  3. Const conALIGN_LEFT = 1
  4. Const conALIGN_RIGHT = 3
  5.  
  6. If Me![Field2] > 0 Then
  7.   Me![Field1].TextAlign = conALIGN_LEFT
  8. ElseIf Me![Field3] > 0 Then
  9.   Me![Field1].TextAlign = conALIGN_RIGHT
  10. Else        'a Catch-All, align General
  11.   Me![Field1].TextAlign = conALIGN_GENERAL
  12. End If
  13. End Sub
May 2 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 2 '09 #4

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. Field1 = String(10 - Len(Format(Field2 + Field3, "#########0")), " ") & Location
May 2 '09 #5

ADezii
Expert 5K+
P: 8,679
@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.
May 2 '09 #6

Alireza355
P: 86
I got it working this way in my report:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.  
  3. If Field1 > 0 Then
  4. Text137.TextAlign = 3
  5. End If
  6.  
  7. If Field2> 0 Then
  8. Text137.TextAlign = 1
  9. End If
  10.  
  11. End Sub
But now I'm wondering how to do it in a subform........ Any idea? (:
May 3 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Ali.

Please review posts #4 & #6.

That cannot work correctly, even though it may seem to in some circumstances.
May 5 '09 #8

FishVal
Expert 2.5K+
P: 2,653
@NeoPa
;)

Actually it does work corrrectly.

Regards,
Fish.
May 5 '09 #9

ADezii
Expert 5K+
P: 8,679
@FishVal
Hello Fish, you mean that I was Right, then Wrong, then Right again, all in the same Thread? (LOL)
May 5 '09 #10

FishVal
Expert 2.5K+
P: 2,653
@ADezii
Did you enjoy that?
Do you want a second go? LOL.
May 5 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
@FishVal
Can you explain?
May 5 '09 #12

FishVal
Expert 2.5K+
P: 2,653
@NeoPa
There is nothing to explain.
It works (at least in Access 2003). Try and see.
May 5 '09 #13

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 5 '09 #14

Alireza355
P: 86
What I wrote in post number 7 IS working PERFECTLY.

But now, I want to do the same in a subform, and can't ):
May 6 '09 #15

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 6 '09 #16

Alireza355
P: 86
The code I gave to you earlier:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
  2.  
  3. If Field1 > 0 Then 
  4. Text137.TextAlign = 3 
  5. End If 
  6.  
  7. If Field2> 0 Then 
  8. Text137.TextAlign = 1 
  9. End If 
  10.  
  11. 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 :(
May 7 '09 #17

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 7 '09 #18

mshmyob
Expert 100+
P: 904
What version of Access are you using? I can probably make it work in AC2007 but not earlier versions.

cheers,
May 7 '09 #19

Alireza355
P: 86
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.
May 9 '09 #20

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 11 '09 #21

Alireza355
P: 86
Thanks anyway.

I wish you the very best.

Best Regards,
Alireza355
Isfahan, Iran
May 12 '09 #22

Post your reply

Sign in to post your reply or Sign up for a free account.