Filtering Retirement Dates - Causing Problem

PKJ · Apr 22, 2010

Hi EveryBody!

I am struck in a query that would return records of employees who are retiring

i On a given Date
ii Before a given Date
iii Between two given dates.

The Structure of the the input table is as below ( I have simplified it for the purpose in hand, actually it is a query based on three other subqueries in my database) -

employees_table :
EmpCode
Name
DateOfBirth
The rules of calculating a Retirement Date are -

- An employee would retire on last day of the month of his birth-date after 60 years if day of birth is greater than 1

Else he would retire on last day of the month preceeding the month of his birth-date.

Example for DOB: 25th April, 2000 RetirementDate is 30th April 2060

for DOB: 1st Jan, 2001 RetirementDate is 31st Dec. 2060

I am using the following VBA function for calculating Retiring Dates -

Code:

Function RetiringDate(DOB As Date) As Date
Dim dob_day, dob_mnth, dob_yr, ret_day, ret_mnth, ret_yr As Long

dob_day = Day(DOB)
dob_mnth = Month(DOB)
dob_yr = Year(DOB)

If dob_day = 1 And dob_mnth = 1 Then
    ret_mnth = 12
    ret_yr = (dob_yr + 60) - 1
ElseIf dob_day = 1 Then
    ret_mnth = dob_mnth - 1
    ret_yr = (dob_yr + 60)
Else
    ret_mnth = dob_mnth
    ret_yr = (dob_yr + 60)
End If

If ret_mnth = 1 Or ret_mnth = 3 Or ret_mnth = 5 Or ret_mnth = 7 Or _
    ret_mnth = 8 Or ret_mnth = 10 Or ret_mnth = 12 Then
    ret_day = 31
ElseIf ret_mnth <> 2 Then
    ret_day = 30
Else
    ret_day = 28
End If

RetiringDate = CDate(ret_yr & "/" & ret_mnth & "/" & ret_day)
End Function

I have used the above function in a subquery that calculates the retiring dates for all employees - it works well, but when I try to use this subquery in another query to further filter the records by using a WHERE clause, as per three requirements stated above (one at time in a separate query), Access does not calculate it stating it to be complex and suggests to simlify it. The subquery and the main query are as under -

Code:

RetirementDates_sq:

SELECT EmpCode, Name, DateOfBirth, RetiringDate(DateOfBirth) As RetirementDate
FROM employee_table;

RetirementDates_fq:

SELECT EmpCode, Name, DateOfBirth, RetirementDate
FROM RetirementDates_sq
WHERE GivenDate = RetirementDate;

Access prompts to enter "GivenDate" which I supply to it but after a while it notifies its incapability.

Please Help.

Regards - PKJoshi.

PKJ · Apr 23, 2010

Perhaps I have not been able to express the above problem clearly - may be the reason that no one took any interest in it. If anything more is to be revealed , kindly someone guide me to express it out. I solicit help from the knowledgeable members of the forum to solve this problem. Help Please!
Regards - PKJ

raskew · Apr 23, 2010

Hi -

I don't see anything in your code, other than the following:

Code:

SELECT EmpCode, Name, DateOfBirth, RetirementDate
FROM RetirementDates_sq
WHERE [B][U]GivenDate[/U][/B] = RetirementDate;

- that in anyway mentions GivenDate.

It would be helpful if you'd post a sample db.

Best Wishes - Bob

PKJ · Apr 23, 2010

Hi Bob,
I am sorry for being poor in expressing the query code. In fact RetirementDates_sq and RetirementDates_fq are the names of the queries that I have mentioned in my problem-statement. The Query named "RetirementDates_fq" uses "RetirementDates_sq". "RetirementDates_sq" runs well but RetirementDates_fq doesn't run and Access notifies incapality to calculate on account its complexity. I can't understand what is complex in it.
The Queries are -

Query: RetirementDates_sq

Code:

SELECT EmpCode, Name, DateOfBirth, RetiringDate(DateOfBirth) As RetirementDate
FROM employee_table;

Query: RetirementDates_fq

Code:

SELECT EmpCode, Name, DateOfBirth, RetirementDate
FROM RetirementDates_sq
WHERE GivenDate = RetirementDate;

I will post a sample database as desired by you. just give some time!

Thanks a lot Dear! -PKJ

Brianwarnock · Apr 23, 2010

It seems simple enough, provided the supplied givendate is correctly entered, try using the parameter function to format it, found in the quert dropdown.

I would simplify your retiring date calculation to.

Code:

Function retdate(dob As Date) As Date
If Day(dob) = 1 Then
retdate = DateSerial(Year(dob) + 60, Month(dob), 0)
Else
retdate = DateSerial(Year(dob) + 60, Month(dob) + 1, 0)
End If

End Function

Brian

BTW this
WHERE GivenDate = RetirementDate
is more normally written as below, the above creates an extra column in the design grid
WHERE RetirementDate = GivenDate

PKJ · Apr 23, 2010

Thanks Brianwarnock for the elegant RetDate function which is very instructive for me.

raskew wrote:

Hi -

I don't see anything in your code, other than the following:

Code:
SELECT EmpCode, Name, DateOfBirth, RetirementDate
FROM RetirementDates_sq
WHERE GivenDate = RetirementDate;

- that in anyway mentions GivenDate.

It would be helpful if you'd post a sample db.

Best Wishes - Bob

I have attach the database after deleting un-required objects for our present purpose - though required queries have been retained. In this database named Employees.accdb - the query RetirementDates_fq is not running. Please supply date in mm/dd/yyyy format when prompted by Access to enter "GivenDate" field as I have used a custom function named "ConvertDateStringToDate" which requires it - however you may not need to read that function.
Please forgive any dirty-ness of code as I am a greenhorn in Proramming field.

