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
> Dates Within 6 Months, Access 2016    
 
   
mike60smart
post Oct 18 2019, 02:21 PM
Post#1


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

How can I only show records with a Renewal date within 6 Months of Today's Date ??

Any help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
RJD
post Oct 18 2019, 02:28 PM
Post#2


UtterAccess VIP
Posts: 10,150
Joined: 25-October 10
From: Gulf South USA


Hi Mike: I would think a WHERE clause something like ...

WHERE Renewal_date <= DateAdd("m",6,Date())

(untested)

I am assuming you are looking forward to future renewal dates, but also grabbing any past due renewal dates.

If you want ONLY renewals due from today to 6 months out, then perhaps ...

WHERE Renewal_date Between Date() and DateAdd("m",6,Date())

(again, untested)

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mike60smart
post Oct 18 2019, 02:42 PM
Post#3


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Joe

I am trying this in a query:-

Reminders: [Renewal Date] Between Date() And DateAdd("m",6,Date())

It is returning a "-1" ??

Is this because "Renewal Date" is calculated as follows in the query:-

Renewal Date: DateAdd("yyyy",[RenewalPeriod],[TrainingDate])

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Oct 18 2019, 02:56 PM
Post#4


UA Moderator
Posts: 76,867
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike,

When you do this:
CODE
Reminders: [Renewal Date] Between Date() And DateAdd("m",6,Date())


You're basically creating a calculated column asking if the [Renewal Date] is Between today and 6 months from now, which would be either a Yes or a No. So, some records in that column should display a -1 and some should display a 0.

There is nothing wrong with this, and if you want to filter the query to those records where renewal dates are coming up, then you just add a criteria to the Reminders column of -1.


--------------------
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
 
RJD
post Oct 18 2019, 02:57 PM
Post#5


UtterAccess VIP
Posts: 10,150
Joined: 25-October 10
From: Gulf South USA


Hi Mike: The Between statement should be in the criteria, in the [Renewal Date] field (column) in Design View.

Between Date() and DateAdd("m",6,Date())

Try that and see if it works for you.

As theDBguy says, the -1 just means True.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mike60smart
post Oct 18 2019, 03:00 PM
Post#6


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi theDBGuy and Joe

theDBGuy many thanks for the explanation. I was puzzled as it would not let me put a criteria of -1

Joe - Perfect that is exactly what I need

Many thanks yet again
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
RJD
post Oct 18 2019, 03:07 PM
Post#7


UtterAccess VIP
Posts: 10,150
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Mike, from both of us.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 01:03 PM