How to count duplicated date

[Jon]

Greeting,

I have table and it has field for date and it has too many duplicated date.
I have unbound form and it has unbound textbox.
What I want to do is input a date in the textbox and click on command button
after that access will matching the input date with the records and if there
are duplicated date will give the total number of them.
I use the following code but does not work

Dim dd As String
dd = Nz(DCount("[ndate]", "[dd]", "[ndate]=" & (Me.aa)), 0)
MsgBox dd

Any help please??

 

[Scott Lichtenberg]

Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

 

[Jon]

Thank you Scott, but does not work!!! please Advice??

Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

Greeting,

I have table and it has field for date and it has too many duplicated
date.
I have unbound form and it has unbound textbox.
What I want to do is input a date in the textbox and click on command
button
after that access will matching the input date with the records and if
there
are duplicated date will give the total number of them.
I use the following code but does not work

Dim dd As String
dd = Nz(DCount("[ndate]", "[dd]", "[ndate]=" & (Me.aa)), 0)
MsgBox dd

Any help please??

 

[Rick Brandt]

Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

Why would you substitute all of those lines of code for a simple DCount()
one-liner?

To Jon, you do not need to use Nz() around your DCount() because DCount()
never returns Null. This should work...

MsgBox DCount("*", "[dd]", "[ndate] = #" & Me.aa & "#")

 

[Jon]

Thank you Rick, but does not wok as well

Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

Why would you substitute all of those lines of code for a simple DCount()
one-liner?

To Jon, you do not need to use Nz() around your DCount() because DCount()
never returns Null. This should work...

MsgBox DCount("*", "[dd]", "[ndate] = #" & Me.aa & "#")

 

[Rick Brandt]

Thank you Rick, but does not wok as well

I was assuming that Me.aa is a control on your form that contains a date
value. Is that correct?

Do the dates in your table include times as well? If so then you will only
find records that match what you enter into the form exactly to the second.
Note that formatting does not affect this. Only what is actually stored.

Do you get a response with no WHERE clause?
MsgBox DCount("*", "[dd]")

Do you get a proper response with a hard-coded WHERE clause value?
MsgBox DCount("*", "[dd]", "[ndate] = #2008-08-31#")

 

[MikeJohnB]

Please clarify,

What is the table name where [ndate] is a field?
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Thank you Rick, but does not wok as well

Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

Why would you substitute all of those lines of code for a simple DCount()
one-liner?

To Jon, you do not need to use Nz() around your DCount() because DCount()
never returns Null. This should work...

MsgBox DCount("*", "[dd]", "[ndate] = #" & Me.aa & "#")

 

[Jon]

hi Mike

The table name is dd , field name is ndate and unbound textbox is aa

Please clarify,

What is the table name where [ndate] is a field?
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Thank you Rick, but does not wok as well

Scott Lichtenberg wrote:
Jon,

Try SQL instead of the Dcount.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb

'Set up SQL string . Note the format for date fields in Access -
#01/01/2008#
strSQL = "SELECT COUNT(*) AS DateCount FROM MyTable WHERE ndate = #" &
Me!MyField & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.Recordcount = 0 Then
MsgBox "Date Not Found"
Else
MsgBox "Date Found. Number of occurances is: " & rs!DateCount
End If

Set rs = Nothing
Set db = Nothing

Why would you substitute all of those lines of code for a simple DCount()
one-liner?

To Jon, you do not need to use Nz() around your DCount() because DCount()
never returns Null. This should work...

MsgBox DCount("*", "[dd]", "[ndate] = #" & Me.aa & "#")

 

[Jon]

Thank you Rick

I was assuming that Me.aa is a control on your form that contains a date
value. Is that correct?

Yes, it is unbound text box

Do the dates in your table include times as well? If so then you will only
find records that match what you enter into the form exactly to the second.
Note that formatting does not affect this. Only what is actually stored.

No, it does not

Do you get a response with no WHERE clause?
MsgBox DCount("*", "[dd]")

No, I do not

Do you get a proper response with a hard-coded WHERE clause value?
MsgBox DCount("*", "[dd]", "[ndate] = #2008-08-31#")

No, I do not

please advice??

Thank you Rick, but does not wok as well

I was assuming that Me.aa is a control on your form that contains a date
value. Is that correct?

Do the dates in your table include times as well? If so then you will only
find records that match what you enter into the form exactly to the second.
Note that formatting does not affect this. Only what is actually stored.

Do you get a response with no WHERE clause?
MsgBox DCount("*", "[dd]")

Do you get a proper response with a hard-coded WHERE clause value?
MsgBox DCount("*", "[dd]", "[ndate] = #2008-08-31#")

 

[Douglas J. Steele]

<picky>

Remember that not everyone has his/her Short Date format set (in Regional
Settings in the Control Panel) to a format that Access will recognize. If,
for instance, the Short Date format is dd/mm/yyyy, that will fail.

Since you have no control over the user's Short Date format, you'd always
best off using

MsgBox DCount("*", "[dd]", "[ndate] = " & Format(Me.aa, "\#yyyy\-mm\-dd\#"))

</picky>

 

[Rick Brandt]

Do you get a response with no WHERE clause?
MsgBox DCount("*", "[dd]")

No, I do not

This can only mean...

Your code is not being run at all.
or
You have no table or query named "dd".

 

[Jon]

Thank you Douglas, I try it but still does not work!!!!! ):

<picky>

Remember that not everyone has his/her Short Date format set (in Regional
Settings in the Control Panel) to a format that Access will recognize. If,
for instance, the Short Date format is dd/mm/yyyy, that will fail.

Since you have no control over the user's Short Date format, you'd always
best off using

MsgBox DCount("*", "[dd]", "[ndate] = " & Format(Me.aa, "\#yyyy\-mm\-dd\#"))

</picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

To Jon, you do not need to use Nz() around your DCount() because DCount()
never returns Null. This should work...

MsgBox DCount("*", "[dd]", "[ndate] = #" & Me.aa & "#")

 

[MikeJohnB]

Just a thought, are you using Access 2007?

Is your database set up as a trusted site?
Did you click options, allow this content when the database opened?
If not, the code will not run.

These other guys are more switched on than me, so just trying to clarify
things.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Do you get a response with no WHERE clause?
MsgBox DCount("*", "[dd]")

No, I do not

This can only mean...

Your code is not being run at all.
or
You have no table or query named "dd".

 

[Rick Brandt]

Just a thought, are you using Access 2007?

Is your database set up as a trusted site?
Did you click options, allow this content when the database opened?
If not, the code will not run.

Excellent point.