Why is DCount expression returning wrong values?

[Scott Pollock]

Hey everyone, I am a complete newb when it comes to programming and Access so bear with me. I developwed an application in Access 2007 to track tickets written for problems with our hardware (issue tracker). I want to count the number of tickets on and/or prior to a date entered in a text box. Here's what I got so far:

Dim enterdate As Date
 
enterdate = txtDate.Value
 
txtTotal.Value = DCount("*", "tblTickets", "[TicketDate] <=" & "#" & enterdate & "#")

Where txtTotal is another text box on the form to display the answer.

This code is executed by command button and when this function is ran it returns a completely wrong number.

Other pieces of information: Entries to the "TicketDate" column in tblTickets is formatted using the Now() function which, as you all know, returns not only the date but the time as well.

I am thinking that their needs to be some of conversion so that I get the correct number from this expression.

If anyone could shed some light on this, it would be greatly appreciated

[NeoPa]

I suspect it's more likely to be related to the format of a date literal in SQL. Check out Literal DateTimes and Their Delimiters (#).

[Scott Pollock]

So should I use the Format() function on the variable or the table column?

[NeoPa]

Try something like this :

Me.txtTotal = DCount("*", _
                     "tblTickets", _
                     "[TicketDate] <= " & Format(Me.txtDate, "\#m/d/yyyy\#")

[Scott Pollock]

NeoPa,

Thank you for spending some time on this with me. The code you provided gives me an answer, but it is still the wrong one.

So I created a SQL query that essentailly does the same thing and the recordset it returns counts every single record EXCEPT the date you enter in txtDate field!

I tried hard coding a date into the VBA code you provided, and still the same thing.

I am still thinking that since the TicketDate column in my table is formatted using the "Now()" function, Access is also trying to calculate the time associated with the date. Is there a way to extract just the date in that column? Got any more ideas?

[NeoPa]

That's because any DateTime value past midnight will always be greater than the simple Date value itself (which is numerically equivalent to midnight of that date).

Try instead :

Me.txtTotal = DCount("*", _
                     "tblTickets", _
                     "DateValue([TicketDate])<=" & Format(Me.txtDate, "\#m/d/yyyy\#")

A more sensible alternative of course, assuming you only want [TicketDate] and not [TicketDateTime], is to assign it the value of Date() rather than Now(). That way you needn't use DateValue() to trim off the time part as it won't be there.

[NeoPa]

A new post was added that has been split off into its own thread (DCount() Confusion) because thread hijacking is not allowed.