This Weeks Birthday(s) Query

[chellebell1689]

So I found this video (youtube.com/watch?v=FTjyuSTcTII&spfreload=10) and followed it to do a query for all upcoming birthdays. The video shows how to do it for the next two months and I tried to modify it just for the next 7 days. When I tried to run it, I got "Date type mismatch in criteria expression" error. I tried putting it back in the criteria she had, but still got the same thing.

At 6:53 you can find the code she used for the first table (basically asking when their next birthday is) and at 9:57 is the code for the criteria. Below is my modification.

Between Date() And DateAdd("d",7,Date())

Thanks in advance!

 

[plog]

Your criteria is good. My guess is the field that criteria is being applied to isn't a date field.

 

[chellebell1689]

I don't know how to change it. It's in a query field and the field it's being applied to is a field that shows the person's next birthday (i.e. Jon Doe's is 3/23/2016). It's just trying to find those that have a birthday between today and 7 days from now.

 

[plog]

In the underlying table, what data type is the field?

 

[chellebell1689]

The original table is the member's table and the first query (which is set up to specify if their next birthday is this yr or next yr) pulls the birthday from there, it's a date field. Then the second query (the one that filters for the current week's birthdays) pulls the next birthday field from the first query.

If you have time, go to the youtube video I linked (couldn't put the "www" because I haven't posted enough to link). It shows how to do both queries. I followed it, but somehow I messed it up.

 

[chellebell1689]

I went to the table and verified that the birthday field is Date/Time field. Then I went to each table and made sure all the date fields were set up as dates in the same format (mm/dd/yyyy). And I'm still getting the mismatch error. Idk what I did differently than the video...

 

[JHB]

... Idk what I did differently than the video...

Show the SQL-string you've for both queries.

 

[chellebell1689]

Query 1 (When's their next birthday?)
SELECT [Members Table].LNAME, [Members Table].FNAME, [Members Table].MOBILEPHONE, [Members Table].BIRTHDAY, [Members Table].DEACONNAME, [Members Table].DEACONPHON, Day([BIRTHDAY]) AS [Day], Month([BIRTHDAY]) AS [Month], DateSerial(Year(Date())+IIf(Format(Date(),"mmdd")>Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day]) AS NextBirthday
FROM [Members Table];


Query 2 (Is their next birthday this week?)
SELECT [Next Birthday Date].LNAME, [Next Birthday Date].FNAME, [Next Birthday Date].MOBILEPHONE, [Next Birthday Date].BIRTHDAY, [Next Birthday Date].DEACONNAME, [Next Birthday Date].DEACONPHON, [Next Birthday Date].NextBirthday
FROM [Next Birthday Date]
WHERE ((([Next Birthday Date].NextBirthday) Between Date() And DateAdd("d",7,Date())));

 

[plog]

You probably have NULL values in [Members Table]. You should add a WHERE clause to Query 1 to exlude records with a [BIRTHDAY] value that is NULL.

Additionally, you shouldn't use [Day] nor [Month] as field names. They are reserved words and cause issues when you try to code things. I suggest prefixing them with what they represent (e.g. BirthDayNumber, BirthMonth)

 

[chellebell1689]

Any chance you (or anyone) could point me in the direction of a webpage that can tell me how to do that? (Preferably with out use of code. I only know how to code in GMS.)

 

[plog]

What's GMS?

 

[chellebell1689]

Game Maker Studio

 

[chellebell1689]

Can someone help me with the WHERE statement, please? Preferably in expression builder. Here's the main expression:
NextBirthday: DateSerial(Year(Date())+IIf(Format(Date(),"mmdd")>Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day])

 

[plog]

You should add a WHERE clause to Query 1 to exlude records with a [BIRTHDAY] value that is NULL.

In design view of Query 1, put this under the BIRTHDAY field:

Is Not Null

 

[chellebell1689]

It works!! Thank you so very much!!!