Formula not working in a Query

[Lincoln Beachy]

I am helping someone to setup an database in access. We have the tables
and forms, reports,queries setup. But on of the query that we are having
problems with. We have an formula inserted to do some calc's in a field
in the query. But it does not allow us to use it. It worked all in MS
works database but it does not do it in access.
Here is the formula:
=IF(AND(UNITS=1,MODEL="Trailer"),5000,IF(AND(UNITS=1.5,MODEL="Trailer"),1
1000,IF(UNITS>3.5,35000,IF(UNITS>3,26000,IF(UNITS>2.5,18000,IF(UNITS>2,11
000,IF(UNITS>1,5000,0)))))))
Here is what we can put in that formula that will work:Coverage Level:
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))
This database is to keep track of an church auto insurance. We have
trailers in the auto plan and when we put the first formula in it will
pop up an message saying: The exppresion you entered contains invalid
syntax.
You may have entered a comma without preceding value or identifier. I
hit ok button. I could not get around it at all.
What should I do, we want to include the trailers too for the coverage
part for the clients to know what their coverage is for that vehicle or
trailer.
I need help,
In Christ Jesus,
Lincoln Beachy

 

[Duane Hookom]

I would try to find the best solution which might be a lookup table with
MODEL and UNITS fields as well as the returned value.

If that is beyond your ambition, you should build a user defined function in
a standard module. Keep in mind the coverage levels WILL change in the
future so a huge expression with lots of IIf()s will be a future maintenance
issue.

A function might be something like:
Public Function GetCoverageLevel(dblUnits As Double, _
strModel As String) As Long
Select Case True
Case dblUnits = 1 And strModel = "Trailer"
GetCoverageLevel = 5000
Case dblUnits = 1.5 And strModel = "Trailer"
GetCoverageLevel = 11000
Case dblUnits > 3.5
GetCoverageLevel = 35000
Case dblUnits > 3
GetCoverageLevel = 26000
Case dblUnits > 2.5
GetCoverageLevel = 18000
Case dblUnits > 2
GetCoverageLevel = 11000
Case dblUnits > 1
GetCoverageLevel = 5000
Case Else
GetCoverageLevel = 0
End Select
End Function

Finally, to fix your syntax, consider this
=IIf((UNITS=1 AND MODEL="Trailer"),5000, IIf(....) )

 

[Lincoln Beachy]

Duane,
I would I do the formula to include the other vehicles and the trailer,
I got the trailer part fix. I did it how you told. I used the module way
and the iif statement. Now I want to include the other parts of my
orginal formula too. Any Idea how I could do this???????????

 

[Duane Hookom]

What are the other parts of your original formula?

 

[Lincoln Beachy]

What are the other parts of your original formula?

Here is the orignal formula that I gave u in the first post.:
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))

I want to put the trailer coverage included with this formula too.

 

[Duane Hookom]

Are we supposed to know what "trailer coverage" is and how it should be
included?

--
Duane Hookom
MS Access MVP
--

What are the other parts of your original formula?

Here is the orignal formula that I gave u in the first post.:
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))

I want to put the trailer coverage included with this formula too.

 

[Lincoln Beachy]

Are we supposed to know what "trailer coverage" is and how it should be
included?

I made an another field for the trailer coverage and now I am trying to
total both fields together. This field is the total. I have the formula
is: ([coverage level]+[trailer]). It just get the totals for the trailer
s not the coverage level field. I am puzzled now. When I changed the
units to a higher number like from 1.0 unit to 2.5 units. The total will
move to the coverage level field.
The trailer owner might have more then one vehicle, that they own. What
should I do.
Lincoln Beachy

 

[Duane Hookom]

Why don't you describe your data and tell us what you want to calculate and
how it should be calculated. I would rather start from scratch.

--
Duane Hookom
MS Access MVP
--

Are we supposed to know what "trailer coverage" is and how it should be
included?

I made an another field for the trailer coverage and now I am trying to
total both fields together. This field is the total. I have the formula
is: ([coverage level]+[trailer]). It just get the totals for the trailer s
not the coverage level field. I am puzzled now. When I changed the units
to a higher number like from 1.0 unit to 2.5 units. The total will move to
the coverage level field.
The trailer owner might have more then one vehicle, that they own. What
should I do.
Lincoln Beachy