Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Unanswered: I have one question

    Hi Every One:

    I have [Item Number]="FJPCBAR6X4" , which is string and I want to Read the 8th value of this string and assign it to Integer variable . I am getting the TypeMismatch error , obvoiusly it doesn like assigning the string value to integer. How would i fix?. I have to read the 8th value in this string and then multiply it by the QtyToCheck. I am pasting my code with line iwth error. Any help will be appreciated.
    Thanx in advance,
    VBAPROGRAMMER


    Public Function getRecipeColumn(itemNo As String, QtyToCheck As Integer) As Integer
    Dim formname, Form As Form
    Dim curdb As Database
    Dim columnadrs As Recordset
    Dim SQLSTmt, recipeItemNo As String
    Dim convertQty As Integer
    If ErrorTrapping = True Then
    On Error GoTo getRecipeColumn_Err_Handler
    End If
    Set Form = Forms![yworkOrder2].Form
    Set formname = Forms![yworkOrder2]![zworkOrderLineItem].Form
    Set curdb = CurrentDb()
    recipeItemNo = Form![Item Number]
    SQLSTmt = "Select * from [Inventory Recipes] where [FinishedProduct]='" & recipeItemNo & "'"
    Set columnadrs = curdb.OpenRecordset(SQLSTmt, dbOpenDynaset)
    While Not columnadrs.EOF

    'I am getting TYPEMISMATCH erro on this line , please help

    convertQty = Left$(itemNo, 8)
    QtyToCheck = convertQty * formname![Quantity Reqd]
    itemNo = columnadrs![columnCB]
    columnadrs.MoveNext
    Wend
    columnadrs.Close
    curdb.Close
    Set columnadrs = Nothing
    Set curdb = Nothing
    Exit Function
    getRecipeColumn_Err_Handler:
    MsgBox Err.Number & " " & Err.Description & " " & Err.Source, vbCritical
    Err.Clear
    Exit Function
    End Function

  2. #2
    Join Date
    Jan 2003
    Location
    Philadelphia, PA
    Posts
    3
    Is the what you want?

    i = val( mid([item number], 8, 1) )

    Wayne

  3. #3
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Hi Wayne
    This code is working just fine, but I have no idea what and how its doing , but its doing what i wanted. Now I am testing what you suggested Wayne.
    Thankyou for help


    readString = Right$(itemNo, 8 - 7)
    convertQty = CInt(readString)
    QtyToCheck = convertQty * formname![Quantity Reqd]

  4. #4
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Hi Wayne:

    WOhOOO this code is workign fine as well
    While Not columnadrs.EOF
    convertQty = Val(Mid(itemNo, 8, 1))
    Debug.Print convertQty
    QtyToCheck = convertQty * formname![Quantity Reqd]
    Debug.Print QtyToCheck
    itemNo = columnadrs![columnCB]
    columnadrs.MoveNext
    Wend

    Thank You so much for help. I really appreciate that.
    Bye

  5. #5
    Join Date
    Jan 2003
    Location
    Dallas/Houston
    Posts
    7

    Re: I have one question

    Originally posted by VBAPROGRAMMER
    Hi Every One:

    I have [Item Number]="FJPCBAR6X4" , which is string and I want to Read the 8th value of this string and assign it to Integer variable . I am getting the TypeMismatch error , obvoiusly it doesn like assigning the string value to integer. How would i fix?. I have to read the 8th value in this string and then multiply it by the QtyToCheck. I am pasting my code with line iwth error. Any help will be appreciated.
    Thanx in advance,
    VBAPROGRAMMER


    Public Function getRecipeColumn(itemNo As String, QtyToCheck As Integer) As Integer
    Dim formname, Form As Form
    Dim curdb As Database
    Dim columnadrs As Recordset
    Dim SQLSTmt, recipeItemNo As String
    Dim convertQty As Integer
    If ErrorTrapping = True Then
    On Error GoTo getRecipeColumn_Err_Handler
    End If
    Set Form = Forms![yworkOrder2].Form
    Set formname = Forms![yworkOrder2]![zworkOrderLineItem].Form
    Set curdb = CurrentDb()
    recipeItemNo = Form![Item Number]
    SQLSTmt = "Select * from [Inventory Recipes] where [FinishedProduct]='" & recipeItemNo & "'"
    Set columnadrs = curdb.OpenRecordset(SQLSTmt, dbOpenDynaset)
    While Not columnadrs.EOF

    'I am getting TYPEMISMATCH erro on this line , please help

    convertQty = Left$(itemNo, 8)

    IF YOU HAVE A TEXT VALUE IN A NUMERIC VALUE YOU CAN GET THAT KIND OF ERROR.

    YOU MIGHT WANT TO CHANGE TO THIS
    MID(VAL(itemNo,8,1))

    OR TRY THE VAL FUNCTION ?

    BYE

    QtyToCheck = convertQty * formname![Quantity Reqd]
    itemNo = columnadrs![columnCB]
    columnadrs.MoveNext
    Wend
    columnadrs.Close
    curdb.Close
    Set columnadrs = Nothing
    Set curdb = Nothing
    Exit Function
    getRecipeColumn_Err_Handler:
    MsgBox Err.Number & " " & Err.Description & " " & Err.Source, vbCritical
    Err.Clear
    Exit Function
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •