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?
HKFueyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the test on EOF is indeed requested...
also please data-type all your variables correctly:
Dim txtCheck as string

anyhow, the error you get is likely about either value data type issues for the JOIN ... ON conditions
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 as string
if not (Rst.eof and Rst.bof ) then
  txtCheck = Rst!CheckIt
end if

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
HKFueyAuthor Commented:
Check the title : )

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

Yes
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Gustav BrockCIOCommented:
Try with:

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

/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"

:)
0
 
HKFueyAuthor Commented:
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 "
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try using the Nz() function

 txtCheck = Nz(Rst!CheckIt,"")

or place a meaningful substitute for null

 txtCheck = nz(Rst!CheckIt,"nothing")
0
 
Gustav BrockConnect With a Mentor CIOCommented:
OK. Then you may simply need:

    IIf(Nz([OriginalDespatchQty]-[DeliveredQty], 0) = 0,'OK', 'X') AS CheckIt " 

/gustav
0
 
HKFueyConnect With a Mentor Author Commented:
Hi Guy,
Re: "anyhow, the error you get is likely about either value data type issues"

The field I assumed was null was actually a space DUH!!!!!!!.
0
 
HKFueyAuthor Commented:
Thanks to all who tried to help!
0
All Courses

From novice to tech pro — start learning today.