DCount and Dates Help Needed

[wambaugh]

Hi All

Im sure this is something stupid but I cant work it out

I have a report with a control on it. The control source is the following DCount statement

=DCount("[category]","TBL_HELPLINE","[start_date] >= #02/01/2005# AND [start_date] <= #02/28/2005# And [category]=" & category.Value)

this counts the number of records in the table "TBL_HELPLINE" that have a start date anytime withing the month of february and also has a category the same as the catergory in another field

This statement works fine as long as there are no records with the same startdate as either of the 2 dates specified in the statement.

If there are records in the table with these 2 dates then the count returned is incorrect.

Can anyone see what I am doing wrong

Many thanks

wambaugh

 

[dhookom]

We are having trouble seeing your data and counts. What is the error?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wambaugh]

When I Run The statement it returns a value

That value is wrong

If I specify the dates as 1/1/05 and 31/1/05 it should return 9 records for the month of january. It only returns 8 This happens any time that there is a record that matches either of the dates specified in the dcount statement

I have used a >= operator and I have tried using the datevalue statement but it still gives the same error

Any ideas

Thanks

Wambaugh

 

[dhookom]

First of all, keep in mind that dates in queries should be in the format m/d/y, not d/m/y. Also, if your [Start_date] field contains a time portion then you will have to add .9999 to your latest date.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wambaugh]

Hi Dhookom

Not sure what you mean. Why do you have to add .9999 to the last date and how would you do it

thanks

Wambaugh

 

[dhookom]

Try:[blue]
=DCount("[category]","TBL_HELPLINE","[start_date] >= #02/01/2005# AND [start_date] <= #02/28/2005# + .9999 And [category]=" & category.Value)[/blue]
Or maybe better:[blue]
=DCount("[category]","TBL_HELPLINE","[start_date] >= #02/01/2005# AND [start_date] < #03/01/2005# And [category]=" & category.Value)[/blue]
This is only necesary if Start_date might be something like 2/28/2005 8:35 AM.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wambaugh]

Duane

Thank you The 2nd statement works

I had tried that before but had gone a day either side and not just the end of the month

Is there a resason why you have to go a day forward and not just the month??

Many thanks

Wambaugh

 

[dhookom]

If a date contains a time value then it is like 28.5 rather than 28. This suggests you need to use < 29 rather than <=28.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]