That might be it. I just added a record without a date and got the error againit 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
I was not aware I had that in the table previously.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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:
Code:WHERE ((([nextbirthdate]) Between Date() And Date()+7));
good luck with your project,
Cottonshirt
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.however, you need to use Between when comparing dates.
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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().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...
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.
this gives you a DateSerial() date representing when their birthday will fall, this year.Code: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 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.Code:SELECT qry_nextbirthdate1.athlete, qry_nextbirthdate1.birthdate, qry_nextbirthdate1.nextbd FROM qry_nextbirthdate1 WHERE (((qry_nextbirthdate1.nextbd) Between Date() And Date()+7));
and, on a second point of confusion. earlier in the thread Ajax posted this:
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."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
a fine distinction, but one worth noting.
take care,
Cottonshirt
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.
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
When they ever go to the record, check if Null/Empty and put up a msg box ?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Ohhhkaaaay...this sounds clever but is actually irrelevant in this context
Pardon me for trying to enlighten the OP with any general information.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.