Need to count number of records in table between dates in another table

[lcp298]

I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).

Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID

I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.

I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).

I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.

SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));

What I can't do is get it to run on the form and take those values from the form.

From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.

SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;

I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.

This works, but obviously I need to be able to change the ID:
=DCount("KitBkID","KitloanCountQry","SchoolID = 107")

This is what I thought it should be
=DCount("[KitBkID]","KitloanCountQry","[SchoolID] = " & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![SCHOOLID] & " And [DateOut] > #"& [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateJoined] &"# And [DateOut] <# "& [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateRenewal] &"# )

Although I have to admit to getting lost in the syntax. This produces #Error.

Sorry for the long winded explanation but hopefully you will understand what I am trying to do.

Any help gratefully received.

 

[boblarson]

Where is the control which has this expression on it? Is it on the same form as [Forms]![MakeaKitloanBooking]![MembershipSubform]? Or is it on the parent form MakeaKitloanBooking?

The thing to note is that the syntax for the form reference is dependent upon where in relation to the control which has the expression is located. It doesn't always like the full form reference.

Also, I would format your date explicitly in the expression:

[DateOut] > Format([DateJoined], "\#mm\/dd\/yyyy\#") ...etc.

If the control is on the same form as the DateJoined control you don't need anything but what I show above - just the control name. If the control is not on the same form as DateJoined then it will need other syntax.

I have put together a little tutorial on my website for showing a great, and easy, way to get the actual syntax you need from the control you are working with.
http://www.btabdevelopment.com/ts/frmrefinqry

 

[boblarson]

Also, it may work if you put the spaces in correctly between the ampersand and the other part and also you are missing a quote at the end.

=DCount("*","KitloanCountQry","[SchoolID] = " & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![SCHOOLID] & " And [DateOut] > #" & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateJoined] & "# And [DateOut] <# " & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateRenewal] & "#") )

 

[lcp298]

The control with the DCount on it is on the subform (membership subform), the same form as the DateJoined, DateRenewal and SCHOOLID. I did try it without the full forms reference but that's one area I got a bit lost.

The dates throughout the DB are UK format, but I s'pose just changing your line to "\#dd\/mm\/yyyy\#" will be correct?

I'll try that, and check out your tutorial. Thanks

 

[boblarson]

The dates throughout the DB are UK format, but I s'pose just changing your line to "\#dd\/mm\/yyyy\#" will be correct?

Nope, you need my original US date format. Thank Microsoft for that one.

 

[lcp298]

Thank you. Yes, you are right about the reference, when I use the expression builder it shortens it to just [SCHOOLID] etc.

I tried your suggestion of: =DCount("*","KitloanCountQry","[SchoolID] = " & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![SCHOOLID] & " And [DateOut] > #" & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateJoined] & "# And [DateOut] <# " & [Forms]![MakeaKitloanBooking]![MembershipSubform].[Form]![DateRenewal] & "#") )

correcting the references and removing the extra ) it complained of to make: =DCount("*","KitloanCountQry","[SchoolID] = " & [SCHOOLID] & " And [DateOut] > #" & [DateJoined] & "# And [DateOut] <# " & [DateRenewal] & "#")

That still gives #Error


I tried this: =DCount("*","KitloanCountQry","SchoolID = [SCHOOLID]") but I still get #Error

When using the expression builder to create the whole criteria, it becomes: =DCount("*","KitloanCountQry","[KitloanCountQry]![Kitloan.SchoolID] = [SCHOOLID]")
but that doesn't work either.

I tried: =DCount("*","KitloanCountQry","SchoolID = [SCHOOLID] And [DateOut] > Format([DateJoined], "\#mm\/dd\/yyyy\#") And [DateOut] < Format([DateRenewal], "\#mm\/dd\/yyyy\#")")

I also created this using the expression builder, which did add the [KitloanCountQry] to [DateJoined] but neither of them will even stay in the expression builder. I click OK, but then if I open the expression builder again it's changed the criteria to only include [SCHOOLID].

I even created the entire DCount using the expression builder which gave me this: =DCount([KitloanCountQry]![KitBkID],[KitloanCountQry],[KitloanCountQry]![Kitloan.SCHOOLID]=[SCHOOLID])
but that gave #Name

 

[lcp298]

Just realised I didn't mention this was in Access 2007. Not sure if that makes a difference.