Conditional Formatting in Microsoft Access Reports

Added on: Saturday 29th August 2009

Whilst working on a Microsoft Access project recently, I needed to produce a report with certain fields highlighted in different colours depending on their values. I new I could do this with conditional formatting so I ploughed on and started building the

After I had spent some time on this, I realised that Access only allows three conditions for formatting of cells - presumably because it mirrors field formatting in forms where you have a format for negative, zero or null and positive values.

This was a bit of a blow as I had committed myself to using this method and the report was all but finished.

After the initial panic, I came up with a fairly simple solution that allowed me to use the five different colours that I needed for each field.

All I did was copy the text box control with the three formats and pasted it on top of the original control.

With the new control renamed I then removed the three conditional formats and added the two extra that I needed.

All that remained was to make sure that the new control only displayed if the value in the field matched one of the two new conditions - one line of code in the Detail_Format event.

My conditions were actually based on the value of a hidden field on the report so the code was simply:

Me!field2.Visible = (Me!hiddenValue > 3)

where field2 is the text box containing the formatting and hiddenValue is the hidden control that is the criteria for the formatting.

Both field2 and the original control had the same recordsource so when the hiddenValue was greater than 3, field2 displayed exactly the same data as the original control but because it was on top, it hid the original.

This worked fine and in fact could be extended to use 10, 20 or more conditions just by adding more duplicate controls and using the Format event to control their display.

NOTE: I had thought originally I could have set the Back Style property of the text boxes to Transparent to hide them without using any code at all but because it was the Back Colour property that was being changed in the formatting this didn't work. Changing the Back Colour when the Back Style is Transparent has no effect.

The report itself was quite a challenge in the first place which is why I didn't want to abandon what I had done for another solution. I will publish another article covering the whole report in due course.

Google Calendar API PHP Class

Added on: Saturday 22nd August 2009

I recently wanted to update a Google calendar via the API using PHP. There are plenty of samples on the Calendar API site but they all use the Zend framework which I didn't want to use.

I've been working hard on a new version of the projects module of our CRM package which enables users to create quotes that when accepted are converted into tasks that can then be scheduled on a calendar.

This also creates a timesheet that users can then update on a daily basis and any completed tasks are automatically added to a customers account ready for billing.

As part of this I thought it would be a good idea to add any scheduled tasks to a Google Calendar - mainly because its then easy to sync this with Outlook.

As I said I wasn't keen on using the Zend examples as I don't use the framework elsewhere so I searched for a PHP 'wrapper' for the calendar API.

As is often the case, PHP Classes had just what I wanted. The class I found also used another class for the Curl calls. The only problem was that the requirements stated that PHP5 was needed and the server I was using only had 4.4.

However, after a quick look at the two classes it seemed that all I would need to do was change the public and private variable declarations to just var. This did the trick up to a point but although the first curl call to login to the account worked fine, the attempt to add a new event failed.

After a liberal sprinkling of curl_error calls, I tracked this down to the curl_execute function returning the error: Failed to open/read local data from file/application.

A quick search on Google and I came across a post on MilkHub that solved the problem.

The wrapper class calls curl with POST data to login to the account but then uses HTTP headers to update an event. Apparently if the POST data is empty you should explicitly set the CURLOPT_POSTFIELDS to a zero length string, something the curl wrapper class wasn't doing.

A quick change to the curl class and it all worked fine. The files are included here if anyone else is having similar problems.