[X]My Assistant

[lord_kaiser]

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

[June7]

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.

[lord_kaiser]

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

[June7]

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

[projecttoday]

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

[MadPiet]

Couldn't you just create a date for March 1 of any year and subtract a day?

[June7]

Of course, so obvious.

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

[projecttoday]

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.

[June7]

Yep, even simpler.

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

I also would like to know purpose for this.

[GroverParkGeorge]

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

[lord_kaiser]

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

[lord_kaiser]

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?

[June7]

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.

[projecttoday]

DateField must be a date/time type field.

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

[RJD]

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)

 DatesBackwards.zip ( 19.47K )Number of downloads: 3

 

[lord_kaiser]

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!!

[RJD]

You are very welcome. Glad that worked for you.

Continued success with your project.

Regards,
Joe

[June7]

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")