Using Combox as a variable for calculating a total?

[CPOconllo]

OK fairly new at access so please bare with me.

I have a table that I am trying to add a querry to that gives me a total,
however that total needs to be based on a lookup colum selection.

the table has these fields in it
, Rate Type, Rate, Miles--------->here is where the querry for total would be
Flat/Per ---- -----

The look up colum in the table has 2 variable Flat and per the name of the
tab;e that the look up fields comes from is also called Rate Type, the Rate
and Miles Fields always have to be entered manualy, if the Rate Type Field is
"Flat" then miles needs not to be in equation as the total would equal the
rate, if the look up variable is "Per" then I need the Total colum to produce
the answer for rate*miles.

My difficulty in this is that the "Flat or Per" are never the same, so the
Rate and Miles they have to be entered manualy, I have tried the

Total: IIf([Rate Type].[Rate Type]="Flat",([Rate]*1),[Rate]*[MIles]), but
when I load into datasheet view a message pops up asking for value of [Rate
Type].[Rate Type] and then it will only give a total when both Rate and miles
have an entry.


I know long winded but I thought more detailed the easier would be for some
one to help me with this please

 

[tina]

my guess is that your problem stems from having a Lookup field *in the
table*. it's likely that the field is *not* storing the values "Flat" or
"Per", but instead storing numeric primary key values from the RateType
table - and that fact is hidden from you by the Lookup field. open the Rate
Type table to see what the name of the key field is and what the key value
is for "Flat" and use those values in your IIf() statement. for example, if
the primary key field is named RateTypeID and the key value of "Flat" is 1,
the IIf() statement should read as

IIf([Rate Type].[RateTypeID]=1,([Rate]*1),[Rate]*[MIles])

also, i strongly urge you to get rid of all Lookup fields in your table(s).
for more information, see
http://home.att.net/~california.db/tips.html#aTip8.

hth