UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Datediff And Dateserial, Access 2016    
 
   
slavescu
post Feb 17 2019, 10:34 AM
Post#1



Posts: 11
Joined: 17-February 19




Hello everyone,

I have this strSQL

strSQL = "SELECT customers.ChartNumber,customers.ContactFirstName, customers.ContactLastName, customers.ContactDOB" & vbCrLf & _
"FROM customers" & vbCrLf & _
"WHERE (((DateDiff('m',Date(),DateSerial(Year(Date()),Month([ContactDOB]),Day([ContactDOB]))))=1))
;"

that basically is helping me finding upcoming birthdays for the current month and works perfectly, however for the love of god I can't figure out how to have the same format but for the upcoming 7 days instead of the current month, any guidelines in the right direction will be appreciated ,

thanks
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2019, 10:44 AM
Post#2


UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

DateDiff() has three arguments: Interval, such as Month, Day, Year. Starting Date and Ending Date, such as #2019/02/17# . Ending Date, such as #2019/02/24#.

So, to calculate the difference in days, instead of months, change the Interval argument to "d", instead of "m".

Now, you want know whether the difference is 7 or less. That's done with <= which means "Less than or equal to".


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
slavescu
post Feb 17 2019, 11:04 AM
Post#3



Posts: 11
Joined: 17-February 19



Thanks a million, i was replacing the "m" for "d" however i was doing = 7 instead of <=7...now it works, thanks again
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2019, 12:42 PM
Post#4


UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA


Congratulations on resolving the problem.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tina t
post Feb 17 2019, 04:46 PM
Post#5



Posts: 6,150
Joined: 11-November 10
From: SoCal, USA


QUOTE
WHERE (((DateDiff('d',Date(),DateSerial(Year(Date()),Month([ContactDOB]),Day([ContactDOB]))))<=7))

this works now, hon, on Feb 17, but it's not going to work right between 12/25 and 12/31. on 12/25/19, a DOB of 1/1 will be parsed out as 1/1/19. so it won't be returned by the query, even though the person's next birthday is 1/1/20 - which is 7 days in the future of 12/25/19. ditto through 12/31 of the current year.

every year, in the last 7 days of the year, you're going to miss the persons with DOBs that fall in the next year, within the 7 future days timeframe. that's what i was telling you in your other post.

hth
tina
This post has been edited by tina t: Feb 17 2019, 04:46 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Feb 17 2019, 06:02 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,520
Joined: 19-June 07
From: SunnySandyEggo


Hi. Check out the link I posted in this other thread.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
slavescu
post Feb 18 2019, 03:34 PM
Post#7



Posts: 11
Joined: 17-February 19



True hon, but this is the corrected version and i checked it into 1/1/2020 and it works as it should

SELECT customers.ContactFirstName, customers.ContactLastName, customers.ContactDOB, DateDiff("yyyy",[ContactDOB],DateNextBirthday([ContactDOB])) AS AGE, customers.patEmail, DateSerial(Year(Date())+IIf(Format(Date(),"mmdd")>Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day]) AS NextDOB, Month([ContactDOB]) AS [Month], Day([ContactDOB]) AS [Day]
FROM customers;
This post has been edited by slavescu: Feb 18 2019, 03:36 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 06:49 AM