help with IF Statement based on Date?

[Guest]

Good Day All,

this is another question based on my milage tracking form. I have a field
on my form called MileageRate, and I would like to have some type of coding
so that if the date of the travel is within "2004" then the milagerate is
..0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook

 

[Wayne Morgan]

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

 

[Guest]

This is incorrect and raises the compile error Argument Not Optional:
IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

Correct:
IIf(Year([DateField])=2004, 0.0375, 0.0475))

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

--
Wayne Morgan
MS Access MVP

Good Day All,

this is another question based on my milage tracking form. I have a field
on my form called MileageRate, and I would like to have some type of
coding
so that if the date of the travel is within "2004" then the milagerate is
.0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook

 

[Wayne Morgan]

Klatuu,

You are correct, in VBA it gives the error. If used in a query though, it
does not. IIf() is processed differently in each. VBA evaluates the True and
False parts whether it needs to or not, so they have to be there. That is
why in VBA this will fail:

IIf(2<3, 0, 1/0)

Even though the false part isn't what will be used, VBA still evaluates it
and gives a "divide by zero" error. However, the Jet processor only
evaluates the one it needs, the statement above will work in a query. It
will also work as the control source in a form or report. If the answer is
False and the false part is missing, Jet returns Null.

--
Wayne Morgan
MS Access MVP

This is incorrect and raises the compile error Argument Not Optional:
IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

Correct:
IIf(Year([DateField])=2004, 0.0375, 0.0475))

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last
argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

--
Wayne Morgan
MS Access MVP

Good Day All,

this is another question based on my milage tracking form. I have a
field
on my form called MileageRate, and I would like to have some type of
coding
so that if the date of the travel is within "2004" then the milagerate
is
.0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook

 

[Guest]

Thanks for the info, Wayne.
Phylosophically, though, I would always code an immediate If statement with
both True and False values. It makes it clearer to read for we mortals.
Also, I notice in your posts you use a lot of nested immediate If statements.
For queries, they are useful, in VBA, they can be confusing and hard to read.
We all have our own practices. My personal rules for using the Iif are:
1. Never nest them. (Readability, Maintenance)
2. Never do any calculation in them. (VBA evaluates both sides and an error
can result.
I am not saying your extensive use of Iif is wrong, it is just not my style.
I want me code to be understandable to anyone who has to read and maintain
it. The Iif is one of the big offenders in readability.

Klatuu,

You are correct, in VBA it gives the error. If used in a query though, it
does not. IIf() is processed differently in each. VBA evaluates the True and
False parts whether it needs to or not, so they have to be there. That is
why in VBA this will fail:

IIf(2<3, 0, 1/0)

Even though the false part isn't what will be used, VBA still evaluates it
and gives a "divide by zero" error. However, the Jet processor only
evaluates the one it needs, the statement above will work in a query. It
will also work as the control source in a form or report. If the answer is
False and the false part is missing, Jet returns Null.

--
Wayne Morgan
MS Access MVP

This is incorrect and raises the compile error Argument Not Optional:
IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

Correct:
IIf(Year([DateField])=2004, 0.0375, 0.0475))

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last
argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005, 0.0475))

--
Wayne Morgan
MS Access MVP


Good Day All,

this is another question based on my milage tracking form. I have a
field
on my form called MileageRate, and I would like to have some type of
coding
so that if the date of the travel is within "2004" then the milagerate
is
.0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook

 

[Wayne Morgan]

The extensive use of nested IIf's tends to be in queries and the Control
Source of textboxes. I agree with you, in VBA code there are better ways
than nesting IIf's. A Select Case statement is usually a good way around
nesting IIf's and is definitely much easier to read.

--
Wayne Morgan
MS Access MVP

