Type Mismatch in Expression

[atrium]

I have an expression in a query for a report

Withdrawls: IIf(Eval([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False),([AmountTotal]*-1),0)

the fields Withdrawl and Reversal are both Yes/No fields. The logic is pretty simple but I keep getting a Type Mismatch error

 

[vbaInet]

Why are you using Eval?

 

[CJ_London]

try leaving out the eval - it's not required

Withdrawls: IIf([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False,([AmountTotal]*-1),0)

 

[atrium]

Have tried it and just tried it again and get the same error. I have three expressions:

Withdrawls: IIf([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False,([AmountTotal]*-1),0)

Deposits: IIf([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False,[AmountTotal],0)

TransAmount: IIf([Transaction Types].[Withdrawl]=True,([AmountTotal]*-1),[AmountTotal])

In my report I do a sum on the withdrawls, Deposits and the Transamount

I get the error when just trying to run the query

 

[vbaInet]

One or more of your fields in the Condition part of the IIF() function are not the right data type. For example you can't check string against boolean.

 

[atrium]

Deposit and withdrawl from the [Transaction Types] file are both Boolean and so is Reversal from the [Transactions] file

The query is below

SELECT Matters.MatterId, Matters.MatterTitle, [Payment methods].PaymentMethodDesc, Transactions.TransDate, Transactions.TransId, Transactions.TransCode, [Transaction Types].TransTypeDesc, First(Transactions.Particulars) AS FirstOfParticulars, Transactions.BankStatementDate, Transactions.MethodOfPayment, Transactions.ReceiptNo, Transactions.ReferenceNo, Transactions.Reversal, IIf([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False,[AmountTotal],0) AS Deposits, IIf([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False,([AmountTotal]*-1),0) AS Withdrawls, IIf([Transaction Types].[Withdrawl]=True,([AmountTotal]*-1),[AmountTotal]) AS TransAmount
FROM [Transaction Types] RIGHT JOIN ([Payment methods] RIGHT JOIN (Transactions RIGHT JOIN Matters ON Transactions.MatterId = Matters.MatterId) ON [Payment methods].PaymentMethodDesc = Transactions.MethodOfPayment) ON [Transaction Types].TransTypeId = Transactions.TransCode
WHERE ((([Transaction Types].TrustLedger)=True))
GROUP BY Matters.MatterId, Matters.MatterTitle, [Payment methods].PaymentMethodDesc, Transactions.TransDate, Transactions.TransId, Transactions.TransCode, [Transaction Types].TransTypeDesc, Transactions.BankStatementDate, Transactions.MethodOfPayment, Transactions.ReceiptNo, Transactions.ReferenceNo, Transactions.Reversal, [Transaction Types].TrustLedger, [Transaction Types].Deposit, [Transaction Types].Withdrawl, Transactions.Particulars, Transactions.AmountTotal
ORDER BY Matters.MatterId;

 

[vbaInet]

Where exactly are you getting the error message?

 

[vbaInet]

If you get the error message in the query then it could be that some of the fields you're joining against are not of the same data type.

 

[CJ_London]

I presume withdrawl is not a typo?

You are using a right join so perhaps you have a row where there is no match and it is trying to compare with a null value?

Also try changing right joins to left joins

 

[vbaInet]

I presume withdrawl is not a typo?

You are using a right join so perhaps you have a row where there is no match and it is trying to compare with a null value?

Also try changing right joins to left joins

As it's a type mismatch so having a field name spelt wrongly will throw a different error.

It's fine if it sees Null it just won't include it in the result. LEFT or RIGHT shouldn't matter.

 

[atrium]

I do apologise for taking up your time guys. But with your help I found the problem. YES it was one of the joins.

Problem Solved

Cheers

Atrium