query query

[NickNeville]

Good morning all
I wonder if you can point me on this please.
I have a query with 6 fields in all and one of the fields is a Count of Date
this works ok and shows the total, but I want to select certain dates and when I put say between Date() and Date()-160 it returns nothing. I think I understand that this cannot be put in the same field as the count but it will not work at all whatever I try. I have tried repeating the date field and this produces a result but cancels the count fields and shows all individual counts and not a total
Is there a solution to this pls

Rgds
Nick

 

[namliam]

You have a column named "Count of date" does that mean you have a column in your table called "Date"??
If so BAD BOY! Date is a reserved word (Date() Function) that shouldnt be used as column name.

You cannot put a constriction on this date column when it is beeing counted, instead add your date field for a second time to the query (in design view) and change the option (where you have count for the first date column) to "Where" and add your between in this column.....

 

[NickNeville]

many thanks for your quick reply.N
My date field is actually called Shoot Date but I only used Date as an example on my question. But point taken and I will never use "Date" on its own. Thankyou.
With regard to your answer do I put the "where" in the count of "shoot date" field or the new one ?

Thanks
Nick

 

[NickNeville]

Hello again
I just entered the new Date field as you said and its works a treat.
But the field does not show in the resulting test, not that it matters but why ?

Nick

 

[namliam]

It doesnt show because when you put in the "where" automaticaly the "visible" tag disappears to (in the design view of the query, the tick mark "show" disappears.

This makes for the column to only 'pop up' in the where clause which is where you want and need it

 

[NickNeville]

OH yea I missed that !
Many many thanks for your help.
Rgds
Nick