Conditional formating
- Thread starter AnnPhil
- Start date
I would like either the label associated with the control to come up in red or a phrase in the control that would remind the employee that there is missing data; example "Please fill in this information". This is an employee information sheet that is filled out and would like to generate a report that shows information and red fly those that are missing information. thanks for any suggestions
boblarson
Smeghead
- Local time
- Yesterday, 20:35
- Joined
- Jan 12, 2001
- Messages
- 32,040
If using Access 2000 or above (I don't know about 97 as it's been so long since I used it) you can use the on NO DATA event of the report to set something. I, personally, prefer to just set a message box that comes up and says that there was no data to display and then cancel the report open event. However, you also have to trap for error 2501 when doing this.
I believe that only works if there is no data for all fields in a report? I probably have 50 fields and i want to red fly just a few fields IF they are blank.
So i thought i could tie the conditional formating to just those fields of interest but i couldn't get it to work. Then i thought i would put an IIF or IF statement somewhere but not luck with that. I am probably writing the IIF or IF statement wrong. Anyway if anyone can come up with something i would appreicate it. Thanks
So i thought i could tie the conditional formating to just those fields of interest but i couldn't get it to work. Then i thought i would put an IIF or IF statement somewhere but not luck with that. I am probably writing the IIF or IF statement wrong. Anyway if anyone can come up with something i would appreicate it. Thanks
Change your box to an unbound. In the data source do an Iff(IsNull statement to print the result or print the message you want.
Right click on the box and use conditional formatting for your text.
Does that all make sense? lightray
Right click on the box and use conditional formatting for your text.
Does that all make sense? lightray
boblarson
Smeghead
- Local time
- Yesterday, 20:35
- Joined
- Jan 12, 2001
- Messages
- 32,040
I was going to suggest the similar thing, but using a label and just setting the intitial caption to "" and then set the caption using the expression on conditional formatting.
But, if you have those many fields, then you're going to be limited to only about 3 fields you can use conditional formatting on. You can set your own conditional formatting by using the On Page event and coding for each field.
But, if you have those many fields, then you're going to be limited to only about 3 fields you can use conditional formatting on. You can set your own conditional formatting by using the On Page event and coding for each field.
OK i put this code on the "On Format" of the detail section of the report and it works wonderfully (for that one field) but now they want this for all fields in the report that have a null value! Is there anyway i could have the report check all fields and if null value then the label is to be in red. (Not asking for much am i?)
If IsNull(DOB) Then
Label_DOB.ForeColor = 255
Else
Label_DOB.ForeColor = 0
End If
If IsNull(DOB) Then
Label_DOB.ForeColor = 255
Else
Label_DOB.ForeColor = 0
End If
Last edited:
AnnPhil,
If i understand your problem correctly i believe the following may help you. You essentially want a 'label' on a report to be conditionally formatted dependent upon a field being blank (IsNull)?
I had a similar requirement and what i have done in order to allow a 'label' on a report to be formatted in certain circumstances is:
1. My report is basically based on a query that is based on a single table, so if your report is already based on a query all good and well and proceed to step 3 below.
2. If not then create a query based on the relevant table and just add all the necessary fields from the base table.
3. When i created the query i added an expression by typing in a blank text field of the query want it is i wished to be displayed as a 'label, for example "D.O.B" (include the " "), when you press enter, Access will automatically place Expr1: in front of it.
4. Make sure that the show box is ticked below where you have just created this new entry. Save the query with a suitable name if needed.
5. Open up the report in design view.
6. If your report was already based on a query go to Step 7. If not you will need to change the Reports Record Source through the Reports properties. Providing you have made no other changes to the fields - only added the new expression, you should be able to attach the report to the new query by clicking in the 'Record Source' property and selecting the new Query. This will now update the Reports fields to the query and also make the new expression Expr# (where # is a number, if the only such entry it should be Expr1) available for you. Goto Step 8.
7. If your report was already based on a query and you have added the extra expression (Expr#) to it, you should see it at the bottom of the Reports Field List.
8. Drag and drop this new expression roughly where you want it. Click on the label of this new expression and press Delete, this should leave you with the 'label' Text Box.
9. Set the size, Font etc as required.
10. Right click on this 'label' now should give you the 'Conditional Formatting' option and from here you should be able to set your required formatting such 'Expression Is' and in the box IsNull([YourFieldNameHere]) and set the formatting required if this expression is true i.e. Red and Bold.
11. Repeat as required for each 'label' required to be conditionally formatted.
Because of some complicated Reference Numbers used within my database and the requirement to automatically increment them, I used this process to create a 'label to represent a particular part of the Reference Number i.e. TF-#### with the label providing the TF - section. This allowed me to 'Grey Out' all the fields when records set to superseded in the database are displayed in the report.
It may not be the most efficient method but it works. If you get stuck drop me a line.
Good Luck
Regards
Alan
If i understand your problem correctly i believe the following may help you. You essentially want a 'label' on a report to be conditionally formatted dependent upon a field being blank (IsNull)?
I had a similar requirement and what i have done in order to allow a 'label' on a report to be formatted in certain circumstances is:
1. My report is basically based on a query that is based on a single table, so if your report is already based on a query all good and well and proceed to step 3 below.
2. If not then create a query based on the relevant table and just add all the necessary fields from the base table.
3. When i created the query i added an expression by typing in a blank text field of the query want it is i wished to be displayed as a 'label, for example "D.O.B" (include the " "), when you press enter, Access will automatically place Expr1: in front of it.
4. Make sure that the show box is ticked below where you have just created this new entry. Save the query with a suitable name if needed.
5. Open up the report in design view.
6. If your report was already based on a query go to Step 7. If not you will need to change the Reports Record Source through the Reports properties. Providing you have made no other changes to the fields - only added the new expression, you should be able to attach the report to the new query by clicking in the 'Record Source' property and selecting the new Query. This will now update the Reports fields to the query and also make the new expression Expr# (where # is a number, if the only such entry it should be Expr1) available for you. Goto Step 8.
7. If your report was already based on a query and you have added the extra expression (Expr#) to it, you should see it at the bottom of the Reports Field List.
8. Drag and drop this new expression roughly where you want it. Click on the label of this new expression and press Delete, this should leave you with the 'label' Text Box.
9. Set the size, Font etc as required.
10. Right click on this 'label' now should give you the 'Conditional Formatting' option and from here you should be able to set your required formatting such 'Expression Is' and in the box IsNull([YourFieldNameHere]) and set the formatting required if this expression is true i.e. Red and Bold.
11. Repeat as required for each 'label' required to be conditionally formatted.
Because of some complicated Reference Numbers used within my database and the requirement to automatically increment them, I used this process to create a 'label to represent a particular part of the Reference Number i.e. TF-#### with the label providing the TF - section. This allowed me to 'Grey Out' all the fields when records set to superseded in the database are displayed in the report.
It may not be the most efficient method but it works. If you get stuck drop me a line.
Good Luck
Regards
Alan
