"data type mismatch in criteria expression"

[Guest]

Hi

Im stuck. I'm writing a query which summarises a previous query, but grouping items based on categories and summing values. Nothing complicated but required to summarise a relatively large access data base with lots and lots of little calculations

Unfortunately on one field it comes up with a "data type mismatch in criteria expression" error and won't run. The rest i can get to sum, but not this field. I have checked and changed the format for the field as 'currency', I've times the contents of the field by a value of one (just in case it wasn't being recognised as a number - it works for problems like this in excel)

The actual field is based on three other fields where it has a large iff function based on utilising the field which is not null (presuming the others are null) or if all three fields are null then return a $0 value

the expression is like this

T_Freight: 1*(IIf(IsNull([Tariff1]) And IsNull([Tariff2]) And IsNull([Tariff3]),0,IIf(IsNull([Tariff1]) And IsNull([Tariff2]),[Tariff3],IIf(IsNull([Tariff1]) And IsNull([Tariff3]),[Tariff2],IIf(IsNull([Tariff2]) And IsNull([Tariff3]),[Tariff1],-9999999))))

akward i know, but i couldn't seem to do a minimum of those fields and thought it might have been due to the blank cells

so any ideas on how and where i could find the source of the error to fix

thanks
Marcus.

 

[Peter Hoyle]

T_Freight: 1*(IIf(IsNull([Tariff1]) And IsNull([Tariff2]) And
IsNull([Tariff3]),0,IIf(IsNull([Tariff1]) And
IsNull([Tariff2]),[Tariff3],IIf(IsNull([Tariff1]) And
IsNull([Tariff3]),[Tariff2],IIf(IsNull([Tariff2]) And
IsNull([Tariff3]),[Tariff1],-9999999)))))

I haven't studied the above expression too closely but it looks as though it
might be trying to provide 3 values to a single column.

I would be inclined just to write a user defined function for this.
Warning - Air Code!

T_Freight: FindTariff(Tariff1, Tariff2, Tariff3)

Function FindTariff(T1 As Variant, T2 As Variant, T3 As Variant) As Single
Dim sngT1 As Single, sngT2 As Single, sngT3 As Single

sngT1 = Nz(T1, 0)
sngT2 = Nz(T2, 0)
sngT3 = Nz(T3, 0)

If sngT1 >= sngT2 and sngT1 >= sngT3 Then
FindTariff = sngT1
ElseIf sngT2 >= sngT1 and sngT2 >= sngT3 Then
FindTariff = sngT2
ElseIf sngT3 >= sngT1 and sngT3 >= sngT2 Then
FindTariff = sngT3
End If

End Function


Cheers,
Peter