Birthday Query messes up when adding filter

[chellebell1689]

Hello,
I have a birthday report that I do every week. This report is based on a birthday query which checks the member's next birthday (generated by a query "NextBdayQuery") and sorts them based on who their deacon is. This works fine until I tell the first query (or either query really) to check the member's type (are they child, visitor, joined, etc; this filters out deceased, moved, transferred). Once I add this filter, the query then adds a copy of each member for each deacon. Please help!! Attached is the db with just the birthday stuff.

 

[AlexN]

Hello,
I have a birthday report that I do every week. This report is based on a birthday query which checks the member's next birthday (generated by a query "NextBdayQuery") and sorts them based on who their deacon is. This works fine until I tell the first query (or either query really) to check the member's type (are they child, visitor, joined, etc; this filters out deceased, moved, transferred). Once I add this filter, the query then adds a copy of each member for each deacon. Please help!! Attached is the db with just the birthday stuff.

The way you have your relationships you'd have to add your Family table too in the query.

P.S. Your database needs serious normalization

 

[chellebell1689]

Ok that worked, for the most part. I have the filter on the first query (figured it would be easier down the road), but now when I try running the second query or open the report, I get "Data type mismatch in criteria expression." In the second query, the only criteria I have is the "NextBirthday" field which just filters out only this week's birthdays (well the ones happening in the next 7 days).

 

[AlexN]

Ok that worked, for the most part. I have the filter on the first query (figured it would be easier down the road), but now when I try running the second query or open the report, I get "Data type mismatch in criteria expression." In the second query, the only criteria I have is the "NextBirthday" field which just filters out only this week's birthdays (well the ones happening in the next 7 days).

That's because you apply date criteria on a string field.

 

[chellebell1689]

So how do I fix it? I'm kinda new...

About the normalization, it looks messy here because this is only a snipit the actual database has A LOT more to it. I just included the basics

 

[AlexN]

So how do I fix it? I'm kinda new...

I think you'll have to fix it in your first query using CDate function and then pass it to your second query. (I didn't try it).

About the normalization, it looks messy here because this is only a snipit the actual database has A LOT more to it. I just included the basics

At least Members table has normalization issues and will cause you trouble for sure. (8 Fields for children? What happens if someone has 9?)

 

[chellebell1689]

Couple questions while I try that fix.

Why would adding a filter for member type affect the way everything is set up? Before I added the filter, it worked just fine.

At least Members table has normalization issues and will cause you trouble for sure. (8 Fields for children? What happens if someone has 9?)

How would I be able to fix this without just making one field to list all the kids?

 

[AlexN]

Couple questions while I try that fix.

Why would adding a filter for member type affect the way everything is set up? Before I added the filter, it worked just fine.

Like I said before, that happens because your filter is a date and you apply it on a field that isn't (no matter how it looks - DateSerial result isn't a date although it looks like one).

How would I be able to fix this without just making one field to list all the kids?

You're gonna have to redesign your database. If you're willing to do so, (I'm afraid you have no other options if you want it to be functional) let me know and I'll show you a way of properly designing your project.Others might do so too.
e.g. Think of Households as members of the community, and People as members of Households.

 

[chellebell1689]

Ok so I tried using the CDate in both queries. I set up the NextBirthday field just like normal, then I added a field and put CDate([NextBirthday]). It shows up in both queries, but when I apply the filter for this week:
Between Date() And DateAdd("d",7,Date())
and then I get the data mismatched error

You're gonna have to redesign your database. If you're willing to do so, (I'm afraid you have no other options if you want it to be functional) let me know and I'll show you a way of properly designing your project.Others might do so too.
e.g. Think of Households as members of the community, and People as members of Households.

I am always up for getting help and pointers! I think I might know where you're going with this, and if you look at the family table, I thought about adding each kid & parent to that table assigned to a family, but then I got to thinking, wouldn't it mess it up if they're listed as a child in one family and a parent/head of house in another? (If that's not where you're going, ignore that lol)

 

[AlexN]

Ok so I tried using the CDate in both queries. I set up the NextBirthday field just like normal, then I added a field and put CDate([NextBirthday]). It shows up in both queries, but when I apply the filter for this week:
Between Date() And DateAdd("d",7,Date())
and then I get the data mismatched error

It doesn't make sense, it works fine on my copy.

I am always up for getting help and pointers! I think I might know where you're going with this, and if you look at the family table, I thought about adding each kid & parent to that table assigned to a family, but then I got to thinking, wouldn't it mess it up if they're listed as a child in one family and a parent/head of house in another? (If that's not where you're going, ignore that lol)

That's why you have to plan first, design later. Having households (Families) as members of the community, and people (Persons) as members of households, would do your job as it seems. Relationship between households doesn't seem to be your primary subject as far as it shows.

 

[chellebell1689]

Idk why it's not working. I've tried using CDate([NextBirthday]) and then I applied the criteria Between Date() And DateAdd("d",7,Date()) to it and I get data mismatch error. I've even tried applying the criteria to the normal field. It runs fine if I don't have that criteria, but I need it so I only see this week's birthdays.

 

[chellebell1689]

I figured it out! When it pulls the birthday from the member table, I told it the criteria is "Is Not Null" apparently that is not the correct way to write that, because it still pulled a few null birthdays and that's what's causing the problem. Can someone tell me the correct way to write that, please?

 

[chellebell1689]

Ok, figured that part out ("Not Null" is the correct way).

Now my report isn't working. It shows each member several times. Gonna try to re-create the report and see if that doesn't work. Feel free to post any possible solutions.

**EDIT**
I tried recreating the report, same thing. I looked the query for this week's birthdays shows them several times as well, but the first query "NextBdayQuery" only shows each once. I looked, the db I provided in an earlier post does the same thing. Also, after playing around with the provided db, I noticed each duplicate in the second query and report equals the number of members in the Members table. So basically the NexBdayQuery is passing each member to the next query for to equal the number of members. (I.E. if I have three members, each member is passed to the second query three times.)

 

[AlexN]

Remove tblMembers from the second query. There isn't a reason for it to be there.
In attached copy there's only one record so what you're describing isn't happening. (Happy birthday by the way).
All these though, are normalization problems and I'm afraid you're gonna get a lot of these as you move on.

 

[chellebell1689]

That fixed it! Thank you! I'm still kind of new to all this, I learned the very basics in high school and again in college, but to do a real database like I'm doing now is more than we learn in school. I've been working on this since the beginning of Jan, and this is my second do-over. (The first one was WAAAAAAAY like a million times worse!)

I appreciate all the help and advice I've been getting on here! And thank you for your help!

 

[AlexN]

Glad you figured it out!

This might help in seeing things in a better way --> http://msdn.microsoft.com/en-us/library/office/aa662176(v=office.11).aspx