Data type mismatch

[DMT Dave]

Hi Guys, this one is bugging me as this should be so simple!

I am receiving datatype mismatch in criteria expression but i know the fields are set to number and my code is set to number, the left 6 digits will exist in the stock table but there may be text after it hence using the left,6 option!

Dim whQty As Long, avQty As Long, alQty As Long, mItemNo As Long
Dim wh As DAO.Recordset, al As DAO.Recordset, av As DAO.Recordset




If Left(Me.ItemType, 4) <> "Home" Then
DoCmd.CancelEvent
Else
mItemNo = Left(Me.ItemNo, 6)
Set wh = CurrentDb.OpenRecordset("Select tblStock.ItemNo, tblStock.StartQty From tblStock WHERE ItemNo = " & mItemNo)
Set av = CurrentDb.OpenRecordset("Select tblStock.ItemNo, tblStock.NewQty From tblStock WHERE ItemNo = " & mItemNo)
Set al = CurrentDb.OpenRecordset("Select tblStock.ItemNo, tblStock.AllocatedQty From tblStock WHERE ItemNo = " & mItemNo)


MsgBox ("Stock Details For Man No: " & mLiftNo & Chr(10) & Chr(10) & _
"Warehouse Qty: " & wh.Fields("StartQty") & Chr(10) & Chr(10) & _
"Allocated Qty: " & al.Fields("AllocatedQty") & Chr(10) & Chr(10) & _
"Available Qty: " & av.Fields("NewQty")), vbInformation + vbOKOnly, "RECORD DETAILS"
End If

[isladogs]

Where does mLiftNo come from in your MsgBox line?
Personally I would just use variables or DLookups if not used for other purposes as well.

Why are you setting three recordsets just to do a message...and are you setting all to nothing at the end?

[orange]

Dave,

In simple terms what are you trying to achieve? There may be options.

[DMT Dave]

Hi guy's, i was just wanting a message to show warehouse qty (startQty field) / allocated qty (allocated qty field) / Available Qty (NewQty field)

I tries the easier way via DLookup and got the same message datatype mismatch in criteria so tried the recordset route

[orange]

Dave,

Tell us about your application in 2 or 3 lines of plain English.
Show us the design of your table(s).
Or post a copy of your database with only a few records -remove anything confidential first- to show the issue.

[isladogs]

Dave
You didn't answer my questions
1. Where is mLiftNo defined?
2. If you must use recordsets (which I would think unnecessary), why use three of them?
Just add all three fields to the same recordset
3. Have you set the recordsets to nothing at the end?

Also
4. Why are you using Chr(10) twice in a row? Should that be Chr(10) & Chr(13)? If so its quicker to type vbCrLf or vbNewLine

[Micron]

i know the fields are set to number and my code is set to number, the left 6 digits will exist in the stock table but there may be text

That is puzzling and confusing. If there is text in the field after the numbers it cannot be a number field, thus "code set to number" is pointless - if we're talking about the same field
Two things I'd advise
- always indicate the line of the failure in your post
- in these situations, always output your sql (e.g. debug.print) and if the issue doesn't become obvious, paste it into a new query. If that is an action query you can switch to datasheet view to test without running. Note that datasheet views of updates (or is it all 3 actions) don't display final effects, just whether or not it will run.

[Gicu]

The Left function returns a string, try mItemNo = cint(Left(Me.ItemNo, 6)).

Cheers,
Vlad