Birthdays Within Next 7 Days Query

[Welshgasman]

it does work

?date()>dateserial(2022,11,14)
False

I suspect it is because for some reason the user has done the dateserial part in one query and applied the criteria in another. I mentioned it in post#8. Not sure why it would make a difference

However been doing some testing of my own and I now think it is to do with records with a null birthday field (it generates the same error message) - need to include another criteria

Where Birthday is not null

That might be it. I just added a record without a date and got the error again
I was not aware I had that in the table previously.

[Cottonshirt]

the problem you are experiencing is because when comparing two dates you are using > and < and = signs in your criteria. this kind of makes intuitive sense because we are taught to think of dates in ACCESS as numbers, and we use > and < and = to compare numbers. however, you need to use Between when comparing dates. so the solution to your problem is two-fold.

step 1: for each person in your DB for whom you have a date of birth, you first run a query that determines their birthday this year.
to do this you simply do DateSerial(Year(Date()),month([birthdate]), day([birthdate])) as nextbirthdate

this gives an output like this:

0001 23/03/2021
0002 24/04/2021
0003 17/7/2021

step 2: then you run a query to find dates that meet the criteria:

WHERE ((([nextbirthdate]) Between Date() And Date()+7));

good luck with your project,


Cottonshirt

[Welshgasman]

No, you can use > and < with/without = without issues, especially when there is only one date.
I do prefer Between when there are two dates though.

[Micron]

however, you need to use Between when comparing dates.

Maybe take another look at the notion you posted. If dates are numbers as you said (they are) then it makes perfect sense that you can use <,>,= or combinations thereof, for dates. Not sure where you got the info from but you've been misled.

Where many people struggle with criteria such as StartDate and EndDate is when EndDate field contains time portion because any time value in the date field that is after 00:00:00 is greater than EndDate so records for the day specified are not returned unless you compensate.

[Cottonshirt]

Micron said: Where many people struggle with criteria such as StartDate and EndDate is when EndDate field contains time portion because any time value in the date field that is after 00:00:00 is greater than EndDate...

this sounds clever but is actually irrelevant in this context. the OP started with the DateSerial() function, which returns a date with no time component. it is exactly synonymous with Date(), and not with Now().

when you look at the return from the DateSerial() function, what you see is a five-digit integer that looks like a number. today is 44516, and we are taught in basic ACCESS 101 to think of this as a number. I agree with you on this.

however, if you use the Vartype() function on this, to see what data type it is, ACCESS says it is type 7, a date.

ACCESS doesn't care what you are taught, or how you think about date functions, or how you parse calculations with dates as though they were numbers. ACCESS thinks the output from the DateSerial() function is a date.

so, you might be able to have a criteria such as WHERE (([birthdate])> Date()); when [birthdate] is an actual date, but this won't work with DateSerial() and you have to use Between.

the point is that if you are working with DateSerial(), when you put > or < or = into the criteria these are mathematical functions and ACCESS is expecting a number, and DateSerial() is not a number, in this context. ACCESS says, "well, I was expecting a number there but you gave me a date, so that's a data type mismatch error right there."

I tested this on a database with around 15000 birthdates in it.

SELECT tbl_athlete.athlete, tbl_athlete.birthdate, DateSerial(Year(Date()),Month([birthdate]),Day([birthdate])) AS nextbd
FROM tbl_athlete
WHERE (((tbl_athlete.birthdate) Is Not Null))
ORDER BY DateSerial(Year(Date()),Month([birthdate]),Day([birthdate]));

this gives you a DateSerial() date representing when their birthday will fall, this year.

SELECT qry_nextbirthdate1.athlete, qry_nextbirthdate1.birthdate, qry_nextbirthdate1.nextbd
FROM qry_nextbirthdate1
WHERE (((qry_nextbirthdate1.nextbd) Between Date() And Date()+7));

this finds the records where the birthday this year falls in the next 7 days. it works. it actually answers the OP's question. in my DB it found 319 records with birthdays between 16 and 23 Nov 2021.

and, on a second point of confusion. earlier in the thread Ajax posted this:

Ajax said:
dateserial does return a date datatype

?isdate(dateserial(2022,11,14))
True

but then so does this with an non date
?isdate(dateserial(2022,15,14))
True

worth noting that if IsDate([adate]) says True (it actually returns -1) this isn't saying "[adate] is a date." it is saying, "[adate] is either a date, or it can be converted to a date using the CDate() function."

a fine distinction, but one worth noting.


take care,


Cottonshirt

[Ant_Snell]

Thanks everyone! At least I have some consolation that it not just me (being a novice). I can confirm that out of the record set of several thousand records a hand full do have a null value in the DOB filed. I will re-visit this after first filtering out the Null values, and post if that solves the issue. It might be a while as I have pressing other jobs ATM.

[Ant_Snell]

WoW! Simple when you know how. Filtering out the Null DOB fields fixed it. The DOB filed should never be null but obviously was in some cases. I don't think I make it a required field as I understand that sometimes it may not be known so thinking about some kind of nag screen that will keep reminding users to find and enter a DOB ASAP. suggestions welcome

[Welshgasman]

When they ever go to the record, check if Null/Empty and put up a msg box ?

[Micron]

this sounds clever but is actually irrelevant in this context

Ohhhkaaaay...
Pardon me for trying to enlighten the OP with any general information.