Iif Query

C

caro79

I am trying to write an Iif statement where if the difference between the
regeneration data date and 1/1/2009 is less than two years, a 1 appears. If
the difference is less than 3 years a 2 is populated, if the difference is
less than 5 years a 3 is populated, if the difference is less than 10 years a
4 populates, and if the difference is greater than 10 years a 5 appears. My
formula is:

Regeneration Data Date Score: Iif("d",DateDiff([Regeneration Data Date],
[1/1/2009])< 2,"1", Iif("d", DateDiff([Regeneration Data Date] ,[/1/2009] <
3, "2", Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 5, "3",
Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 10, "4", Iif("d",
DateDiff([Regeneration Data Date] , [1/1/2009] > 10, "5")))))

Please help!
 
J

Jerry Whittle

Is [1/1/2009] a field name? If not, it will not work. Try #1/1/2009# for a
January 1st, 2009 date.

Your DateDiffs don't have an interval specified. Probably need something
like this:
DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#)

Next is your logic. Assuming that Regeneration Data Date is today, DateDiff
would produce a 0. 0 is less than 1, 3, 5, and 10. An IIf statement would try
to evaluated all of them as True. You need some Betweens in there.

Also you have < 10 and > 10. If the number is exactly 10, there will be a
problem. There could also be problems if a null value gets in there.
 
C

caro79

