Hi im fairly new to Access and would like to thank any help I recieve in advance.
Here's my problem, I have an appointments database with an appointments table, the two relevant fields are ApptDate and CourtesyCar, ApptDate is a short date field and CourtesyCar is a yes/no field, this table is linked to a appointment form where each appointment shows on the form. This form has a text box that shows the date of that particular appointments page at the top, and all the appointments for that date are shown on that form so clicking a forward control moves refreshes the form with the next days date and all the appointments for that date and so on and so on, (with me so far) my problem is i have an unbound text box that i wish to show a count of all the Courtesy Cars booked out on that particular date. I have created a query that when the form is open and i click the query in the left hand pane it runs and correctly adds up all the Courtesy Cars booked on the date displayed in the forms date field, but when i make the query the data row source for the text box and open the form it doesnt open but a date parameter dialogue box opens asking for a date value, Its probably something stupid but ive pulled just about all my hair out and am now working on the cat.
this is my query
ctlViewDate is the date text box on the Form. Or would a Dlookup and count be a more versatile option , all i want it to do is when the form opens or is updated to display the total of all the courtesy cars booked on the date that is displayed in the text box on the form
i hope that is clearish and appreciate your time
Regards Chris
Here's my problem, I have an appointments database with an appointments table, the two relevant fields are ApptDate and CourtesyCar, ApptDate is a short date field and CourtesyCar is a yes/no field, this table is linked to a appointment form where each appointment shows on the form. This form has a text box that shows the date of that particular appointments page at the top, and all the appointments for that date are shown on that form so clicking a forward control moves refreshes the form with the next days date and all the appointments for that date and so on and so on, (with me so far) my problem is i have an unbound text box that i wish to show a count of all the Courtesy Cars booked out on that particular date. I have created a query that when the form is open and i click the query in the left hand pane it runs and correctly adds up all the Courtesy Cars booked on the date displayed in the forms date field, but when i make the query the data row source for the text box and open the form it doesnt open but a date parameter dialogue box opens asking for a date value, Its probably something stupid but ive pulled just about all my hair out and am now working on the cat.
this is my query
Code:
SELECT Count(tblAppointments.CourtesyCar) AS CountOfCourtesyCar, tblAppointments.ApptDate
FROM tblAppointments
GROUP BY tblAppointments.ApptDate
HAVING (((tblAppointments.ApptDate)=[Forms]![frmAppointments]![ctlViewDate]));ctlViewDate is the date text box on the Form. Or would a Dlookup and count be a more versatile option , all i want it to do is when the form opens or is updated to display the total of all the courtesy cars booked on the date that is displayed in the text box on the form
i hope that is clearish and appreciate your time
Regards Chris
