creating a compound iff statement...

[Guest]

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

 

[fredg]

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

 

[Guest]

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

 

[fredg]

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

Click on Modules + New

Click on Insert + Procedure

Enter SomeName in the Name box.
Select the Function check box
Click ok

Type your function between the 2 existing lines.
Something like this:

Public Function SomeName(StringIn as string) as String
Dim strNew as String
If StringIn = "X" Then
strNew = "This"
ElseIf StringIn = "Y" then
strNew = "That"
ElseIf StringIn = "Z" Then
strNew = "This and That"
Else
srtNew = "None of the above"
End If
SomeName = strNew
End Function

Save the function.
When prompted, name the new module anything except SomeName.

Call if from a query and pass it the name of the field to be used in
the function.

Exp:SomeName([FieldA])

You'll probably also want to add error handling as needed.

 

[Guest]

Thanks for the clarification...

What if my string to my "tested" is a date field

and I need the following:

IF date = 2004 then mileagerate = .375
If date = 1/1/2005 - 8/31/2005 them mileagerate = .405
if date = 8/31/2005 - 12/31/2005 then mileagerate = .485

is this possible?

Brook

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

On Tue, 25 Oct 2005 16:51:03 -0700, Brook wrote:

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

Click on Modules + New

Click on Insert + Procedure

Enter SomeName in the Name box.
Select the Function check box
Click ok

Type your function between the 2 existing lines.
Something like this:

Public Function SomeName(StringIn as string) as String
Dim strNew as String
If StringIn = "X" Then
strNew = "This"
ElseIf StringIn = "Y" then
strNew = "That"
ElseIf StringIn = "Z" Then
strNew = "This and That"
Else
srtNew = "None of the above"
End If
SomeName = strNew
End Function

Save the function.
When prompted, name the new module anything except SomeName.

Call if from a query and pass it the name of the field to be used in
the function.

Exp:SomeName([FieldA])

You'll probably also want to add error handling as needed.

 

[Guest]

In that case, I'd suggest using the Select Case statement. Just look up the
syntax under help and you'll be fine.

HTH

Thanks for the clarification...

What if my string to my "tested" is a date field

and I need the following:

IF date = 2004 then mileagerate = .375
If date = 1/1/2005 - 8/31/2005 them mileagerate = .405
if date = 8/31/2005 - 12/31/2005 then mileagerate = .485

is this possible?

Brook

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

:

On Tue, 25 Oct 2005 16:51:03 -0700, Brook wrote:

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

Click on Modules + New

Click on Insert + Procedure

Enter SomeName in the Name box.
Select the Function check box
Click ok

Type your function between the 2 existing lines.
Something like this:

Public Function SomeName(StringIn as string) as String
Dim strNew as String
If StringIn = "X" Then
strNew = "This"
ElseIf StringIn = "Y" then
strNew = "That"
ElseIf StringIn = "Z" Then
strNew = "This and That"
Else
srtNew = "None of the above"
End If
SomeName = strNew
End Function

Save the function.
When prompted, name the new module anything except SomeName.

Call if from a query and pass it the name of the field to be used in
the function.

Exp:SomeName([FieldA])

You'll probably also want to add error handling as needed.

 

[Guest]

Thanks,

I tried that, and the problem with that is that I am unable to create my
report based on the qry b/c of the subquery...

Brook

In that case, I'd suggest using the Select Case statement. Just look up the
syntax under help and you'll be fine.

HTH

Thanks for the clarification...

What if my string to my "tested" is a date field

and I need the following:

IF date = 2004 then mileagerate = .375
If date = 1/1/2005 - 8/31/2005 them mileagerate = .405
if date = 8/31/2005 - 12/31/2005 then mileagerate = .485

is this possible?

Brook

On Tue, 25 Oct 2005 17:27:03 -0700, Brook wrote:

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

:

On Tue, 25 Oct 2005 16:51:03 -0700, Brook wrote:

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Click on Modules + New

Click on Insert + Procedure

Enter SomeName in the Name box.
Select the Function check box
Click ok

Type your function between the 2 existing lines.
Something like this:

