asked on

Access VBA 3464: Data type mismatch in criteria expression.

I have this query which works in Access: -

Sql = "SELECT dbo_MdnMaster.DispatchNote, IIf(isnull([DeliveredQty]),'X',IIf([OriginalDespatchQty]-[DeliveredQty]<>0,'X','OK')) AS CheckIt "
Sql = Sql & "FROM dbo_MdnMaster INNER JOIN (dbo_Job INNER JOIN dbo_JobDetail ON dbo_Job.JobID = dbo_JobDetail.JobID) ON dbo_MdnMaster.DispatchNote = dbo_Job.JobRef3 "
Set Rst = Dbs.OpenRecordset(Sql)
Dim txtCheck
txtCheck = Rst!CheckIt

Open in new window

It fails on the last line, can anyone help?

Scott McDaniel (EE MVE )

What do you mean by "fail"? Do you get an error? If so, then post the details of that error.

If you post the SQL into a query in Access, does it return records as expected?

HKFuey

ASKER

Check the title : )

Re: If you post the SQL into a query in Access, does it return records as expected?

Yes

Gustav Brock

Try with:

If Rst.RecordCount > 0 Then
Rst.MoveFirst
txtCheck = Rst!CheckIt.Value
End If

/gustav

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Check the title : )

Would have been nice had you pointed out that 3464 was an ERROR. We can't read your mind, so in the future if you want to indicate that in your title, please write something like "Access VBA Error 3464 blah blah"

:)

HKFuey

ASKER

Hi Gustav,
I am checking for a record. I think the problem must be a null record in the formula as
txtCheck = Rst!Barcode works fine.

Hi Guy, I tried both: Dim txtCheck and Dim txtCheck as string.

The query in Access returns 'X' for this formula when 'DeliveredQty' is null : -
IIf(isnull([DeliveredQty]),'X',IIf([OriginalDespatchQty]-[DeliveredQty]<>0,'X','OK')) AS CheckIt "

SOLUTION