DateADD Question

[jeepjenn]

I have a database table that contains six columns in which a date can be entered.

Depending on the state that the "document' is in, there is only 1 date in the row at a time. I am trying to figure out how to add a column that will use dateadd to add 2 years to the one date that exists in one of



the six cells on that "document" record and show it in the new column.

Basically, these are documents that have been "retired", "closed", "withdrawn", "dismissed", 'failed" or are "successful". After two years, I can dispose of them.

I want to be able to run a simple query to know which ones I can remove from the files and destroy.


Is there a better way or am I going at this completely wrong??

[ranman256]

[dateField2] = dateadd("y",2,[Datefield1])

set them in an update query.

[CJ_London]

Your table would be better structured with a status field and a date field, then it would be easy. However there is no need to add a calculated field just use in your query

SELECT *
FROM myTable
WHERE nz(retired,nz(closed,nz(withdrawn,nz(dismissed,nz(failed,nz(successful)))))<dateadd("yyyy",-2,date())

[jeepjenn]

Ok, so from what I understand that adds the 2 years to the 'one specific date' from the 'one specific cell' I choose to include in the query....but how do I have the query look at all six columns and choose (for the lack of a better work) the cell that actually has the date in it? There is only one date...but it could be in any of the 6 columns...

[jeepjenn]

Thanks Ajax...I'm going to try that now!!

[jeepjenn]

Which part is the subquery? I'm getting an error to check the subquery's syntax and enclose the subquery in parentheses...

[CJ_London]

it's the whole thing, no subquery - substitute the name of your table for 'mytable' and the names of the various date columns with what you have called them.

Just noticed the should be six ) after 'successful', there are only five in my example. If your field names have spaces or non alphanumeric characters, you must enclosed them with square brackets. Your field names should also not be reserved words, which as described, they are not, but if different then double check - google 'access reserved words' to find a full list

[jeepjenn]

Sigh...I just don't know what I'm doing wrong...

[CJ_London]

OK, provide the exact name of your table and the exact names of the six fields with the dates

[jeepjenn]

SELECT *
FROM myTable
WHERE nz(retired,nz(closed,nz(withdrawn,nz(dismissed,nz(failed,nz(successful)))))<dateadd("yyyy",-2,date())

[/QUOTE]


Ok,...

my tables name is: "All Grievances"

The columns that the date could be in are titled: "Successful", "Settled", "Failed", "Withdrawn", "Rejected", or "Closed"

There is often more than one date in the record, so I go through and delete the older dates so I only have the latest update for what is happening with the document.

Whatever is the date shown (most recent date usually), I want the Access Database to add 2 years to that date (which then becomes the date I can destroy the documents)

Basically this creates a list from the database of when I can destroy grievance files.

[CJ_London]

If the names you provided are correct then you just be able to copy and paste this query into the sql window

SELECT *
FROM [All Grievances]
WHERE nz(Successful,nz(Settled,nz(Failed,nz(Withdrawn,nz(Rejected,nz(Closed))))))<dateadd("yyyy",-2,date())

You originally said

Depending on the state that the "document' is in, there is only 1 date in the row at a time

now you are saying

There is often more than one date in the record, so I go through and delete the older dates so I only have the latest update for what is happening with the document.

so make sure you have deleted all the older dates before running the query.

you originally said

I want to be able to run a simple query to know which ones I can remove from the files and destroy.

which is what the query does. You are now saying

I want the Access Database to add 2 years to that date (which then becomes the date I can destroy the documents)

which is it? The code for your second requirement would be

SELECT *, dateadd("yyyy",2,nz(Successful,nz(Settled,nz(Failed,nz(Withdrawn,nz(Rejected,nz(Closed))))))) as DateForDestruction
FROM [All Grievances]

[jeepjenn]

Sorry Ajax, I thought I was making it more simple by letting you know what I was doing to the data in the first place...not trying to make it more complicated.

There is only one date if I have gone through and deleted all the others...otherwise this is a database to track the various stages, and they do have multiple dates.

I will continue to try to figure out what I'm doing using your suggestions...

I must be doing something wrong, and I was hoping to learn what that was, but I don't want to take up anymore of your time.

Thanks...

[CJ_London]

OK - so what happens when you paste either query into a new query and run it? Do you get an error? a wrong result?

[jeepjenn]

I get the following error:

[rpeare]

I'm just going to add something here, it seems to me you're just trying to identify the 'right' date to perform your calculation on so try something simple first like:

TestDate: iif(not isnull([closed]), [closed], iif(not isnull([rejected]), [rejected], iif(not isnull([withdrawn]), [withdrawn], iif(not isnull([failed]), [failed], iif(not isnull([settled]), [setteled], iif(not isnull([successful]), [successful], null))))))

if this gets you the correct date then you can perform your calculation on the result. Just bear in mind the formula above should be in order of importance, so the first if (in this case closed) assumes that if there is a date in the 'closed' field it takes priority over everything else.

so your formula to add two years to your properly chosen date would be

NewTestDate = dateadd("yyyy", 2, TestDate)

or

NewTestDate = dateadd("yyyy", 2,iif(not isnull([closed]), [closed], iif(not isnull([rejected]), [rejected], iif(not isnull([withdrawn]), [withdrawn], iif(not isnull([failed]), [failed], iif(not isnull([settled]), [setteled], iif(not isnull([successful]), [successful], null)))))))

All that being said, I would tend to agree with ajax in storing your dates in a normalized structure, if a particular record can have multiple dates associated with it you could have a subtable with the FK (foreign key) to the main record, the date associated with the status, and the status (settled, rejected, etc.)