How to validate a Count?

[Learning123]

Hi,

I have a bookings database for horses. I have created a query with these fields:
Date of lesson - parameter query.
Time of lesson
Horse or Pony ID

The user will then look down the query to see if the horse is already booked for that time. If not, then they can go ahead and make the booking.

I have put a Record Count on the Time of Lesson. Horses cannot work more than 5 hours a day. Each lesson is counted as one hour. How do i validate or use some expression e.g. Not >5 on this Count, and then have an error message come up if the horse is selected for over 5 hours?

Thanks

 

[pbaldy]

You could use a DCount with a criteria for the date and the horse, which should give you the count for that horse on that date. More info on syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[Learning123]

Hi, would this work on a Count field? I notice that the ones on the link you gave me are all V lookups - but I'm not sure what to put in the expression as there is no need to look anything up.

I tried using conditional formatting on the count field, and selected "Field value is less than or equal to 5" but it doesn't seem to do anything, and is there a way of making an error message come up if it is over 5?

Thanks for your help.

 

[pbaldy]

I said DCount; the syntax is the same for DLookup. Now I'm thinking you already have a count field? There is no conditional formatting in a query, only forms and reports. Can you clarify what your data looks like and where you're trying to do this?

 

[Learning123]

Yep,

I have a query with:
Date of Lesson
Horse or Pony ID
Time of Lesson

Both Date and Horse/Pony ID are Parameter queries.


I then have a Report (sorry, I don't think I was clear enough about this before), based on this query. In the 'group and sort' option on Access 2007 I selected 'with Time of lesson totalled' on 'Count Records', 'Show grand total.'

The count then returns the number of records, which in this case is the number of lessons booked and so also the number of hours the horse rides for. This is working fine now, it's the validation/formatting/whatever you can do for this, which I can't do - How do I stop someone booking over 5 for the same Horse/Pony?

Hope this makes sense, thanks again

 

[pbaldy]

I'm back where I started then. On the form where the user enters a booking, you can use a DCount to find out how many bookings the requested horse has on the requested date. You can then inform the user that horse isn't available. Typically you'd either use the before update event of the form, but there are other ways depending on your setup.

 

[Learning123]

Thanks, I'll have a go at this one then