[RESOLVED] Help!!! To Format The Access Report

Dear friends

I am creating a report in which i want that the data should be formated according to some condition. for example

if date of joining of a person is more than 20 years of date of birth, Date of joining should be displayed in green, if date of joining is more than 25 year of date of birth then date of joining should be displayed in yellow color.

I do not know how to do it in ms Access. Kindly help me regarding that.

plz help!!! its urgent


Thansk in advance


shivpreet2k1

Hi,,,

Use the conditional formatting property of a control...

In the design view of the report ,right click on a control (i.e textbox)..Choose conditional formatting then
apply a certain criteria for it...Then select the appropriate font color inside the conditional formatting box...

Inform me if this works...

:) :) :)

Hi Friend

Thnx for help but i already tried for this. may be i am wrong on some part that it should check the data of the other field lke if i want to format the date of joining, this field first of all compare itself with the field date of birth in the same record and if there is any difference according to the condition then it should display the different color.

Kindly provide help on this.

Thanks in advance

Shivpreet2k1

Quote:

---

may be i am wrong on some part that it should check the data of the other field lke if i want to format the date of joining, this field first of all compare itself with the field date of birth in the same record

---

Hi,

Are you saying that your comparing two fields 1) dateofbirth
2)datejoining? Say, if dateofjoining field is more than 20 years of date of birth, the dateofjoining field should be displayed yellow?

Am I correct on this?

:) :) :)

Hi friend

Yes u r right. I m comparing two fields.


Shivpreet2k1

Hi,

Quote:

---

if date of joining of a person is more than 20 years of date of birth, Date of joining should be displayed in green, if date of joining is more than 25 year of date of birth then date of joining should be displayed in yellow color.

---

I suggest that the condition should be more specific...I say the result date is
26 based from dateofbirth and datejoin...Since 26 is greater than 20 and greater than 25, what should be done?

This is my suggestion for the condition...

Algorithm:

Code:

---


If result > 20 and <= 25 then
    font color is green
else  'this if for result greater than 25
    font color is yellow
end if

---

Hope my suggestion is fine with you.... :) :) :)

You can also easily format an Access report in the report class file like so.

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Text1.Properties.Item(25) = 255 'RED
End Sub

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Text1.Properties.Item(25) = 255 'RED
End Sub

Hi!

where does .item(25) referred to? Is this referred to the font color?
Im new with this code....


Can this code be applicable in his requirements? His requirements have some certain criteria? I mean condition...

:) :) :)

Right, I was only showing how to change the font color dynamically when the report is being generated.

The Properties.Item(25) refers to the textbox objects forecolor property.

hi robdog


thnx for ur help. just one more question. how can i acess the text property of textbox in the report.

Plz help its urgent.

Thanks again buddy

Shivpreet2k1

Hi RD,

I tried to apply this code...However the label is now being underlined not changing the forecolor...

VB Code:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Label9.Properties.Item(25) = 255
    Label10.Properties.Item(25) = 266
End Sub

I also tried the code in your post however it does not change the forecolor..

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    txtname.Properties.Item(25) = 255 'RED
End Sub

Thanks... :) :) :)

Dear Friend

Use this code in report open event . it is working. ihave cheked it.

Tks

Shivpreet2k1

Hi...

Can you post your code...

Thanks...

:) :) :)

Item 25 is only for the textbox forecolor. If you use a different control then the forecolor property will be a different number. I think you can use the items names but dont have time to test it.
Just do a Debug.Print Label9.Properties.Item(0).Name while placing a breakpoint to stop the code execution. When you find the item number that corresponds to the property you want then use that number, that is if the actual property names dont work.

Hi RD,

I'll give it a spin...

Thanks...

HI RD,

Thanks for the idea....

Maybe i'll be comfortable with the property names rather than the numbers..Works fine for me...

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     datejoin.Properties.Item("ForeColor") = 255
End Sub

:) :) :)

Yes, I wasnt sure if it worked with the names. Its been a while since I did formatting on an Acces report. Property names are more easier to follow. :)

Quote:

---

Property names are more easier to follow

---

I agree..... :thumb:

Hi kgcomputers

Tahnk for ur help but. i want to access text of the date datafield which property i should use. I have used text property but it gives me error.

what is the problem in this code.

MsgBox [Date].Properties.Item("text")

plz help its urgent.


Thanks in advance

Shivpreet2k1

Quote:

---

MsgBox [Date].Properties.Item("text")

---

Hi...

What is the error of the code?

:) :) :)

Dear friend
i wanna see and compare the data in access report but i am not able ot

try to run urself the code in the report open event and see it and if u find solution. kindly inform me. i wam waiting.

Thanks

Shivpreet2k1

If your control is named [Date] then rename it as the reserved word may still be giving issues evven with the brackets.

Quote:

---

If your control is named [Date] then rename it as the reserved word may still be giving issues evven with the brackets.

---

It may be a cause....Try substituting the name with txtDate...

:) :) :)

Dear Friend

I have tried it but it still not working. cOul u provide me ur code.


thnx

Shivpreet2k1

Are you placing the code in the Detail_Format section or ? Can you post your code as that would be more relevant.

What are the modifications you made through the code? Post your code please.... :) :) :)

Hi Kg and rob dog


Got it boss. Pronlem has been solved . I m placing some sample coe so that it could help other new bies also.

VB Code


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

On Error Resume Next

VB Code:

'document in custom formatting
If IsDate([Date].Properties.Item("Text")) = True Then
 
   [Document_in_Custom].Properties.Item("BackStyle") = 1
    [Document_in_Custom].Properties.Item("backcolor") = 16777215
Else
    
    If DateDiff("D", [Date].Properties.Item("Text"), Now) > 4 Then
        If IsDate([Document_in_Custom].Properties.Item("Text")) = False Then
            [Document_in_Custom].Properties.Item("BackStyle") = 1
            [Document_in_Custom].Properties.Item("backcolor") = 255
        End If
    Else
        If DateDiff("D", [Date].Properties.Item("Text"), Now) > 2 And DateDiff("D", [Date].Properties.Item("Text"), Now) <= 4 Then
            If IsDate([Document_in_Custom].Properties.Item("Text")) = False Then
                [Document_in_Custom].Properties.Item("BackStyle") = 1
                [Document_in_Custom].Properties.Item("backcolor") = 65535
            End If
        End If
    End If
  End If
 
End sub

_____

Thanks friends


shivpreet2k1

Glad to hear it :)

Ps, in order to let others know its resolved, click on the thread tools menu and select "Mark Thread as Resolved" so its viewable from the forum level view. ;)

Your Welcome and Good job....
One last thing, please put VB code tags in your code so that it would be more readable...

:) :) :) :thumb: :thumb: :thumb:

Added [vbcode] tags. :)

I got it for him. :)