Dates Problem, why won't it work?!?

[Muzzery]

Hi,

I am using this statement to find the amount of entries in a table within certain dates:

TotalCount = DCount(&quot;[id]&quot;, &quot;Referral Inbox summary&quot;, &quot;[Received] >= #&quot; & Forms![Referral Lookup]![WeekStarting]# & &quot; AND [Received] <= #&quot; & Forms![Referral Lookup]![WeekEnding] & &quot;#&quot

This seems to be the way to get it to work in the help file, but i don't understand why it isn't working properly. If i do the date anything before the 12/06/2002 it doens't work, the return value is 0

The format is the same througout the time it takes to filter out the information needed (taken from an inbox, so not all e-mails are needed,) it stays as a date all the way through. I have tried it with and without the &quot;#&quot;'s but it still doesn't work properly. Even if i Hard code the dates, it still won't work.

When it is filtering out the data, it goes through about 2 queries, would they change the format?

Thanks in advance,

Muzz

 

[AlexMidd]

I think the # after the first [WeekStarting] should be after the ampersand and the quotes:

TotalCount = DCount(&quot;[id]&quot;, &quot;Referral Inbox summary&quot;, &quot;[Received] >= #&quot; & Forms![Referral Lookup]![WeekStarting] & &quot;# AND [Received] <= #&quot; & Forms![Referral Lookup]![WeekEnding] & &quot;#&quot

instead of

TotalCount = DCount(&quot;[id]&quot;, &quot;Referral Inbox summary&quot;, &quot;[Received] >= #&quot; & Forms![Referral Lookup]![WeekStarting]# & &quot; AND [Received] <= #&quot; & Forms![Referral Lookup]![WeekEnding] & &quot;#&quot

unless this is just a typo in your post. Have fun! )

Alex Middleton

 

[Muzzery]

No sorry, that was just a typo in the post, i still can't figure it out, any more ideas?

 

[Muzzery]

I seem to have narrowed down the problem, the format seems to change when i transfer all the information from a uery to a table. I can run one part of it when there are no brokers to look up on the query like so:

TotalCount = DCount(&quot;[ReceivedShort]&quot;, &quot;Referral Inbox Lookup Query Final&quot;, &quot;[ReceivedShort] >= #&quot; & Forms![Referral Lookup]![WeekStarting] & &quot;# AND [ReceivedShort] <= #&quot; & Forms![Referral Lookup]![WeekEnding] & &quot;#&quot

But if i try to look up the name of the broker in the query, it says that there is an invalid call procedure?!? I found the way to get round this is to move iot to a table, and it seems to like it, but that causes the problem of not finding the dates? It's a visious circle i can't get out of!

Any ideas how i can get round this please?