Date calculation using Iff statement in update query????

[stevenmcgraw via AccessMonster.com]

Good evening all,

I am currently a novice at access but have high hopes of becomeing much
better. As it stands I have a problem that I need some expertice in. I have
a database built with dates built in that we input manualy using a form and
such. Needless to say I am trying to learn how to do a calculation with in
an update query that will read a code either 5, 6, or 7 in one column from my
table that would effect whether it will add 5 years or 10 years to the date
from another column in my table then update the calculated date into a blank
field for each record. The calculation i have attempted to use but failed
misserably at is :

IIf(([Permanent Party Personnel]![SAR Code]="5"),DateAdd("d",1826.5,
[Permanent Party Personnel]![Invest Date]),DateAdd("d",3652.5,[Permanent
Party Personnel]![Invest Date]))

Any feedback would be most helpfull.

Thanks in advance,

 

[Graham Mandeno]

Hi Steven

If you want to add years, you should add years, not days.

Try this:

DateAdd( "yyyy", IIf([SAR Code]="5", 5, 10), [Invest Date])

 

[Dale Fye]

Steven,

How did you fail miserably? Did you get an error message, or did it not
update the way you expected?

You mention [SAR Code] of 5, 6, or 7 in your message, but your IIF statement
only addresses 5 or anything else. Did you really want to test for values of
6 and 7 as well?

