Using Subform data as Query parameters

[BrianCurran]

Hi all,
I want to count the total number of orders between two dates for a particular customer. I can get the result in a query by using parameters but I want the result to show in a subform without anyone typing the parameters in.

I'm currently using a Form to enter details of customer "Orders" and I have a SubForm that displays several pieces of information from the "Customers" Table including how many orders they have pre-paid for plus the start and end dates of their annual contract.

Example:
Mr. Bloggs
12 (orders prepaid)
01 June 2010
31 May 2011
5 (orders already entered, how do I calculate this in the SubForm?)

The "Orders" table contains orders for all customers going back several years so I would like to use the SubForm data to query the Orders table and return a result.

Any suggestions on how I could do this?

Many thanks
Brian.

 

[nathansav]

Can you not just use DCOUNT as that field in the sub form.

 

[BrianCurran]

Thanks for the clue nathansav, I've just had a look at the DCOUNT function but I can't get it to work.

Can you seen anything blatantly obvious with the expression below? (Apart from the lousy naming conventions etc.)

DCount ("[Billable Hours]", "Alarm Response - Data Entry - Query 2", "[Date of Arrival] >= [Bundled From] And <= [Bundled To]")

 

[BrianCurran]

Ok, after a bit of tweaking I can return the correct number of previous orders but without the date criterion.

=DCount("[Billable Hours]","Alarm Response - Data Entry - Query 2","[SIC] = [Keyholding]")

I now need the DCount function to just return the orders between two dates. Can I add this to the above expression, if so, how would I write it?

Thanks
Brian.

 

[BrianCurran]

Finally worked it out, seems fine but I'll need to check the results for errors:

=DCount("[Billable Hours]","Alarm Response - Data Entry - Query 2","[SIC] = [Keyholding] And [Date of Arrival] >= [Bundled From] And [Date of Arrival] <= [Bundled To]")