If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to count duplicated date
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?? |
#2
|
|||
|
|||
How to count duplicated date
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" wrote in message ... 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?? |
#3
|
|||
|
|||
How to count duplicated date
Thank you Scott, but does not work!!! please Advice??
"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 "Jon" wrote in message ... 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?? |
#4
|
|||
|
|||
How to count duplicated date
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 & "#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
How to count duplicated date
Thank you Rick, but does not wok as well
"Rick Brandt" wrote: 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 & "#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
How to count duplicated date
Jon wrote:
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#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
How to count duplicated date
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 "Jon" wrote: Thank you Rick, but does not wok as well "Rick Brandt" wrote: 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 & "#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#8
|
|||
|
|||
How to count duplicated date
hi Mike
The table name is dd , field name is ndate and unbound textbox is aa "MikeJohnB" wrote: 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 "Jon" wrote: Thank you Rick, but does not wok as well "Rick Brandt" wrote: 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 & "#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#9
|
|||
|
|||
How to count duplicated date
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?? "Rick Brandt" wrote: Jon wrote: 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#") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#10
|
|||
|
|||
How to count duplicated date
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 http://I.Am/DougSteele (no private e-mails, please) "Rick Brandt" wrote in message ... 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 & "#") |
|
Thread Tools | |
Display Modes | |
|
|