DCount

[mike60smart]

Hi
I cannot get a DCount of the number of times the same Date is entered into a Table.

My latest attempt is as follows with no Joy, it displays 0
Any help appreciated.

=DCount("BatchDate","tblBatchData","BatchDate = " & [Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate])

 

[Pat Hartman]

What is our rule when we use string dates? Our rule is - format them as mm/dd/yyyy or yyyy/mm/dd

=DCount("BatchDate","tblBatchData","BatchDate = " & [Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate])

BECOMES:

=DCount("BatchDate","tblBatchData","BatchDate = #" & Format([Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate]), "yyyy/mm/dd") & "#"

This solution of course will NEVER work if you made the mistake of using Now() rather than Date() to generate the BatchDate

 

[MajP]

should work if you used now()

DCount("BatchDate","tblBatchData","cint(BatchDate) = " & cint([Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate])

 

[mike60smart]

What is our rule when we use string dates? Our rule is - format them as mm/dd/yyyy or yyyy/mm/dd

BECOMES:

=DCount("BatchDate","tblBatchData","BatchDate = #" & Format([Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate]), "yyyy/mm/dd") & "#"

This solution of course will NEVER work if you made the mistake of using Now() rather than Date() to generate the BatchDate

Many thanks Pat & MajP

I had not used Now for the Date

 

[pbaldy]

should work if you used now()

DCount("BatchDate","tblBatchData","cint(BatchDate) = " & cint([Forms]![frmBatchDates]![frmBatchData].[Form]![BatchDate])

It would need to be CLng().

 

[MajP]

Yes, should be just INT not CINT.

 

[Solo712]

Yes, should be just INT not CINT.

The point may be moot but, FWIW, pbaldy is most probably right. Neither "CINT" nor "INT" worked for me as I tested the casting. In my example (below) only Clng worked.

x = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM MyBatch WHERE Clng(txtDate) = " & _
                   CLng(DateSerial(2024, 3, 10))).Fields(0).Value

 

[The_Doc_Man]

The problem with CINT is that if you do a DEBUG.PRINT CLNG( DATE() ) you get a day in the 43,500 range (exact date immaterial). But 43K is greater than 32767, which is the largest possible positive INTEGER. What you have is technically an integer overflow condition for CINT - but CLNG has a wider integer range so handles that number just fine.

 

[MajP]

The point may be moot but, FWIW, pbaldy is most probably right. Neither "CINT" nor "INT" worked for me as I tested the casting. In my example (below) only Clng worked.

Do not know what to tell you but your are wrong. Int(date) works. Always has. Not sure how you tested it, but here is a simple test test
Table1 Table1

testDate
3/10/2024 1:00:00 PM​
3/10/2024 4:55:00 AM​
3/11/2024 1:00:00 AM​

SELECT ID, Table1.testDate
FROM Table1
WHERE Int([testDate]) = Int(Now())

Or

SELECT Table1.ID, Table1.testDate
FROM Table1
WHERE Int([testDate]) = Date()

Or

SELECT Table1.ID, Table1.testDate
FROM Table1
WHERE Int([testDate]) =DateSerial(2024,3,10)

Query1 Query1

ID	testDate
1​	3/10/2024 1:00:00 PM​
2​	3/10/2024 4:55:00 AM​

 

[ebs17]

... WHERE Clng(txtDate) = " & _
                   CLng(DateSerial(2024, 3, 10))).Fields(0).Value

Something like this shouldn't be presented to newcomers to copy.

WHERE Clng(txtDate) ...
A calculation on the pure table field prevents possible index use. Why would anyone do something like that without necessity?
Regardless of whether you need the possible performance or not: This is very bad habit formation.
On the left side the calculation can be omitted, the date field would also be comparable to a number calculated on the right side.

CInt or even multiplication by 1 returns a date value to the internal number.
However, database systems have implemented day 0 in different ways. In MS Access this is December 30, 1899. In SQL Server it's probably a little different by default.

In order to remain safe for use, you need SQL-compliant formatting for the right side when a date value is supplied from outside (form text field, variable) using either the American format or the ISO format

... WHERE DateField = " & Format(DateVariable, "\#mm\/dd\/yyyy\#")
' or
... WHERE DateField = " & Format(DateVariable, "\#yyyy\-mm\-dd\#")

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com