I'm also not sure why you are adding days when you obviously want years, in
the dateadd function Dateadd("yyyy", #, [Invest Date])

So, you want to update a blank field in your table, based on the values of
two other fields in the same table. Is that right? Most purists would ask
you why you want to do this, since the 2nd date field can always be
calculated in a query, whenever you need it, based on the value of the
original two fields. And, if someone inadvertantly changes the values in one
of these other two fields, but forgets to run the update query, then the
value in your third field (2nd date field) will be incorrect.

Having said that, if you want to update a field based on two other fields,
the query might look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = DateAdd("yyyy", IIF([Sar Code] = 5, 5, 10), [Invest
Date])

You could do it the way you were going, but it is cleaner this way. Your
way would look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = IIF([SAR Code] = 5,
DateAdd("yyyy", 5, [Invest Date]),
DateAdd("yyyy", 10, [Invest Date]))


This would update the [BlankFieldName] field (you didn't give us a name for
the new date field) to a date 5 or 10 years from the value in the [Invest
Date] field. You might also want to put a where clause in so that it doesn't
attempt do do this for fields where [Invest Date] is null, or where the new
field has already been filled in.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Good evening all,

I am currently a novice at access but have high hopes of becomeing much
better. As it stands I have a problem that I need some expertice in. I have
a database built with dates built in that we input manualy using a form and
such. Needless to say I am trying to learn how to do a calculation with in
an update query that will read a code either 5, 6, or 7 in one column from my
table that would effect whether it will add 5 years or 10 years to the date
from another column in my table then update the calculated date into a blank
field for each record. The calculation i have attempted to use but failed
misserably at is :

IIf(([Permanent Party Personnel]![SAR Code]="5"),DateAdd("d",1826.5,
[Permanent Party Personnel]![Invest Date]),DateAdd("d",3652.5,[Permanent
Party Personnel]![Invest Date]))

Any feedback would be most helpfull.

Thanks in advance,

 

[stevenmcgraw via AccessMonster.com]

I will certainly give your suggestion a try. The method that I used in my
first post was the direction that I wanted to go but it would not push a
calculated date to the new field so it wasn’t a complete failure just
ignorance in my level of knowledge. To answer your question on the sar code,
the dates added differ only for 5’s and everything else that falls into that
column is either 6 or 7 which use the same calculation. These are the only
“codes†among other data that we pull from an alternate source to update our
personell database. As for the dateadd method I am adding days because on a
subsequent calculation when I figure this one out is to add essentially 10
years or 5 years minus 7 months in order to know when to start the
investigation. I will certainly use your calculation in the “over due
column†for those individuals. Now to answer a subsequent question as to
why not have a calculation with in a field though a good idea in theory I
have run into many problems with running reports and having wrong dates or
calculations come out in the report. Also with the amount calculating power
that is required for some things I didn’t want to run the risk of Access
blowing a fuse. Its not quite as robust as Oracle.

Thanks for the quick replys and I will get back to let everyone know how it
went.

Thanks

Steven,

How did you fail miserably? Did you get an error message, or did it not
update the way you expected?

You mention [SAR Code] of 5, 6, or 7 in your message, but your IIF statement
only addresses 5 or anything else. Did you really want to test for values of
6 and 7 as well?

I'm also not sure why you are adding days when you obviously want years, in
the dateadd function Dateadd("yyyy", #, [Invest Date])

So, you want to update a blank field in your table, based on the values of
two other fields in the same table. Is that right? Most purists would ask
you why you want to do this, since the 2nd date field can always be
calculated in a query, whenever you need it, based on the value of the
original two fields. And, if someone inadvertantly changes the values in one
of these other two fields, but forgets to run the update query, then the
value in your third field (2nd date field) will be incorrect.

Having said that, if you want to update a field based on two other fields,
the query might look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = DateAdd("yyyy", IIF([Sar Code] = 5, 5, 10), [Invest
Date])

You could do it the way you were going, but it is cleaner this way. Your
way would look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = IIF([SAR Code] = 5,
DateAdd("yyyy", 5, [Invest Date]),
DateAdd("yyyy", 10, [Invest Date]))

This would update the [BlankFieldName] field (you didn't give us a name for
the new date field) to a date 5 or 10 years from the value in the [Invest
Date] field. You might also want to put a where clause in so that it doesn't
attempt do do this for fields where [Invest Date] is null, or where the new
field has already been filled in.

Good evening all,

[quoted text clipped - 15 lines]

Thanks in advance,

 

[Dale Fye]

Looks like you are working with security clearances. Can you actually get
one completed in the 7 months you indicate? Last time I renewed mine, it
took about a year from the date I submitted it 'till it came back approved.

You are correct, Access is not quite as robust as Oracle, but depending on
how you are using it (stand-alone, networked) most simple date calculations
will be extremely quick.

You could also use:

StartInvestateAdd("m", -7, DateAdd("yyyy", iif([SAR Code] = 5, 5, 10),
[Invest Date]))

This is very clean and elegant, and says subtract 7 months from 5 or 10
years from the current investigation date.


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I will certainly give your suggestion a try. The method that I used in my
first post was the direction that I wanted to go but it would not push a
calculated date to the new field so it wasn’t a complete failure just
ignorance in my level of knowledge. To answer your question on the sar code,
the dates added differ only for 5’s and everything else that falls into that
column is either 6 or 7 which use the same calculation. These are the only
“codes†among other data that we pull from an alternate source to update our
personell database. As for the dateadd method I am adding days because on a
subsequent calculation when I figure this one out is to add essentially 10
years or 5 years minus 7 months in order to know when to start the
investigation. I will certainly use your calculation in the “over due
column†for those individuals. Now to answer a subsequent question as to
why not have a calculation with in a field though a good idea in theory I
have run into many problems with running reports and having wrong dates or
calculations come out in the report. Also with the amount calculating power
that is required for some things I didn’t want to run the risk of Access
blowing a fuse. Its not quite as robust as Oracle.

Thanks for the quick replys and I will get back to let everyone know how it
went.

Thanks

Steven,

How did you fail miserably? Did you get an error message, or did it not
update the way you expected?

You mention [SAR Code] of 5, 6, or 7 in your message, but your IIF statement
only addresses 5 or anything else. Did you really want to test for values of
6 and 7 as well?

I'm also not sure why you are adding days when you obviously want years, in
the dateadd function Dateadd("yyyy", #, [Invest Date])

So, you want to update a blank field in your table, based on the values of
two other fields in the same table. Is that right? Most purists would ask
you why you want to do this, since the 2nd date field can always be
calculated in a query, whenever you need it, based on the value of the
original two fields. And, if someone inadvertantly changes the values in one
of these other two fields, but forgets to run the update query, then the
value in your third field (2nd date field) will be incorrect.

Having said that, if you want to update a field based on two other fields,
the query might look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = DateAdd("yyyy", IIF([Sar Code] = 5, 5, 10), [Invest
Date])

You could do it the way you were going, but it is cleaner this way. Your
way would look something like:

UPDATE [Permanent Party Personnel]
SET [BlankFieldName] = IIF([SAR Code] = 5,
DateAdd("yyyy", 5, [Invest Date]),
DateAdd("yyyy", 10, [Invest Date]))

This would update the [BlankFieldName] field (you didn't give us a name for
the new date field) to a date 5 or 10 years from the value in the [Invest
Date] field. You might also want to put a where clause in so that it doesn't
attempt do do this for fields where [Invest Date] is null, or where the new
field has already been filled in.

Good evening all,

[quoted text clipped - 15 lines]

Thanks in advance,

 

[stevenmcgraw via AccessMonster.com]

Well depending on the clearence and what the person has done in the past 10
or 7 years. But true story we had a gentleman that has been here at DM for
the past 5 years and didnt move squadrons, addresses, or even go tdy to any
foreign soil. His was TS was completed in 3 months and adjudicated in 6 so
it happens on occasion though rare at best.

I am attempting to use your example but am unclear how to put this in to the
query. Is this a module code for VB or am I simply ignorant as to the proper
syntax?

Thanks

Looks like you are working with security clearances. Can you actually get
one completed in the 7 months you indicate? Last time I renewed mine, it
took about a year from the date I submitted it 'till it came back approved.

You are correct, Access is not quite as robust as Oracle, but depending on
how you are using it (stand-alone, networked) most simple date calculations
will be extremely quick.

You could also use:

StartInvestateAdd("m", -7, DateAdd("yyyy", iif([SAR Code] = 5, 5, 10),
[Invest Date]))

This is very clean and elegant, and says subtract 7 months from 5 or 10
years from the current investigation date.

I will certainly give your suggestion a try. The method that I used in my
first post was the direction that I wanted to go but it would not push a

[quoted text clipped - 65 lines]

 

[Dale Fye]

Steve,

My reply assumed you had a query that you would use, and that example would
be listed as a computed column in the query.

Another way to use this would be as the control source in an unbound text
box on a form. So, when you pulled up an employees record, it would compute
the StartInvestDate and display it in the textbox. In that case, it would
look like:

ControlSource: = DateAdd("m", -7, DateAdd("yyyy", iif([SAR Code] = 5, 5,
10), [Invest Date]))


HTH
Dale

Well depending on the clearence and what the person has done in the past
10
or 7 years. But true story we had a gentleman that has been here at DM
for
the past 5 years and didnt move squadrons, addresses, or even go tdy to
any
foreign soil. His was TS was completed in 3 months and adjudicated in 6
so
it happens on occasion though rare at best.

I am attempting to use your example but am unclear how to put this in to
the
query. Is this a module code for VB or am I simply ignorant as to the
proper
syntax?

Thanks

Looks like you are working with security clearances. Can you actually get
one completed in the 7 months you indicate? Last time I renewed mine, it
took about a year from the date I submitted it 'till it came back
approved.

You are correct, Access is not quite as robust as Oracle, but depending on
how you are using it (stand-alone, networked) most simple date
calculations
will be extremely quick.

You could also use:

StartInvestateAdd("m", -7, DateAdd("yyyy", iif([SAR Code] = 5, 5, 10),
[Invest Date]))

This is very clean and elegant, and says subtract 7 months from 5 or 10
years from the current investigation date.

I will certainly give your suggestion a try. The method that I used in
my
first post was the direction that I wanted to go but it would not push a

[quoted text clipped - 65 lines]

Thanks in advance,