Hello
I have a fairly standard table that includes peoples' dates-of-birth. I'm creating a query that will allow people to enter a month and day-of-month to get a list of people with that birthday.
People are in the table as a couple, so I'm actually looking for a list of if either person in the relationship has a birthday on a specified day.
This works so far:
PARAMETERS [Month Number] Short, [Day Number] Short;
SELECT NEWrelationships.RelationshipName, NEWrelationships.FName1, NEWrelationships.LName1, NEWrelationships.DOB1, NEWrelationships.FName2, NEWrelationships.LName2, NEWrelationships.DOB2
FROM NEWrelationships
WHERE (((Month([DOB1]))=([Month Number])) AND ((Day([DOB1]))=([Day Number]))) OR (((Month([DOB2]))=([Month Number])) AND ((Day([DOB2]))=([Day Number])));
I would like to be able to get the list of birthdays for the entire month by using the same query. To that end, I thought user could leave the [Day Number] parameter blank and I could use IIF, IS Null, and a wildcard to make it work like this:
PARAMETERS [Month Number] Short, [Day Number] Short;SELECT NEWrelationships.RelationshipName, NEWrelationships.FName1, NEWrelationships.LName1, NEWrelationships.DOB1, NEWrelationships.FName2, NEWrelationships.LName2, NEWrelationships.DOB2
FROM NEWrelationships
WHERE (((Month([DOB1]))=([Month Number])) AND ((Day([DOB1]))=IIf(IsNull([Day Number]),'Like *',[Day Number]))) OR (((Month([DOB2]))=([Month Number])) AND ((Day([DOB2]))=IIf(IsNull([Day Number]),'Like *',[Day Number])));
If I enter a [Day Number] it gives me the correct list. If I leave [Day Number] blank, it returns an empty set (no error). I've tried using 'Like *' (as shown) as well as just *. I've also tried with and without single/double quotes. Can't seem to get it to work.
I appreciate any assistance with this.
TIA
Steve