Results 1 to 8 of 8

Data type mismatch

  1. #1
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    186

    Data type mismatch

    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!

    Code:
    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

  2. #2
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,415
    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?
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,795

  4. #4
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    186
    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

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,795
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,415
    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
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,713
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  8. #8
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    790
    The Left function returns a string, try mItemNo = cint(Left(Me.ItemNo, 6)).

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. keep getting data type mismatch
    By tagteam in forum Access
    Replies: 3
    Last Post: 04-05-2018, 02:26 PM
  2. data type mismatch
    By ottoc in forum Queries
    Replies: 1
    Last Post: 12-02-2014, 09:42 AM
  3. Data Type Mismatch in SQL
    By Phred in forum Queries
    Replies: 2
    Last Post: 01-04-2012, 03:40 PM
  4. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 03:48 AM
  5. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums