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
> Working With Dates Backwards From A Date, Access 2016    
 
   
lord_kaiser
post Nov 11 2019, 11:45 PM
Post#1



Posts: 143
Joined: 8-August 17
From: Australia


Hi Everyone,

I'm just looking to get some help on a little problem i have.

I have an end date 29/02/2020

What i need to do is find the last 12 months date for the past 5 years.
Row 1: 28/02/2019
Row 2:28/02/2018
Row 3:28/02/2017
Row 4:28/02/2016
Row 5:28/02/2015

Is there an easy way to generate a table based on a query such as this?

Thanks

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
June7
post Nov 12 2019, 12:24 AM
Post#2



Posts: 1,019
Joined: 25-January 16



If that is a sample of desired output, I don't see 12 months of dates.

If you want a table of dates, suggest using VBA to create records.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
lord_kaiser
post Nov 12 2019, 12:35 AM
Post#3



Posts: 143
Joined: 8-August 17
From: Australia


I didn't explain myself clearly so here goes:

I'm just looking to get some help on a little problem i have.

I have an end date 29/02/2020

What i need to do is find the last date of the same month (From the end date above) for the past 5 years.

Row 1: 28/02/2019
Row 2:28/02/2018
Row 3:28/02/2017
Row 4:29/02/2016
Row 5:28/02/2015

Is there an easy way to generate a table based on a query such as this?

Thanks

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
June7
post Nov 12 2019, 01:33 AM
Post#4



Posts: 1,019
Joined: 25-January 16



Oh, you are trying to figure out last date for February because of leap year.

I think this will require a VBA custom procedure writing records to a table. Unless you already have a table of records with a date field that can be used as source for the years.

SELECT DISTINCT CDate(IIf(IsDate("2/29/" & Year([DateField])),"2/29/" & Year([DateField]),"2/28/" & Year([DateField]))) AS EndFeb FROM table;

This post has been edited by June7: Nov 12 2019, 01:37 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Nov 12 2019, 01:36 AM
Post#5


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


For all the other months the value is fixed. What do you need the code for?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Nov 12 2019, 02:15 AM
Post#6



Posts: 3,366
Joined: 27-February 09



Couldn't you just create a date for March 1 of any year and subtract a day?
Go to the top of the page
 
June7
post Nov 12 2019, 02:23 AM
Post#7



Posts: 1,019
Joined: 25-January 16



Of course, so obvious.

SELECT DISTINCT CDate("3/1/" & Year([DateField]))-1 AS EndFeb FROM table;

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Nov 12 2019, 02:30 AM
Post#8


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


You can use DateSerial with 0 for the day to get the last date of the previous month. But I'd still like to know why this is necessary if you have already figured out the dates.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Nov 12 2019, 03:03 AM
Post#9



Posts: 1,019
Joined: 25-January 16



Yep, even simpler.

SELECT DISTINCT DateSerial(Year(DateField),3,0) AS EndFeb FROM table;

I also would like to know purpose for this.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Nov 12 2019, 08:30 AM
Post#10


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


I also see no reason to create a TABLE with these dates. But, again, knowing the business rules that govern it should clarify things.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lord_kaiser
post Nov 12 2019, 10:40 PM
Post#11



Posts: 143
Joined: 8-August 17
From: Australia


Thanks for the feedback everyone.

The reason i want a table is I am trying to establish the services delivered/not delivered within a specific time frame.

Unfortunately the database that i can extract from only has the current date (In this case Feb 2020)

These rows don't exist (I only have the last date) so looking to have these rows append based on my criteria above:

Row 1: 28/02/2019
Row 2:28/02/2018
Row 3:28/02/2017
Row 4:29/02/2016
Row 5:28/02/2015


I know how long the client was a client of the business (In this case 5 years) So rather than having to manually look through records, i have date stamps of when the services were delivered but need to know in which period. Hence my question working backwards from the date for 5 years.

If there is a simple solution, I'm all ears!! Simple is beautiful!!

I'll give your recommendations a shot and keep you posted if any issues.

Thanks
This post has been edited by lord_kaiser: Nov 12 2019, 11:05 PM

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
lord_kaiser
post Nov 12 2019, 10:54 PM
Post#12



Posts: 143
Joined: 8-August 17
From: Australia


I'm trying to use this query:

SELECT DISTINCT DateSerial(Year(DateField),3,0) AS EndFeb FROM table;

but keep getting a data mismatch error. Is the above for text?

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
June7
post Nov 12 2019, 11:21 PM
Post#13



Posts: 1,019
Joined: 25-January 16



What are your table and field names?

DateField must be a date/time type field.

If you don't have a table with a date/time field then this suggestion will not work.

If you need to add records to table, that will require a VBA procedure.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Nov 13 2019, 07:47 AM
Post#14


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


QUOTE
DateField must be a date/time type field.


This is not correct. Year() will work with a string. Or a numerical value.

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Nov 13 2019, 06:52 PM
Post#15


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


Hi PMFJI. I have been looking in on this thread since it first appeared - and still don't think I seem to see a solution posted that creates all the previous years' dates. So I thought I'd give it a shot.

You indicated 5 years back - assuming that as a standard. I also assumed that you had many dates (clients?) to calculate from (otherwise, why bother - just create the 5 new records and be done). And I assumed that you wanted the created dates to be the end of the month. So the attached demo addresses all that. It includes a utility table with sequences 1 through 5 and a Cartesian product to create the extra records with a query, rather than with VBA.

However, the way you talked about 5 years caused me to wonder if it could be any number of years, based on the client. If it could vary from 5, then you must have something to indicate how many years by client, and a number table to accommodate the maximum, not just 5. The same approach can be used as in the demo, just limited to the client indicated years.

Anyway, this is just a start, in case you are still dealing with this.

The query can be turned into an append query, with other necessary fields included, if any, to flesh out the table with all the values you want.

HTH
Joe
Attached File(s)
Attached File  DatesBackwards.zip ( 19.47K )Number of downloads: 3
 

--------------------
"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
 
lord_kaiser
post Nov 17 2019, 05:25 PM
Post#16



Posts: 143
Joined: 8-August 17
From: Australia


Hi RJD,

Apologies for the radio silence as I was away.

Your answers to my question was spot on!!

I looked through the sample database you provided and it is exactly what i am after!!

Thank you so much!!


--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
RJD
post Nov 17 2019, 05:46 PM
Post#17


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


You are very welcome. Glad that worked for you.

Continued success with your project.

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
 
June7
post Nov 17 2019, 06:13 PM
Post#18



Posts: 1,019
Joined: 25-January 16



QUOTE
This is not correct. Year() will work with a string. Or a numerical value.
Okay, got it. I just tested following which work:

Year("Mar 2020")
Year("3 2020")
Year("3 30 2020")
Year("3/30/2020")
Year("2020/3/30")
Year("2020 3 30")
Year(43920)

The following does not work.

Year("2020.3.30")
Year("3.30.2020")


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 10:15 PM