An interesting question for you advanced level computer geniuses!

[rushy-s]

Evening all. I am creating a school sports fixture's system on Microsoft Access 2003.

One of the end user requirements were "Flag up unconfirmed fixtures".

Now, unconfirmed fixture means fixtures that were arranged before the term started but must be confirmed at least 7 days before the games takes place.

I've racked my brains for this answer, done plenty of research on the internet, read books etc. but haven't got the answer!

My question: How do I get unconfirmed fixtures that are to be played within 10 days to be flagged up in red when the application is opened?

Is there an SQL code I could use or some sort of trigger I can create in Access itself?

If you have any questions please ask!

Thanks Rushy

[Allan Murphy]

I will assume that you are opening a report when the application opens to show a list of unconfirmed events.

I created a report with the following fields sport_date to show the date of the sport, sport to show the name of the sport and sport_confirmed is to show the sport is confirmed or not confirmed.

Have a look at the BackColor property of a field etc.

Now, in the design view of the report click on the detail section then select On Format event and add the following code


Private Sub Detail_format(Cancel As Integer, PrintCount As Integer)
If ((Me!sport_date >= Date) And (Me!sport_date < Date + 10)) Then
If Me!sport_confirmed = False Then
Me!sport.BackColor = 255
Else
Me!sport.BackColor = 16777215
End If
else
Me!sport.BackColor = 16777215
End If

End Sub

Explanation of the Code
The first IF statement checks if the sport is within the next 10 days, if not then set the back color of the sport field to white.

Now if the date is within the date range then check if the sport is unconfirmed, if it is then change the back color to Red. If it is confirmed then change the back color to white.

[rushy-s]

That's brilliant, I'm still at the design stage at the moment so next week when I start implementing everything I'll let you know how I get on!

Thanks a ton!

Rushy