Calculating Retirement Date from DOB in an Employee Table

[nityanda10]

If DOB is 01.04.1960, the DOR (date of retirement) would be 31.03.2020.
Else, if DOB is 02.04.1960 or any other date in the month, the DOR would be 30.04.2020.
(Retirement principle= DOB+60 years)

[zmbd]

This is simple enough that the DateAdd Function should do the trick.

DateAdd ( interval, number, date )

One thing to note: Internally Access handles dates using the USA format #MM/DD/YYYY# especially in VBA. Usually, both the SQL and the VBA engines deal with the international formats correctly; however, if you have issues with the function returning the correct date then you might try explicitly setting the date in the USA format (note the "#" which indicates a date literal):
DateAdd("Y",60, #04/01/1960#)

[PhilOfWalton]

Don't think it is as simple as that.

Without my usual rant about the total illogicality of the US date format, and the somewhat less illogicality of the UK date format, I am at a bit of a loss to understand the logic of Nityanda's question.

He or she seems to say that if the DOB was the first of the month, the DOR is DOB + 59 years + 361, 362, 363, 364 days (Depending on the month) to take you to the last date in the previous month.

On the other hand, if the DOB is after the first of the month, the DOR is DOB + 60 years + up to 29 days (depending on how many days in the month) to take you to the end of the month.

That really seems illogical. Can the OP confirm this is correct before giving a solution.

Phil

[NeoPa]

ZMBD:
One thing to note: Internally Access handles dates using the USA format #MM/DD/YYYY# especially in VBA.

Back to school with you. That's just wrong. Only date strings in SQL could be considered to behave that way, but even then that isn't 100% as that's only one of the valid formats for a SQL date string. See Literal DateTimes and Their Delimiters (#).

VBA, and the data within the database itself, stores dates internally as numbers. These numbers typically reflect the number of days (and parts thereof for times) from a known starting date. In MS Office that date is 30 december 1899.

[NeoPa]

You're quite right Phil. That seems to be the question. I guess from the name we can assume that expressing that clearly in English may be even more difficult than it would for someone for whom English is their first language, and let's face it many of those would struggle too.

So, assuming that any day except the first of the month ends up as the end of the same month, but when it's the first they squeak into the previous month. The logic to follow would be :

1. Take off one day from the original [DOB].
2. Format the result into month & year (mmm yyyy).
3. Convert this string back into date format. With no day specified it will always assume the first of the month.
4. The result is now the start of the correct month so we need to (A) add a month plus sixty years then (B) subtract a day.
   1. Adding a month can also include adding the sixty years if we do it by months. 60 x 12 = 720. We add 721.
   2. Subtract a day again.

In SQL that would be something like :

DateAdd('d',-1,DateAdd('m',721,CDate(Format(DateAdd('d',-1,[DOB]),'mmm yyyy')))) AS [DOR]

[PhilOfWalton]

@ Neopa

Certainly the SQL works perfectly when converted to VBA and changing all the single quote to double quotes.

It also gives the correct UK dates in SQL

Phil

[NeoPa]

Yes Phil.

SQL & VBA aren't entirely dissimilar. Certainly the quotes used for VBA are different from those recommended for SQL.

Bear in mind the expression is mostly based on functions and their returned values.

[twinnyfo]

NeoPa,

I like your very logical approach to this problem, and (yet again) a one-liner solution to the problem. I actually come across date calculations similar to this from time to time at work—I just slogged through it. Now I have a more logical and elegant approach.

Thanks.

[zmbd]

ZMBD:
One thing to note: Internally Access handles dates using the USA format #MM/DD/YYYY# especially in VBA.

NeoPa: Back to school with you. That's just wrong. Only date strings in SQL could be considered to behave that way, but even then that isn't 100% as that's only one of the valid formats for a SQL date string. See Literal DateTimes and Their Delimiters (#).

VBA, and the data within the database itself, stores dates internally as numbers. These numbers typically reflect the number of days (and parts thereof for times) from a known starting date. In MS Office that date is 30 december 1899.

Neopa - you know I have a great respect for you - so so much you have taught me and yet so much more to teach me!
Your solution, as usual, is quite elegant in design and application. You caught OP's intent that I allowed my chemistry background to mislead me from as I will explain later ( I need to start re-reading these posts two or three times! )
> Yes, as you well know, I am aware of how Access and Excel actually store numbers, as a double (dateserial) of the elapsed time since 1900-01-01 (or is that 1899-12-31, or 1899-12-30 (and Lotus' opps with leap years!) we just discussed that in another thread and I've ran across that same information again last week - funny how things run in cycles like that )

> Reading thru OP's post I noticed that the dates were in DD.MM.YYYY format - early on, as I live in the USA, International dates were often a thorn in my side (great gobbledygook - couldn't anyone agree on a global date format a century ago - would have saved us all from that that stupid Y2K mess!), and until I read Allen's website, I had no clue as to why this was so - and it is Allen's article (how long ago was that? How long has Allen been working with Access - looks like 1992 on his site - doesn't seem that long ago) which was the bases for my earlier post: Allen Browne: International Dates in Access

1. Misinterpretati on in the User Interface
Define the date format for your locale in Windows Control Panel | Regional Settings. You can therefore use your local date format when you enter dates into the user interface part of Access: tables, queries, forms, or the Criteria of Query Design View. (...)

So using the DateAdd() with a DD.MM.YYYY format in the SQL editor should be just fine with the regional formatting as set in the OS; however, that may not always be true and I've had to debug some databases I've inherited that didn't work quite as intended until I forced the format to #MM/DD/YYYY# - I've seen some weird stuff happen with VBA functions used in SQL, I think that this is due to the attempted interpretation of the date as Allen mentions in the remainder of the above paragraph and the fact that I was receiving data with dates in YMD, DMY, and MDY some with dots or hyphens others with slashes... gives me a headache revisiting that nightmare!

As for the VBA statement - again in part from Allen's article:

2. Wrong Formatting in Code In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

To demonstrate this, enter any date in the Criteria row under a date field in Query Design, and then switch to SQL View. In Query Design view, you see the date according to your local settings, but the SQL statement uses mm/dd/yyyy format.

SQL clauses are not always obvious, e.g. the Filter of a form, the WhereCondition of OpenReport, or the third argument of a domain aggregate function. Examples:
DoCmd.OpenRepor t "MyReport", acViewPreview, , "InvoiceDat e > #12/31/2000#"

Debug.Print DLookup("Studen tID", "tblStudent ", "EntryDate = #6/30/1953#")

strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.Start Date, "mm\/dd\/yyyy") & "#;"

The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes. (...)

-Whenever I know that I'll be dealing with international dates I've used the principle as given and then shown in the examples in my vba coding to enclose my dates in the "#" to ensure that there are no issues with international dates.

Speaking of principle ideas:

nityanda10
If DOB is 01.04.1960, the DOR (date of retirement) would be 31.03.2020.
Else, if DOB is 02.04.1960 or any other date in the month, the DOR would be 30.04.2020.
(Retirement principle= DOB+60 years)

My apologies for missing that you were using the last day of the Birth Month 60 years from the date of birth - Both Phil and Neopa were a bit more astute in catching that there was a glitch between the examples and your final statement.
I Highlight the last statement in your post where you state the "Principle" of the retirement is DOB+60years - this is a very specific terminology which is not in-line with the examples given - and the red herring I followed in answering your question. Perhaps my misinterpretati on of your post is due to my chemistry background; if one states that something is "the principle" upon which an methodology is built upon, then that principle becomes the guiding force behind any solution to the problem at hand - I incorrectly assumed your examples were simply mis-calculated instead of more carefully reading your examples as given - I do apologize for that error. I only point this out to help you with future questions.

PhilOfWalton:(...) Without my usual rant about the total illogicality of the US date format, and the somewhat less illogicality of the UK date format(...)

Rant away! The only logical format, IMNSHO, is YYYYMMDDHHmmss. Preferably no punctuation to confuse the matter; however, if one would have to use punctuation then the underscore or hyphen so that one can use them in file names without issues as such: YYYY-MM-DD-HHmmss .

[PhilOfWalton]

@ zmbd

I agree completely the logical order is year, month day - the time bit seems reasonably universal, so does' really enter into the discussion, but I feel punctuation is necessary.
If you were to enter 2018111 without punctuation, would that be interpreted as 1st November 2018 or 11th January?

Phil

[PhilOfWalton]

Going back to the dates problem, here is a simple function.
Remember I live in the UK

Public Function ShowDate(DateIn As Date) As Date

    Debug.Print Format(DateIn, "dd mmm yyyy")
    
End Function

If I type ?ShowDate(#1/2#) in the immediate window, I get 02 Jan 2018 - wrong as far as I am concerned
If I type ?ShowDate(#1 Feb#) in the immediate window,I get 01 Feb 2018 - Correct

Now from a UK point of view, those "DateIns" are identical. So Access makes the wrong assumption that if the UK day is 12 or less that it actually the month.

I have a very involved routine that gets the correct answers but relies in the dates being entered as strings rather than dates, and uses the regional settings to get the correct values.

There must be a simpler way.

Phil

[zmbd]

PhilOfWalton
If you were to enter 2018111 without punctuation, would that be interpreted as 1st November 2018 or 11th January?

However, note that the format as given is explicitly: Four digit year, two digit month, two digit day - YYYYMMDDHHmmss; therefor, 2018111 would be malformed and the value would be rejected as a valid date. The value would have to be entered as either 20181101 for November 1, 2018 or as 20180111 for January 11, 2018. The only reason I can see for using punctuation is as a visual clue that one is talking about a date 2018-01-11 and not twenty-million-one-hundred-eighty-thousand-one-hundred-eleven. Herein, again, a standard delimiter such as the dash or underscore would be ideal to allow for file names without confusion with directory paths or extensions.

Going back to the dates problem, here is a simple function. (...)
If I type ?ShowDate(#1/2#) in the immediate window, I get 02 Jan 2018 - wrong as far as I am concerned
If I type ?ShowDate(#1 Feb#) in the immediate window, I get 01 Feb 2018 - Correct (...)

Allen discusses this aspect in the remainder of section 1 that I partially quoted.

(...)relies in the dates being entered as strings rather than dates(...)There must be a simpler way.

I agree there should be an easier way. When I parse external data I have some fairly lengthy code to check for things such as [data] Like "##[. , , , /, -]*" then check Left([Data],2)>=13 etc... fortunately our business model dictates two digit day (the 1st is "01" not "1"), either text month (MMM or MMMM) or two digit month, and four digit year and 24-hour clock; thus, I can reject the data file on the first record if this isn't followed - many are not so blessed. (more than one customer unhappy with me when I rejected their data - if everyone would adopt ISO 8601 - ROTFL).

[NeoPa]

ZMBD:
Neopa - you know I have a great respect for you

Hi my friend. I hope you understand that this is also true in reverse. Not simply for your understanding of the subject, but also for your volunteer efforts to help others here on Bytes.com. Make no mistake - they are considerable and extremely helpful. I hope my earlier post was seen for what it was intended - a playful slap-down when you made a rare mistake. For most others I'd have been a lot more careful how I expressed it - to avoid causing offense.

Moving back to the discussion about dates though - what I was trying to point out, and you may notice that Allen Browne (An Aussie cleric and ex-MVP, as he's now retired bless him.) also tries to make clear with his repeated use of the term "date literals" to describe strings which are expected to be interpreted as dates, as opposed to date values which are something else again, was the difference of handling between dates themselves, and those date literals that need to be interpreted into dates one way or another.

What is often confusing, especially in Access where we often have to deal with SQL strings directly, even going as far as to build up strings in VBA code that we later plan to execute as SQL commands, is that when using the QBE grid in a QueryDef the literal date strings we type in are interpreted using our local settings, yet when we look at the SQL version of that same QueryDef, the literal date string has been converted to that SQL date literal standard that matches the US date format (m\d\y).

I believe strongly that in such a complicated & confusing situation it is important that we help new users by being as accurate and specific as we can in our terminology - just as Allen Browne was always careful to do.

[NeoPa]

As far as principles go, one can refer in English to a basic principle which might be a fundamental idea that is open to specific qualifications. Unlike the usage in Chemistry circles certainly. Bear in mind also that the OP is unlikely to be a native English speaker.

So it was that I interpreted the post. The fundamental concept is [DOB] + 60 years. Fine. More specifically though, some extra rules need to be applied. In chemistry this would be included within that which is the principle. The extra rules, from my interpretation of the post - and particularly the examples - is that the [DOR] always falls at the end of a month and that, unless you're born on the first of a month - when instead you're considered to be from the month before - your [DOR] will fall at he end of the same month as your [DOB].

From experience, and a certain understanding of human nature, I've realised that most people struggle to express ideas clearly in an explanation, but they can better express them using examples. If you've ever worked on projects you'll know that in all but the rarest of cases they get thrown back after you've produced it exactly to their specification, simply because what they said never matched what they were actually thinking. Many of us now tend to rewrite what they say into a detailed specification document that they have to sign before we start the work. As those doing the work generally have much better skills at specifying requirements precisely, a great deal of misunderstandin g is avoided when they see in black and white what "what they said" exactly means.

[PaulamaG]

Thanks for sharing your experiences here. I have just got such a task from my employer, and I didn’t know how to even start, but now I understand how to do it.

(...)