Public Function SomeName(StringIn as string) as String
Dim strNew as String
If StringIn = "X" Then
strNew = "This"
ElseIf StringIn = "Y" then
strNew = "That"
ElseIf StringIn = "Z" Then
strNew = "This and That"
Else
srtNew = "None of the above"
End If
SomeName = strNew
End Function

Save the function.
When prompted, name the new module anything except SomeName.

Call if from a query and pass it the name of the field to be used in
the function.

Exp:SomeName([FieldA])

You'll probably also want to add error handling as needed.

 

[fredg]

Thanks for the clarification...

What if my string to my "tested" is a date field

and I need the following:

IF date = 2004 then mileagerate = .375
If date = 1/1/2005 - 8/31/2005 them mileagerate = .405
if date = 8/31/2005 - 12/31/2005 then mileagerate = .485

is this possible?

Brook

good day Fredg,

Thanks for the quick response..

I think the best bet for me would be to call a function...

Do I just code the if then else statement in standard if then else coding?

how do I call the function? and when you say funtion.. do you mean module?

Thanks,

Brook

:

On Tue, 25 Oct 2005 16:51:03 -0700, Brook wrote:

Good day all...

Is it possible to create a compound IF Statement in Access Query?

For example... I want to set up a statement similar to :

If X, then
XX
Else If Y, then
YY

Is this possible?

Thanks,

Brook

What if it's neither X nor Y?

Directly in an Access Query, you can use the IIf() function.

Exp:IIf([FieldA]="X","Show This",IIf([FieldA] = "Y","Show this
instead","Neither X nor Y"))

If you have a more complicated If Then you can use If.. Then.. Else in
a Public Function in a Module.
Simply call the function from the query:
ExpoThisFunction([FieldName])

There are additional functions that you can also use, such as (in the
query) Choose, Switch and (in a public function) Select Case.

Click on Modules + New

Click on Insert + Procedure

Enter SomeName in the Name box.
Select the Function check box
Click ok

Type your function between the 2 existing lines.
Something like this:

Public Function SomeName(StringIn as string) as String
Dim strNew as String
If StringIn = "X" Then
strNew = "This"
ElseIf StringIn = "Y" then
strNew = "That"
ElseIf StringIn = "Z" Then
strNew = "This and That"
Else
srtNew = "None of the above"
End If
SomeName = strNew
End Function

Save the function.
When prompted, name the new module anything except SomeName.

Call if from a query and pass it the name of the field to be used in
the function.

Exp:SomeName([FieldA])

You'll probably also want to add error handling as needed.

You have asked a general question, for which I gave a generalized
response. Now you are asking a specific question. Wouldn't it have
been simpler and quicker to ask the specific question first?

IF date = 2004 then mileagerate = .375

1) 2004 is not a date.
A date includes 4 components... Month Day Year and a Time value.
Even if you just entered a date (using Date()), the time value is
presumed to be .0 (midnight).

If date = 1/1/2005 - 8/31/2005 them mileagerate = .405
if date = 8/31/2005 - 12/31/2005 then mileagerate = .485

2) When referring to written dates you must wrap the written date
within the date delimiter symbol (#)

Assuming all the dates in the table have been entered using Date()
(without a time value):

Function GetRate(DateIn as Date) as Double
Dim Rate as Double
If [DateIn] Between #1/1/2004# and #12/31/2004# Then
' or you could use If Year(DateIn) = 2004 Then
[Rate] = 0.375
ElseIf [DateIn] Between #1/1/2005# and #8/31/2005# Then
[Rate] = 0.405
Else
[DateIn] Between #9/1/2005# and #12/31/2005# Then
[Rate] = 0.485
End If
GetRate = Rate
End Function

Note that if your criteria ends at 8/31 you must start the next
criteria at 9/1.

Call it from the query:
MileageRate:GetRate([DateField])

Actually, if this is the entire need, I would just use an IIF
statement right in the query:
MileageRate:IIf(Year([DateField]) = 2004, 0.375, IIf([DateField]
Between #1/1/2005# and #8/31/2005#, 0.405, 0.475))