Jerry-
Since I know that the data will never be less than 1 year old, I changed the
formula to:
Regeneration Data Date Score: Iif("yyyy", DateDiff([Regeneration Data Date],
#1/1/2009#)< 2,"1", Iif("yyyy", DateDiff([Regeneration Data Date]
,#1/1/2009#) < 3, "2", Iif("yyyy", DateDiff([Regeneration Data Date] ,
#1/1/2009#) < 5, "3", Iif("yyyy", DateDiff([Regeneration Data Date] ,
#1/1/2009#) < 10, "4", Iif("yyyy", DateDiff([Regeneration Data Date] ,
#1/1/2009#) > 10, "5")))))

Now I get a message saying I have the wrong number of arguments- what am I
doing wrong?
I also tried:
Regeneration Data Date Score: Iif(DateDiff, "yyyy", ([Regeneration Data
Date], #1/1/2009#)< 2,"1", Iif(DateDiff, "yyyy",([Regeneration Data Date]
,#1/1/2009#) < 3, "2", Iif(DateDiff, "yyyy",([Regeneration Data Date] ,
#1/1/2009#) < 5, "3", Iif(DateDiff, "yyyy",([Regeneration Data Date] ,
#1/1/2009#) < 10, "4", Iif(DateDiff, "yyyy", ([Regeneration Data Date] ,
#1/1/2009#) > 10, "5")))))

Many thanks,
Caro

Jerry Whittle said:
Is [1/1/2009] a field name? If not, it will not work. Try #1/1/2009# for a
January 1st, 2009 date.

Your DateDiffs don't have an interval specified. Probably need something
like this:
DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#)

Next is your logic. Assuming that Regeneration Data Date is today, DateDiff
would produce a 0. 0 is less than 1, 3, 5, and 10. An IIf statement would try
to evaluated all of them as True. You need some Betweens in there.

Also you have < 10 and > 10. If the number is exactly 10, there will be a
problem. There could also be problems if a null value gets in there.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

caro79 said:
I am trying to write an Iif statement where if the difference between the
regeneration data date and 1/1/2009 is less than two years, a 1 appears. If
the difference is less than 3 years a 2 is populated, if the difference is
less than 5 years a 3 is populated, if the difference is less than 10 years a
4 populates, and if the difference is greater than 10 years a 5 appears. My
formula is:

Regeneration Data Date Score: Iif("d",DateDiff([Regeneration Data Date],
[1/1/2009])< 2,"1", Iif("d", DateDiff([Regeneration Data Date] ,[/1/2009] <
3, "2", Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 5, "3",
Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 10, "4", Iif("d",
DateDiff([Regeneration Data Date] , [1/1/2009] > 10, "5")))))

Please help!
Click to expand...
Click to expand...
 
J

John Spencer

Perhaps something like the following will work.

Regeneration Data Date Score:

IIF(DateDiff("yyyy",[Regeneration Data Date], #1/1/2009#)> 10, 5,
IIF(DateDiff("yyyy",[Regeneration Data Date], #1/1/2009#)> 5, 4,
IIF(DateDiff("yyyy",[Regeneration Data Date], #1/1/2009#)> 4, 3,
IIF(DateDiff("yyyy",[Regeneration Data Date], #1/1/2009#)> 2, 2,1))))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dale Fye

You have a number of formatting issues.
1. The datediff function requires at least 3 parameters (interval, date1,
date2), where interval is a string that denotes the interval to use ("d" for
days, "yyyy" for years, ...)
2. You used the "d" for days interval, but your description talks about years
3. Your date to compare with should be offset with #, not []
4. You need to closeout your DateDiff function with a right parenthesis ).

You also failed to account for the value of =10

Try it with:

Regeneration Data Date Score: Iif(DateDiff("yyyy", [Regeneration Data Date],
#1/1/2009#)< 2,"1", Iif(DateDiff("yyyy", [Regeneration Data Date]
,#1/1/2009#) <
3, "2", Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#) < 5, "3",
Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#) < 10, "4",
Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009# > 10, "5")))))

Now, then next problem is that of how the DateDiff function works.
Basically, it compares the value associated with the interval you have
selected between the two dates you provide, so DateDiff("yyyy", #12/31/2008#,
#1/1/2009#) = 1, even though there is actually only one day difference. So
I'm not really sure this is what you are looking for.

I personally think I would probably resort to writing a function and passing
it the two values you are looking for. This gives you added flexibility in
assessing the difference between the dates, and makes it significantly easier
to read. Something like the following should work:

Public Function fnRegenScore(Date1 as Date, CompareTo as Date) as string

Dim intYears as integer

'the second part of this accounts for the situation mentioned above
'and only counts full years that have been completed
intYears = DateDiff("yyyy", Date1, Date2) _
+ (Format(Date1, "mmdd") > Format(Date2, "mmdd"))

if intYears < 2 then
fnRegenScore = "1"
elseif intYears < 3
fnRegenScore = "2"
elseif intYears < 5
fnRegenScore = "3"
elseif intYears < 10
fnRegenScore = "4"
else
fnRegenScore = "5"
endif

End function

----
HTH
Dale



caro79 said:
I am trying to write an Iif statement where if the difference between the
regeneration data date and 1/1/2009 is less than two years, a 1 appears. If
the difference is less than 3 years a 2 is populated, if the difference is
less than 5 years a 3 is populated, if the difference is less than 10 years a
4 populates, and if the difference is greater than 10 years a 5 appears. My
formula is:

Regeneration Data Date Score: Iif("d",DateDiff([Regeneration Data Date],
[1/1/2009])< 2,"1", Iif("d", DateDiff([Regeneration Data Date] ,[/1/2009] <
3, "2", Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 5, "3",
Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 10, "4", Iif("d",
DateDiff([Regeneration Data Date] , [1/1/2009] > 10, "5")))))

Please help!
Click to expand...
 
C

caro79

Many thanks!!!

Dale Fye said:
You have a number of formatting issues.
1. The datediff function requires at least 3 parameters (interval, date1,
date2), where interval is a string that denotes the interval to use ("d" for
days, "yyyy" for years, ...)
2. You used the "d" for days interval, but your description talks about years
3. Your date to compare with should be offset with #, not []
4. You need to closeout your DateDiff function with a right parenthesis ).

You also failed to account for the value of =10

Try it with:

Regeneration Data Date Score: Iif(DateDiff("yyyy", [Regeneration Data Date],
#1/1/2009#)< 2,"1", Iif(DateDiff("yyyy", [Regeneration Data Date]
,#1/1/2009#) <
3, "2", Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#) < 5, "3",
Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009#) < 10, "4",
Iif(DateDiff("yyyy", [Regeneration Data Date] , #1/1/2009# > 10, "5")))))

Now, then next problem is that of how the DateDiff function works.
Basically, it compares the value associated with the interval you have
selected between the two dates you provide, so DateDiff("yyyy", #12/31/2008#,
#1/1/2009#) = 1, even though there is actually only one day difference. So
I'm not really sure this is what you are looking for.

I personally think I would probably resort to writing a function and passing
it the two values you are looking for. This gives you added flexibility in
assessing the difference between the dates, and makes it significantly easier
to read. Something like the following should work:

Public Function fnRegenScore(Date1 as Date, CompareTo as Date) as string

Dim intYears as integer

'the second part of this accounts for the situation mentioned above
'and only counts full years that have been completed
intYears = DateDiff("yyyy", Date1, Date2) _
+ (Format(Date1, "mmdd") > Format(Date2, "mmdd"))

if intYears < 2 then
fnRegenScore = "1"
elseif intYears < 3
fnRegenScore = "2"
elseif intYears < 5
fnRegenScore = "3"
elseif intYears < 10
fnRegenScore = "4"
else
fnRegenScore = "5"
endif

End function

----
HTH
Dale



caro79 said:
I am trying to write an Iif statement where if the difference between the
regeneration data date and 1/1/2009 is less than two years, a 1 appears. If
the difference is less than 3 years a 2 is populated, if the difference is
less than 5 years a 3 is populated, if the difference is less than 10 years a
4 populates, and if the difference is greater than 10 years a 5 appears. My
formula is:

Regeneration Data Date Score: Iif("d",DateDiff([Regeneration Data Date],
[1/1/2009])< 2,"1", Iif("d", DateDiff([Regeneration Data Date] ,[/1/2009] <
3, "2", Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 5, "3",
Iif("d", DateDiff([Regeneration Data Date] , [1/1/2009] < 10, "4", Iif("d",
DateDiff([Regeneration Data Date] , [1/1/2009] > 10, "5")))))

Please help!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calc Diff Date with ID - Karl Dewey 2
Date Brackets as an IIf statement 3
Number of days not calculating correct in an iif expression 2
Query running sum - help again 2
Cycle Time Calculation Returns Blank 1
IIF statement in query calculated field 5
Help with small result type 2
Grouping - Distinct 5

Top