DCount

[Rocinante]

I am trying to count records between two dates specified in a form. When I run the code I get the total of records form the beginning of the file to the last date criteria.

When I run the code without variables but simply substituting the dates directly in the code I get the right answer. Clearly my criteria expression is wrong but I have tried a number of ways to get it and cannot get right answer.

This is waht I have done:
1) I declare variables to hold the dates & one to hold the count answer
2) Set the variables to the values in the form
3) Do calculation

Dim FirstDate As Date
Dim SecondDate As Date
Dim CompsDates As Long

FirstDate = Me.txtDate1
SecondDate = Me.txtDate2

CompsDates = DCount("*", "tbl_Compliments_Log", "[Date_Raised] Between #" & FirstDate & "# AND #" & SecondDate & "#")

This code works:
CompsDates = DCount("*", "tbl_Compliments_Log", "[Date_Raised]>= DateValue('1/08/2003') AND [Date_Raised]<= DateValue('31/08/2003')")

Any suggestions

Thanks

 

[Rocinante]

Update

The errant code line is:
CompsDates = DCount("*", "tbl_Compliments_Log", "DateValue([Date_Raised]) Between #" & FirstDate& "# AND #" & SecondDate & "#")

If I substitute

Between DateValue(' 1/08/2003 ') AND #" & SecondDate & "#

for

Between #" & FirstDate& "# AND #" & SecondDate & "#

I also get the right answer. When I check the value of FirstDate it is 1/08/2003.

Thanks

 

[pono1]

The syntax is not fun to look at but I think this should get you close...

MsgBox DCount("*", "tbl_Compliments_Log", _
              "[Date_Raised]>=" & "#" & _
              FirstDate & "#" & _
              " AND [Date_Raised]<=" & _
              "#" & SecondDate & "#")

Regards,
Tim

 

[Jon K]

By default, Access treats any dates surrounded by the # signs as in US date format (unless the day is larger than 12th). So Between #1/08/2003# And #31/08/2003# is Between January 8th and August 31st, 2003.

On systems not using US date format, you should avoid using the # signs to delimit dates.


CompsDates = DCount("*", "tbl_Compliments_Log", "[Date_Raised] Between DateValue('" & FirstDate & "') AND DateValue('" & SecondDate & "')")
.

 

[Rocinante]

Thanks TIm,

I wrote your code as
CompsDates = DCount("*", "tbl_Compliments_Log", "[Date_Raised]>=" & "#" & FirstDate & "#" & " AND [Date_Raised]<=" & "#" & SecondDate & "#")

and still got wrong answer. (I must admit as a relative new comer to Access I am having a little trouble working out the rukles as to how & and " ' work!)

I not the post after yours and that may provide the clue.

Regards

Stephen

 

[Rocinante]

Thanks Jon K

That worked. I have purchased several texts and search several good sites on the net but no where have I found that using # reverts date back to US format. Really appreciate your sharing of knowledge.

Regards

Stephen