How do I use DCount to count number of records with the same date?

I have a table with birthdays in, the format is mm/dd/yyyy. I have a query that formats this date to mm/dd as a new field then i have a form with a command button that appends these fields, along with others into a new table.  After appending the records, I want a message box telling me if the count of mm/dd is greater than 6. Can someone give me some suggestions? I can't seem to get the DCount to count the mm/dd field.
Melvin HeatwoleDeveloperAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
I just need to know if there are more than 6 individuals with the same month and day in the tblOnCalendar table, and then I plan to put them into the next day along with info that shows the day the birthday actually is

And what if that next day already has six birthdays recorded?

But you could loop the recordset:

Dim rs As DAO.Recordset
Dim Days As Integer
Dim LastDate As Date

Set rs = CurrentDb("Select * From tblOnCalendar Order By [DateField]")
LastDate = rs!DateField.Value
While Not rs.EOF
    If rs!DateField.Value = LastDate Then
        Days = Days + 1
    Else
        ' Reset counter.
        Days = 1
    End If
    If Days = 6 Then
        rs.Edit
            rs!DateField.Value = DateAdd("d", 1, rs!DateField.Value)
        rs.Update

        ' To start over, uncomment:
        'rs.Requery
        'rs.MoveFirst
        'LastDate = rs!DateField.Value
        'Days = 0
        ' -------------------------------------

    End If
    rs.MoveNext
Wend
rs.Close

Open in new window

1
 
Dale FyeCommented:
ARe you looking for a specific MM/DD value?  If you are using the Format( ) function to save the data in the "new table", then it will be saved as text.  You might do something like:

DCOUNT("*", "new table name", "MonthYearOfBirth = "02/18")

If that is not what you are looking for, then provide some sample data and what you would expect the result to look like given that data.

Dale
0
 
Melvin HeatwoleDeveloperAuthor Commented:
Thanks for the quick response.
I did not realize that when I use the Format function that it is saved as text. Now could you tell me how to get the number of duplicates that there are for each date?
I am making a birthday calendar database and the calendar only has room for 6 birthday's. So I would like to know if there are more than 6 records that have the same day and month.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
Your query would look something like:

SELECT MonthYearOfBirth, Count(1) as Birthdays
FROM [new table name]
GROUP BY MonthYearOfBirth
0
 
PatHartmanCommented:
There is no need to make a temporary table first and in fact making temp tables just causes bloat so do not make them unless absolutely necessary.  Just do the count in the query.

Select Format(DOB, "mm/yyyy") As MYDOB, Count(*) As CountMYDOB
From YourTable
Group By Format(DOB, "mm/yyyy")
Order by Format(DOB, "mm/yyyy");

Queries can be used as the recordsource for forms or reports just as tables can so storing this data is completely unnecessary.
0
 
Dale FyeCommented:
Pat makes a very good point.  I was not certain whether you were using that temporary table for some other purpose, but if you are only doing this to get the count of birthdays by month, her method is better.

Dale
0
 
Melvin HeatwoleDeveloperAuthor Commented:
I will explain further. I have a table that has all the information about each individual. Birthday, First Name, Last Name, Parents,  etc. There is also a field called ysnOnCalendar.
I have a form that is based on this table. On this form you can select who you want to be on the Calendar by clicking in the ysnOnCalendar field. Then when you click a command button to preview the calendar,there is code that uses INSERT INTO to put all the records that were checked into a table called tblOnCalendar. Then there is code that checks to see if the calendar is a leap year, and if there are individuals with birthdays on Feb 29. If the Calendar is not for a leap year, and there are birthdays on Feb 29, then it changes the birthday of the Feb 29 individuals to March 1. So, it is not really a temporary table that I am using. The code then changes the persons birthday only in the tblOnCalendar table because I don't want to change the individuals actual birthday. That part is working well. Now I just need to know if there are more than 6 individuals with the same month and day in the tblOnCalendar table, and then I plan to put them into the next day along with info that shows the day the birthday actually is. I can make a query count, but I want to do it in VBA.
0
 
Melvin HeatwoleDeveloperAuthor Commented:
I pasted this code in and changed the "DateField to the name of the field in my table, but when I run it, it  comes up with an error. The error is "Run-time error 3265. Item not found in this collection." When I debug, it is highlighted on line 5 and says object variable not set. Wonder what I am doing wrong?
0
 
Gustav BrockCIOCommented:
Item not found means a misspelled table or field.
0
 
Melvin HeatwoleDeveloperAuthor Commented:
I had to add a Dim statement "Dim db As Database" and "Set db = CurrentDb()"
I uncommented the Requery, MoveFirst etc. but it only changes one date, then if I run it again, it will do another date. I must be missing something. Otherwise, I think it will work well. Thanks for the excellent help!
0
 
Gustav BrockCIOCommented:
Oh, I see I missed something, sorry. This line should read:

Set rs = CurrentDb.OpenRecordset("Select * From tblOnCalendar Order By [DateField]")

Open in new window

0
 
Melvin HeatwoleDeveloperAuthor Commented:
I uncommented the following:
        'rs.Requery
        'rs.MoveFirst
        'LastDate = rs!DateField.Value
        'Days = 0
but if I have 8 days the same, it only changes 1 date until I run it the second time then it changes another one. What am I missing?
0
 
Gustav BrockCIOCommented:
Well, it's air code.
If you have some sample data, I could check it out.
0
 
Melvin HeatwoleDeveloperAuthor Commented:
I think I got it figured out. Just needed to change the Days=0 to Days=1
Thanks for your help.
0
 
Gustav BrockCIOCommented:
Great!
0
 
Melvin HeatwoleDeveloperAuthor Commented:
Thanks a lot for your help. It is working well now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.