Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Birthdays Within Next 7 Days Query

    I am trying to create a query showing birthdays within the next 7 days. I have followed the YouTube video https://www.youtube.com/watch?v=FTjyuSTcTII to the T.


    Criteria: NextBirthday: DateSerial(Year(Date())+IIf(Format(Date(),"mmdd")> Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day])
    However, when running the second query:
    Criteria: Between Date() And DateAdd("d",7,Date())
    I get a “Data Type mismatch in criteria expression” error
    I think this is telling me the NextBirthday field is not Date Type format, so I have formatted it to short date (along with all the other date fields it is based on). It certainly looks like a date type even if I format to long date type it spells out the correct month. But I still get the error even if I just try to put the NextBirthday field in ascending order, with no criteria. How can I force Access to recognise the field as a Date? The only thing I am thinking may have messed it up is that the YouTube vid is American date format mm/dd/yy and I am in the UK and use dd/mm/yy
    Any help appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think this is telling me the NextBirthday field is not Date Type format
    help if we knew what your birthday field is populated with (you shouldn't be storing next birthday as it is easily calculated and changes every year). I would expect something like "11/4", "28/6" which would be a text field. Or it could be populated as a date of birth field which would clearly include a year.

    If you are storing it as nextbirthday as a text field, use the cdate function to convert text to a date

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Ajax: The NextBirthday field is calculated based on the [Date of Birth] field in the underlying table (Formatted as date) using the formula above. First pulling out [Month] & [Day] Using "Day: Day([Date of Birth])" & "Month: Month([Date of Birth])" as shown in YouTube Video. As far as I can see everything is formatted and stored as a date format.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Day and Month are reserved words and should not be used for naming fields/columns they can generate misleading errors. Suggest call them something else - prefix with a b for example or better still just put them in your formula

    ?dateserial(datediff("yyyy",#1/01/2000#,date())-(format(#1/01/2000#,"mmdd")<format(date(),"mmdd")),month(#1/01/2000#),day(#1/01/2000#))
    01/01/2022
    ?dateserial(datediff("yyyy",#12/01/2000#,date())-(format(#12/01/2000#,"mmdd")<format(date(),"mmdd")),month(#12/01/2000#),day(#12/01/2000#))
    01/12/2021
    ?isdate(dateserial(datediff("yyyy",#12/01/2000#,date())-(format(#12/01/2000#,"mmdd")<format(date(),"mmdd")),month(#12/01/2000#),day(#12/01/2000#)))
    True

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Ajax: thanks but I am still getting the “Data Type mismatch in criteria expression” error when trying to add criteria or sort. That's after renaming the Month & Day fields to BMounth & BDay. If I create a calculated field TEST:IsDate([NextBirthday]) I get a -1 result indicating that it is a date field. Totally baffled

  6. #6
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Ajax: Not the best solution but I have just made my query into a Make Table query and created a TEMP_NextBirthday table. I can then create a query based on the new table and all works as it should. Sledge hammer to crack a nut but at least it works.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I got 4 minutes into that video and had to wonder why go the route with DateSerial and other such functions when you could just use
    Between Date() And DateAdd("d",7,Date())

    Perhaps a date conversion function would help you next time. I would not continually re-create a table for this. Some say it's a recipe for corruption.
    http://allenbrowne.com/ser-36.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I wouldn't create a temporary table either. But to OP working from a date of birth is looking for birthdays in the next 7 days - hence the need to add years to the birthday. Don't know why the OP needs a second query but that seems to be where the problem is

    If the OP showed the full sql to both queries, we might discover the issue is somewhere else

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    working from a date of birth is looking for birthdays in the next 7 days
    Ahh, yes. Well another way would be to compare day and month numbers like the following, not that it is any better.

    Click image for larger version. 

Name:	DOBquery.jpg 
Views:	18 
Size:	27.0 KB 
ID:	46589

    Seems to work but I have limited data to test with. The query returns 1 record with DOB of 11/16/2018 which looks correct. Just another approach.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    What happens when the 7 days go into the next month?
    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

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    OK now I am confused.?
    I *thought* that you could just use DateSerial(Year(Date()),Month([Transactiondate]),Day([TransactionDate])) to get the current birthday and compare against Date() and Date()+7, but I also get a type mismatch.
    MS docs say DateSerial returns a Variant(Date) https://support.microsoft.com/en-gb/...a-93f2b046f503 which I always thought was a date, though I have not used it much myself.
    Even tried CDate() on the returned value and still get Type mismatch?

    The date functions even work on the result?
    Code:
    SELECT DateSerial(Year(Date()),Month([Transactiondate]),Day([TransactionDate])) AS NextBD, Day([nextbd]) AS Expr1, Month([nextBD]) AS Expr2, Year([nextBD]) AS Expr3
    FROM Transactions;
    and even this works?
    Code:
    SELECT DateSerial(Year(Date()),Month([Transactiondate]),Day([TransactionDate])) AS NextBD, Day([nextbd]) AS Expr1, Month([nextBD]) AS Expr2, Year([nextBD]) AS Expr3, [nextbd]+7 AS Expr4
    FROM Transactions;
    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

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    If I was stuck on this and needed to proceed, I would just do this?
    Up to you to adjust whether a non date birthday by 29/02/2021 this year is a day before or a day after? I've gone for a day after.
    Not thoroughly tested either

    Code:
    Public Function NextBD(pdate As Date) As Date
    Dim dtDate As Date
    
    dtDate = DateSerial(Year(Date), Month(pdate), Day(pdate))
    If Not IsDate(dtDate) Then
        dtDate = DateSerial(Year(Date), Month(pdate) + 1, 0)
    End If
    NextBD = dtDate
    
    End Function
    Code:
    SELECT Transactions.TransactionDate, nextbd([TransactionDate]) AS Expr1
    FROM Transactions
    WHERE (((nextbd([TransactionDate])) Between Date() And Date()+7));
    HTH
    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

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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



    but what it is doing is adding 3 months (15-12)
    ?dateserial(2022,15,14)
    14/03/2023

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Ajax View Post
    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



    but what it is doing is adding 3 months (15-12)
    ?dateserial(2022,15,14)
    14/03/2023
    But when you compare that to something like Date() the query states 'mismatch in criteria', which is what started this thread

    Edit:
    Now even more thoroughly confused as now this works?
    Code:
    SELECT DateSerial(Year(Date()),Month([Transactiondate]),Day([TransactionDate])) AS NextBD
    FROM Transactions
    WHERE (((DateSerial(Year(Date()),Month([Transactiondate]),Day([TransactionDate]))) Between Date() And Date()+7));
    I swear I was getting mismatch before
    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

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  2. Replies: 8
    Last Post: 09-29-2015, 10:37 AM
  3. Birthdays
    By comfygringo in forum Queries
    Replies: 3
    Last Post: 07-11-2013, 07:29 PM
  4. Birthdays
    By bambi_ in forum Queries
    Replies: 1
    Last Post: 05-22-2012, 03:29 AM
  5. Help with SQL: Birthdays in Next 30 days
    By kaylachris in forum Queries
    Replies: 1
    Last Post: 06-21-2010, 05:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums