DCount Dynamic Variables

[adam.greer]

Hi all, been a while since I've posted, had some job changes for the last year. Good to be back working on Access

I'm trying to create a query that counts multiple fields. However I need to it count the fields based a static text value and a dynamic date range. Here's my SQL for the query

SELECT tblClientDetails.satisfactiondate, DCount("satisfaction","tblClientDetails","satisfaction = 'Very Satisfied' And satisfactiondate = between [date1] and [date2]") AS [Very Satisfied], 

DCount("satisfaction","tblClientDetails","satisfaction = 'Satisfied'") AS Satisfied, 
DCount("satisfaction","tblClientDetails","satisfaction = 'Not Satisfied'") AS [Not Satisfied]
FROM tblClientDetails;

So far I'm just trying the date range on the 'Very Satisfied' field, that was just for quick testing, it will apply to all fields.

The query works fine if I replace "between [date1] and [date2]" with a static date ie 01/01/09, but I'm getting a syntax error with this. Been searching for a while but it seems most people don't do this.

Thanks

Adam

 

[KenHigg]

This is a stab in the dark - Maybe you need to qualify the dates with pound signs:

between #[date1]# and #[date2]#

???

 

[adam.greer]

No luck.

I've gotten around it by creating a temp table with a query asking the user for the dates, then basing the DCount query off of that temp table. I little messy but it seems to be working.

Thanks for your help Ken

 

[KenHigg]

Glad you have it working -