Thanks and regards.
-PKJ

The attached/uploaded file is not appearing in the post. I am retrying.

Brianwarnock · Apr 23, 2010

The attachment didn't show.
I notice that it is a 2007 db, I and many others cannot access 2007 dbs can you save it in 2003 format.

Brian

Kryst51 · Apr 23, 2010

Brianwarnock said:
The attachment didn't show.
I notice that it is a 2007 db, I and many others cannot access 2007 dbs can you save it in 2003 format.

Brian

Also, maybe compact and repair and zip it up....

Brianwarnock · Apr 23, 2010

Please supply date in mm/dd/yyyy format when prompted by Access to enter "GivenDate" field as I have used a custom function named "ConvertDateStringToDate" which requires it - however you may not need to read that function.

I'm just wondering if this is where your problem is.
Why not just take the input in date format? The Parameter option I mentioned earlier can ensure that.
Plus you are aware that SQL and VBA assume dates to be US format, they handle UK date fields ok , but if dates are hardcoded or referenced anyway than as a fieldname they need to be in US format, It is a bit of a minefield.

Brian

PKJ · Apr 23, 2010

I tried saving the database in .mdb format but the queries and VBA module disappeared. So I have saved it in a ZIP folder and am attaching again.

PKJ · Apr 23, 2010

Brian said-

I'm just wondering if this is where your problem is.
Why not just take the input in date format? The Parameter option I mentioned earlier can ensure that.
Plus you are aware that SQL and VBA assume dates to be US format, they handle UK date fields ok , but if dates are hardcoded or referenced anyway than as a fieldname they need to be in US format, It is a bit of a minefield.

Brian

Brian, I am poor in that - Parameter option - can you show an example. Thanks!

Brianwarnock · Apr 23, 2010

If you are using the Design grid click on query then Parameters and then fill in the table.
In SQL it is the first line like so

PARAMETERS givendate DateTime;

Then the entry in the pop up has to be a valid date in your system format.

Brian

Brianwarnock · Apr 23, 2010

A bit more info.hope you don't mind.

It can be better to drive parameter queries/reports via a form, the parameter(s) are entered on the form where validation can be done if necessary, and then the Report/query run by a command button, easy to do using the wizards.
The criteria is referenced in the query by
Forms!formname!controlname
eg
Forms!frmDates!dteGivenDate

An additional advantage is that the same syntax can be used to place the criteria in a Report, Oh and during testing the data doesn't have to be repeatedly entered.

Brian

PKJ · Apr 23, 2010

Brian wrote:

A bit more info.hope you don't mind.

I am rather thankful and grateful for your help. But perhaps I need to learn this all from a standard text-book on this.

Could you view the EmployeeDatabse that I attached. If your suggestion of PARAMETER option is directly related to my problem, I shall give it a try.

However I shall wait for your guidance if it can be solved alternatively.
Regards -PKJ

[Brian, I leave as it is 1:00 AM at my place (Jaipur, India) and I have an early-morning commitment]

Thanks and wish Good-time to all AWF members.

Brianwarnock · Apr 23, 2010

As I said earlier I cannot open 2007 Databases, if you can save and attach a 2003 version I will take a look, but it maybe tomorrow as I will be leaving in a short while.

Brian

PKJ · Apr 23, 2010

Ok Friend! Good Bye! I shall try to attach a .mdb file tommorrow.

boblarson · Apr 23, 2010

Here's the 2002-2003 version for Brian (I believe he doesn't have 2007).

Brianwarnock · Apr 24, 2010

Thanks for that Bob, second thoughts maybe not.
BTW I don't object to others jumping in to answer and in this case your greater expertise is needed.

PKJ

I have tried everything, using Parameter statement and even creating a form to input the criteria, plus my retirement date function but in the end no joy.
I can only get = or Between ... And... to work in the criteria , not > or < .
I've compacted and repaired, change the date format in the table and pulled out my remaining hair, but I have no idea what the problem is.

Brian

Brianwarnock · Apr 24, 2010

Had 1 last idea before I have to go.
changed retirementdates_sq to a maketable query and ran retirementdates_fq against that table and it worked so as i suspected when I changed the DOB format in the table, but it wasn't that, there is a problem somewhere back up the chain of queries. I'll have another look sometime, but hopefully somebody will in the meantime suggest a solution other than the temporary table.

Brian

PKJ · Apr 24, 2010

Bob,
Thanks for uploading a 2002-2003 format-file.

Brian,

I have also tried your suggestion of using PARAMETERS option in this problem causing query "RetirementDates_fq".

Code:

PARAMETERS GivenDate DateTime;
SELECT *
FROM RetirementDates_sq
WHERE RetDate<GivenDate;

It is definetley of great use for me in my current project. It helped in dropping out the unneccessary use of the custom function for date-conversion that I was using but the original problem still persists. The query does not run and Access notifies the following message -

This expression is typed incorrectly, or or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the variables to variables.

Maybe your efforts could not solve the problem - I remain thankful to you. I am also in hope that some other expert on the forum will be able to solve this.

How to adopt your aproach of creating temporary table - did you do this using VBA? Can I rely that as workable solution till some concrete-solution is worked-out? Please guide.

I reaffirm my faith in AWF and solicit help.

Regards - PKJ

Filtering Retirement Dates - Causing Problem

Registered User.

Registered User.

AWF VIP

Registered User.

Retired

Registered User.

Retired

Singin' in the Hou. Rain

Retired

Registered User.

Attachments

Registered User.

Retired

Retired

Registered User.

Retired

Registered User.

Smeghead

Attachments

Retired

Retired

Registered User.

Similar threads

Users who are viewing this thread