Expression Help

[Guest]

Hi,

I am trying to set minimum fees in a query from which I print a report. The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[Guest]

The logic and the nested IIFs look right, but what is "[Customer!Type]"?
Is this a table field?
Is this a form field?
I think maybe this is where you might be getting a Syntax error. The bang
(!) is either in the wrong place or maybe you are missing
brackets...depends...

Depending on the context, you'd might use one of these instead:
[Customer].[Type]
[Customer]![Type]
Customer!Type
Me.Type
Me!Type
......it just depends.....

In what context are you using this?
Is this within a Query?
Is this within a field on a form?
Is this within some macro or VBA code?
If so, are you using this to refer to fields in a recordset?

More info please.

In addition, in the future, I would avoid using reserved words such as
"Type" for field names.

 

[Guest]

You know...my bad.
I should have paid more attention to the forum I was in...Queries.
Ok, then.
Assuming the table name is Customer, and the field name is Type...

Try changing:
[Customer!Type]

to:
[Customer].[Type]

 

[Guest]

There are three tables involved in the query. One table Customer has a field
called "Type", which is the 1,2,3,4,5,6,7,8, etc.

When I change it to [Customer].[Type] then the management fee is shown
correctly as $25 for anything that is calculated as under $25.

Same for Customer!Type but it places the brackets back on the formula.

Same for [Customer]![Type]. So, all three work, but the main issue appears
to be that somehow the formula is restricting the types to only 10.

Any of these seem to be restricting the list to only those that are type 10,
but at least they are displaying the correct amount. I wonder why the Type is
being restricted? If this was repaired I have a feeling that the $75 would be
working too.

In what context are you using this?

Is this within a Query? It is a field in a query.
Is this within a field on a form? No. a field from one of three tables being queried.
Is this within some macro or VBA code? No
If so, are you using this to refer to fields in a recordset? No

More info please.

In addition, in the future, I would avoid using reserved words such as
"Type" for field names. I only used this to change the real table and field name in case someone might recognize it.

The logic and the nested IIFs look right, but what is "[Customer!Type]"?
Is this a table field?
Is this a form field?
I think maybe this is where you might be getting a Syntax error. The bang
(!) is either in the wrong place or maybe you are missing
brackets...depends...

Depending on the context, you'd might use one of these instead:
[Customer].[Type]
[Customer]![Type]
Customer!Type
Me.Type
Me!Type
.....it just depends.....

In what context are you using this?
Is this within a Query?
Is this within a field on a form?
Is this within some macro or VBA code?
If so, are you using this to refer to fields in a recordset?

More info please.

In addition, in the future, I would avoid using reserved words such as
"Type" for field names.

Hi,

I am trying to set minimum fees in a query from which I print a report. The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[John Spencer]

Is Customer.Type a number field or a text field? If it is a number field

IIF(Customer.Type=10, IIF(MgmtFee<25,25,MgmtFee),
IIF(MgmtFee<75,75,MgmtFee))

Other than that I don't see what could cause the calculation to fail.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Novice,

Part of your problem may be that Type is a reserved word in Access.
Whenever you use reserved words a field names (this is highly frowned upon)
you need to wrap them in brackets [], otherwise, you may confuse Access and
get error messages or unexpected results. I would change the field name if
you can.

I've got a function that I use that may simplify this some. the function
fnMax( ) accepts an array of values and will determine the maximum value
from the set of values passed to it. I leave it to you to modify this to
get the fnMin function. Because you don't want your MgmtFee1 to be less
than a particular value ($25 if Type is 10, $75 if Type is anything else),
you would use this function like:

MgmtFee1: IIF([Customer].[Type] = 10, fnMax(25, [MgmtFee]), fnMax(75,
[MgmtFee]))

If Type =10 and [MgmtFee] = 30, then MgmtFee1 will be 30
If Type =10 and [MgmtFee] = 20, then MgmtFee1 will be 25
If Type <>10 and [MgmtFee] = 30, then MgmtFee1 will be 75
If Type <>10 and [MgmtFee] = 120, then MgmtFee1 will be 120

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type, but
can be any type including NULL values
'fnMax(3, 7, 10) = 10
'fnMax(#9/1/07#, #9/15/07#, #10/1/06#) = #9/15/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or SomeValues(intLoop) < myMin Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function

HTH
Dale

 

[Guest]

I get #Error in the column.

Whatever the problem is it has to do with the

Is Customer.Type a number field or a text field? If it is a number field

IIF(Customer.Type=10, IIF(MgmtFee<25,25,MgmtFee),
IIF(MgmtFee<75,75,MgmtFee))

Other than that I don't see what could cause the calculation to fail.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,

