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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
Your query would look something like:

SELECT MonthYearOfBirth, Count(1) as Birthdays
FROM [new table name]
GROUP BY MonthYearOfBirth
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 FyeOwner, Developing Solutions LLCCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.