DCount Between From Date & To Date

[madhouse]

I've found many different solutions to a problem with dates that I'm having in this forum, but I just can't seem to get any of them to work for me.

Basically I've got a field called TransDate that's defined as Date Type Date/Time and Format Short Date. This is the date for transactions which are processed via the database. Then I've got a form with 2 fields where a user can enter a To Date and From Date - both fields are Format Short Date and have an Input Mask 00/00/0000;0;*. On the same form I have a command button - the code behind this button is where I'm trying to do a count of all transactions between the dates entered. However, I seem to be getting "silly" results being returned. Take the following records in my transaction table:

08/07/2004
08/07/2004
08/07/2004
08/07/2004
08/07/2004
10/07/2004
10/07/2004

This is a selection of 7 records in the table. Now if I enter the dates 01/07/2004 and 11/07/2004 it returns a count of 7 which is correct. But if I enter the dates 08/07/2004 and 09/07/2004 it returns a count of 0 when it should return a count of 5. I have tried various means and ways of counting the records, here are a few:

intCount = DCount("TransNo", "tblTransP", "TransDate BETWEEN " & CDate(Me.txtFromDate) & " AND " & CDate(Me.txtToDate))

intCount = DCount("TransNo", "tblTransP", "TransDate BETWEEN " & Format(Me.txtFromDate, "dd/mm/yyyy") & " AND " & Format(Me.txtToDate, "dd/mm/yyyy"))

intCount = DCount("TransNo", "tblTransP", "TransDate BETWEEN #" & Me.txtFromDate & "# AND #" & Me.txtToDate & "#")

Unfortunately none of these seem to return the correct results. What am I doing wrong?

By the way, I'm in the UK as I know some of the replies in this forum regarding date questions are aimed at US users.

 

[Jon K]

Since the date delimiter "#" defaults to US date format, you can convert txtFromDate and txtToDate to US date format before surrounding them with the # signs. For example:

  Dim USfromDate As String
  Dim UStoDate As String
  
  USfromDate = Month(CDate(Me.txtFromDate)) & "/" & _
               Day(CDate(Me.txtFromDate)) & "/" & _
               Year(CDate(Me.txtFromDate))
                
  UStoDate = Month(CDate(Me.txtToDate)) & "/" & _
             Day(CDate(Me.txtToDate)) & "/" & _
             Year(CDate(Me.txtToDate))

  intCount = DCount("TransNo", "tblTransP", _
     "TransDate BETWEEN #" & [b]USfromDate[/b] & "# AND #" & [b]UStoDate[/b] & "#")

 

[DLB]

Can we surround date fields with quotes?

Try surrounding your date fields with quotes. Access expects string dates in VBA to be in US format when they are delimited by #'s. If you delimit them with " instead, they will be interpreted as being in your windows default short date format.

When I surrounded a date with chr(34), I just got a data type mismatch error:

Run-time error '3464':

Data type mismatch in criteria expression.

 

[madhouse]

Thanks for the replies guys but I got it working by using the following:

Dim dbs As Object 
Dim rst As Object 
Dim lngCount As Long 
Set dbs = CurrentDb 
Set rst = dbs.OpenRecordset("Select Count(*) From tblTransP Where CDbl([TransDate]) BETWEEN " & CDbl(Me.txtFromDate) & " AND " & CDbl(Me.txtToDate)) 
lngCount = rst(0) 
rst.Close 
dbs.Close 
Set rst = Nothing 
Set dbs = Nothing

 

[DLB]

I have tried various means and ways of counting the records, here are a few:
..................
..................
Unfortunately none of these seem to return the correct results. What am I doing wrong?

Thanks for the replies guys but I got it working by using the following:
.................

There are usually more than one way to do a thing.

Since you are not interested in knowing how to make your original code work (I find Jon K's suggestion works), you can create a query e.g. Query1:-

SELECT *
FROM tblTransP
WHERE TransDate Between [forms]![formName]![txtFromDate] And
[forms]![formName]![txtToDate];
​

Then in the VBA code on the form, you can simply use:-

intCount = DCount("*", "Query1")
​

There is no need to open a recordset.
It works no matter whether you are in the US or in the UK.

 

[Mile-O]

Thanks for the replies guys but I got it working by using the following:

Dim dbs As Object 
Dim rst As Object

Be explicit.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

 

[DLB]

Thanks Pat,

Putting single quotes inside DateValue() works:-

intCount = DCount("TransNo", "tblTransP", "TransDate BETWEEN DateValue('" & Me.txtFromDate & "') AND DateValue('" & Me.txtToDate & "')")


Obviously we need to use the DateValue() function to change the strings back to date/time to avoid data type mismatch.