I am trying to set minimum fees in a query from which I print a report. The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[Guest]

It isn't really named Type. I replaced what was there for purposes of this
discussion. I didn't want to use the real name of the field.

MgmtFee1: IIF([dbo_grp_project].[ IND_PRJ] = 10, fnMax(25, [MgmtFee]),
fnMax(75, [MgmtFee]))
Gives a pop-up “Undefined Function “fnMax†in expression.

Novice,

Part of your problem may be that Type is a reserved word in Access.
Whenever you use reserved words a field names (this is highly frowned upon)
you need to wrap them in brackets [], otherwise, you may confuse Access and
get error messages or unexpected results. I would change the field name if
you can.

I've got a function that I use that may simplify this some. the function
fnMax( ) accepts an array of values and will determine the maximum value
from the set of values passed to it. I leave it to you to modify this to
get the fnMin function. Because you don't want your MgmtFee1 to be less
than a particular value ($25 if Type is 10, $75 if Type is anything else),
you would use this function like:

MgmtFee1: IIF([Customer].[Type] = 10, fnMax(25, [MgmtFee]), fnMax(75,
[MgmtFee]))

If Type =10 and [MgmtFee] = 30, then MgmtFee1 will be 30
If Type =10 and [MgmtFee] = 20, then MgmtFee1 will be 25
If Type <>10 and [MgmtFee] = 30, then MgmtFee1 will be 75
If Type <>10 and [MgmtFee] = 120, then MgmtFee1 will be 120

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type, but
can be any type including NULL values
'fnMax(3, 7, 10) = 10
'fnMax(#9/1/07#, #9/15/07#, #10/1/06#) = #9/15/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or SomeValues(intLoop) < myMin Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function

HTH
Dale

Hi,

I am trying to set minimum fees in a query from which I print a report.
The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[John Spencer]

Did you create a custom function in a VBA module named fnMax? Or did
you copy one named fnMax? As far as I know there is no built-in
function named fnMax. If you did build a function with that name did
you save it in a module that is NOT named fnMax? The name of a module
should never be the same as the name of a function or sub that is saved
in any module in the database.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

It isn't really named Type. I replaced what was there for purposes of this
discussion. I didn't want to use the real name of the field.

MgmtFee1: IIF([dbo_grp_project].[ IND_PRJ] = 10, fnMax(25, [MgmtFee]),
fnMax(75, [MgmtFee]))
Gives a pop-up “Undefined Function “fnMax†in expression.

Novice,

Part of your problem may be that Type is a reserved word in Access.
Whenever you use reserved words a field names (this is highly frowned upon)
you need to wrap them in brackets [], otherwise, you may confuse Access and
get error messages or unexpected results. I would change the field name if
you can.

I've got a function that I use that may simplify this some. the function
fnMax( ) accepts an array of values and will determine the maximum value
from the set of values passed to it. I leave it to you to modify this to
get the fnMin function. Because you don't want your MgmtFee1 to be less
than a particular value ($25 if Type is 10, $75 if Type is anything else),
you would use this function like:

MgmtFee1: IIF([Customer].[Type] = 10, fnMax(25, [MgmtFee]), fnMax(75,
[MgmtFee]))

If Type =10 and [MgmtFee] = 30, then MgmtFee1 will be 30
If Type =10 and [MgmtFee] = 20, then MgmtFee1 will be 25
If Type <>10 and [MgmtFee] = 30, then MgmtFee1 will be 75
If Type <>10 and [MgmtFee] = 120, then MgmtFee1 will be 120

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type, but
can be any type including NULL values
'fnMax(3, 7, 10) = 10
'fnMax(#9/1/07#, #9/15/07#, #10/1/06#) = #9/15/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or SomeValues(intLoop) < myMin Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function

HTH
Dale

Hi,

I am trying to set minimum fees in a query from which I print a report.
The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[Guest]

No John. Dale Frye is way out of my undertanding level. I am not even sure
where to begin with that. I will have to play around with it, but I don't
have time for any head-pounding logic today. I can usually figure out the
answers that you all provide after some experimentation, and will play around
with it until I can write the proper expression. This is really important.
Thanks for you input. If you can make this any less painfull I would
appreciate it.

Did you create a custom function in a VBA module named fnMax? Or did
you copy one named fnMax? As far as I know there is no built-in
function named fnMax. If you did build a function with that name did
you save it in a module that is NOT named fnMax? The name of a module
should never be the same as the name of a function or sub that is saved
in any module in the database.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

It isn't really named Type. I replaced what was there for purposes of this
discussion. I didn't want to use the real name of the field.

