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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Gustav BrockCIOCommented:
Try with:

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

/gustav
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)Commented:
try using the Nz() function

 txtCheck = Nz(Rst!CheckIt,"")

or place a meaningful substitute for null

 txtCheck = nz(Rst!CheckIt,"nothing")
0
Gustav BrockCIOCommented:
OK. Then you may simply need:

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

/gustav
0
HKFueyAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.