Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: Hod do code this?

    I have form that we will enter data (Numbers) by barcode reader and we have to different kinds barcodes. What I would like to do is formula that says if the first three digits is less than or equal 899 delete the last five digits else delete the last seven digits. Is this possible. here are two examples of the barcodes and what I need the outcome to be.

    1. 89912345678 = 899123

    2. 90012345678 = 9001

    Any advice will be appreciated.
    Last edited by hmho; 06-01-09 at 23:42.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In a query as an expression...(note: syntax and numbers to use for left may not be 100% correct)
    Try 1:
    iif(left([FieldName],3) <= 899, left([FieldName],6), left([FieldName],4)
    Try 2:
    iif(left([FieldName],3) <= 899, left([FieldName],len([FieldName])-6), left([FieldName],len([FieldName])-8)
    Try 3:
    iif(ABS(left([FieldName],3)) <= 899, left([FieldName],6), left([FieldName],4)

    In vba...

    Try 1:
    if Abs(left(me!MyFieldName,3)) <= 899 then
    MyVariableName = left(me!MyFieldName,6)
    else
    MyVariableName = left(me!MyFieldName,4)
    end if

    Try 2:
    Dim Left3 as integer
    Dim MyVariableName as integer

    Left3 = abs(left(me!MyFieldName,3))

    if Left3 <= 899 then
    MyVariableName = left(me!MyFieldName,6)
    else
    MyVariableName = left(me!MyFieldName,4)
    end if
    Last edited by pkstormy; 06-03-09 at 00:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on what barcode format and reader you are using you may need to put a but more error protection around PKStormy's code

    personally I'd suggest you place the code in a function (so if needs be the same code can be used in different forms.

    I'm guessing that you are wanting to do this once, IE at the point of capturing the barcode.

    public function TidyUpBarcode(strBarcode as sting) as integer
    'ok hopw do we flag an error
    'for now return 0 on error
    TidyUpbarcode=0
    if isnumeric(strbarcode) = false then exit function
    'ok so we know its a number
    if cint(left(strbarcode,3)) <900 then
    if len(strbarcode)>5 then 'check the length of the supplied barcode
    'if its more than 5 characters long chop off the last 5 characters
    TidyUpbarcode= left(strbarcode,len(strbarcode)-5
    else ' use all the digits in the barcode
    TidyUpbarcode= strbarcode
    endif
    else
    if len(strbarcode)>7 then
    TidyUpbarcode= left(strbarcode,len(strbarcode)-7
    else
    TidyUpbarcode= strbarcode
    endif
    endif
    end function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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