Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Better query (MS ACCESS 2003)

    I have a query that fills a list, however it works poorly.

    SELECT TXMASTERS.ID1, Val(Right([Barcode],9)) AS [AT], TXMASTERS.Barcode
    FROM TXMASTERS
    WHERE (((Val(Right([Barcode],9)) & '') Like Val([FORMS]![MainForm1].[LNAME].[CAPTION]) & '*'))
    ORDER BY TXMASTERS.Barcode;

    If you look at the attached picture showing a number entered next to the barcode reader icon, it does not match the number entered.
    As the number is text and numeric, and its a string, its not known which way to deal with it. Many thanks
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Better query (MS ACCESS 2003)

    Val returns 0 for all text strings that begin with a letter, so Val([FORMS]![MainForm1].[LNAME].[CAPTION]) evaluates to 0. Perhaps you should use =Val(Right([FORMS]![MainForm1].[LNAME].[CAPTION],9)) instead of the Like condition.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Better query (MS ACCESS 2003)

    Thanks Hans. I tried it but still have problems. If I use the like statement using the right statement it still fills the list wrongly, never really shows near matches to the inputted number , and of course if I eliminate the Like statement it only shows the one record when the complete number has been entered. Somehow it needs to be filling the list as more of the number gets entered, somehow looking at the len value of both caption and inputted text. As usual, I found this error at the wrong time thinking was all okay. Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Better query (MS ACCESS 2003)

    Why not use
    <code>
    WHERE [Barcode] Like [FORMS]![MainForm1].[LNAME].[CAPTION] & '*'
    </code>
    Otherwise I don't understand what you actually want.

Posting Permissions

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