Thanks for the info, Wayne.
Phylosophically, though, I would always code an immediate If statement
with
both True and False values. It makes it clearer to read for we mortals.
Also, I notice in your posts you use a lot of nested immediate If
statements.
For queries, they are useful, in VBA, they can be confusing and hard to
read.
We all have our own practices. My personal rules for using the Iif are:
1. Never nest them. (Readability, Maintenance)
2. Never do any calculation in them. (VBA evaluates both sides and an
error
can result.
I am not saying your extensive use of Iif is wrong, it is just not my
style.
I want me code to be understandable to anyone who has to read and maintain
it. The Iif is one of the big offenders in readability.

Klatuu,

You are correct, in VBA it gives the error. If used in a query though, it
does not. IIf() is processed differently in each. VBA evaluates the True
and
False parts whether it needs to or not, so they have to be there. That is
why in VBA this will fail:

IIf(2<3, 0, 1/0)

Even though the false part isn't what will be used, VBA still evaluates
it
and gives a "divide by zero" error. However, the Jet processor only
evaluates the one it needs, the statement above will work in a query. It
will also work as the control source in a form or report. If the answer
is
False and the false part is missing, Jet returns Null.

--
Wayne Morgan
MS Access MVP

This is incorrect and raises the compile error Argument Not Optional:
IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475))

Correct:
IIf(Year([DateField])=2004, 0.0375, 0.0475))

:

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last
argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475))

--
Wayne Morgan
MS Access MVP


Good Day All,

this is another question based on my milage tracking form. I have a
field
on my form called MileageRate, and I would like to have some type of
coding
so that if the date of the travel is within "2004" then the
milagerate
is
.0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook

 

[Guest]

I think we are on the same page. One thing I usually do if there is a
complex formula needed in a query is to use a function. I am not sure if
that is less efficient, although my gut tells me it is, but efficiency has to
be moderated with sanity.

The extensive use of nested IIf's tends to be in queries and the Control
Source of textboxes. I agree with you, in VBA code there are better ways
than nesting IIf's. A Select Case statement is usually a good way around
nesting IIf's and is definitely much easier to read.

--
Wayne Morgan
MS Access MVP

Thanks for the info, Wayne.
Phylosophically, though, I would always code an immediate If statement
with
both True and False values. It makes it clearer to read for we mortals.
Also, I notice in your posts you use a lot of nested immediate If
statements.
For queries, they are useful, in VBA, they can be confusing and hard to
read.
We all have our own practices. My personal rules for using the Iif are:
1. Never nest them. (Readability, Maintenance)
2. Never do any calculation in them. (VBA evaluates both sides and an
error
can result.
I am not saying your extensive use of Iif is wrong, it is just not my
style.
I want me code to be understandable to anyone who has to read and maintain
it. The Iif is one of the big offenders in readability.

Klatuu,

You are correct, in VBA it gives the error. If used in a query though, it
does not. IIf() is processed differently in each. VBA evaluates the True
and
False parts whether it needs to or not, so they have to be there. That is
why in VBA this will fail:

IIf(2<3, 0, 1/0)

Even though the false part isn't what will be used, VBA still evaluates
it
and gives a "divide by zero" error. However, the Jet processor only
evaluates the one it needs, the statement above will work in a query. It
will also work as the control source in a form or report. If the answer
is
False and the false part is missing, Jet returns Null.

--
Wayne Morgan
MS Access MVP


This is incorrect and raises the compile error Argument Not Optional:
IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475))

Correct:
IIf(Year([DateField])=2004, 0.0375, 0.0475))

:

Try,

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475,
"Error"))

Replace "Error" with what ever you desire or you can skip the last
argument
entirely:

IIf(Year([DateField])=2004, 0.0375, IIf(Year([DateField])=2005,
0.0475))

--
Wayne Morgan
MS Access MVP


Good Day All,

this is another question based on my milage tracking form. I have a
field
on my form called MileageRate, and I would like to have some type of
coding
so that if the date of the travel is within "2004" then the
milagerate
is
.0375, or withing 2005 then the milagerate is ".0475".

Does anyone have any ideas? or suggestions?

Thanks,

Brook