MgmtFee1: IIF([dbo_grp_project].[ IND_PRJ] = 10, fnMax(25, [MgmtFee]),
fnMax(75, [MgmtFee]))
Gives a pop-up “Undefined Function “fnMax†in expression.

Novice,

Part of your problem may be that Type is a reserved word in Access.
Whenever you use reserved words a field names (this is highly frowned upon)
you need to wrap them in brackets [], otherwise, you may confuse Access and
get error messages or unexpected results. I would change the field name if
you can.

I've got a function that I use that may simplify this some. the function
fnMax( ) accepts an array of values and will determine the maximum value
from the set of values passed to it. I leave it to you to modify this to
get the fnMin function. Because you don't want your MgmtFee1 to be less
than a particular value ($25 if Type is 10, $75 if Type is anything else),
you would use this function like:

MgmtFee1: IIF([Customer].[Type] = 10, fnMax(25, [MgmtFee]), fnMax(75,
[MgmtFee]))

If Type =10 and [MgmtFee] = 30, then MgmtFee1 will be 30
If Type =10 and [MgmtFee] = 20, then MgmtFee1 will be 25
If Type <>10 and [MgmtFee] = 30, then MgmtFee1 will be 75
If Type <>10 and [MgmtFee] = 120, then MgmtFee1 will be 120

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type, but
can be any type including NULL values
'fnMax(3, 7, 10) = 10
'fnMax(#9/1/07#, #9/15/07#, #10/1/06#) = #9/15/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or SomeValues(intLoop) < myMin Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function

HTH
Dale

Hi,

I am trying to set minimum fees in a query from which I print a report.
The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks

 

[Guest]

Hi John,

I'm giving you credit for this one. You were right that there wasn't
anything wrong with the expression. The problem turned out to be the type of
join. Thanks for you input and the others.

Did you create a custom function in a VBA module named fnMax? Or did
you copy one named fnMax? As far as I know there is no built-in
function named fnMax. If you did build a function with that name did
you save it in a module that is NOT named fnMax? The name of a module
should never be the same as the name of a function or sub that is saved
in any module in the database.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

It isn't really named Type. I replaced what was there for purposes of this
discussion. I didn't want to use the real name of the field.

MgmtFee1: IIF([dbo_grp_project].[ IND_PRJ] = 10, fnMax(25, [MgmtFee]),
fnMax(75, [MgmtFee]))
Gives a pop-up “Undefined Function “fnMax†in expression.

Novice,

Part of your problem may be that Type is a reserved word in Access.
Whenever you use reserved words a field names (this is highly frowned upon)
you need to wrap them in brackets [], otherwise, you may confuse Access and
get error messages or unexpected results. I would change the field name if
you can.

I've got a function that I use that may simplify this some. the function
fnMax( ) accepts an array of values and will determine the maximum value
from the set of values passed to it. I leave it to you to modify this to
get the fnMin function. Because you don't want your MgmtFee1 to be less
than a particular value ($25 if Type is 10, $75 if Type is anything else),
you would use this function like:

MgmtFee1: IIF([Customer].[Type] = 10, fnMax(25, [MgmtFee]), fnMax(75,
[MgmtFee]))

If Type =10 and [MgmtFee] = 30, then MgmtFee1 will be 30
If Type =10 and [MgmtFee] = 20, then MgmtFee1 will be 25
If Type <>10 and [MgmtFee] = 30, then MgmtFee1 will be 75
If Type <>10 and [MgmtFee] = 120, then MgmtFee1 will be 120

Public Function fnMax(ParamArray SomeValues() As Variant) As Variant

'Accepts an array of parameters, preferably of the same data type, but
can be any type including NULL values
'fnMax(3, 7, 10) = 10
'fnMax(#9/1/07#, #9/15/07#, #10/1/06#) = #9/15/07#

Dim intLoop As Integer
Dim myMin As Variant

For intLoop = LBound(SomeValues) To UBound(SomeValues)

If IsNull(SomeValues(intLoop)) Then
'do nothing
ElseIf IsEmpty(myMin) Or SomeValues(intLoop) < myMin Then
myMin = SomeValues(intLoop)
End If

Next

fnMin = myMin

End Function

HTH
Dale

Hi,

I am trying to set minimum fees in a query from which I print a report.
The
management fee for a type"10" customer cannot be lower than $25. The
management fee for all other types (1,2,3,4,5,6,7,8,9 etc) cannot be lower
than $75.

MgmtFee1:
IIf([Customer!Type]="10",IIf([MgmtFee]<=25,25,[MgmtFee]),IIf([MgmtFee]<=75,75,[MgmtFee]))

This expression isn't working. What am I doing wrong? Thanks