Simple query with "like" not working >:(

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Bruce Lawrence

    #1

    Simple query with "like" not working >:(

    Ok, I'm baffled...

    I'm making a query in access 97 between 2 tables. There is a field in
    both tables called "DWGNO".

    OPENORD has a record with a DWGNO of "0000001234 5"
    DIEDATA has a record with a DWGNO of "12345"

    I'm not doing this with VBA right now. I'm doing it through the query
    gui.

    My query:
    Both tables are in the upper section with no links between them.
    I have selected 4 fields. Only 3 are told to display results.
    The field that is not showing results is where the criteria is entered.
    The field is called DieData.DWGNO and the criteria is Like "*" &
    [openord]![dwgno]

    This shows no query results.
    Please help me

  • Allen Browne

    #2
    Re: Simple query with "like&quot ; not working >:(

    If you open these tables in design view, what is the Data Type of the
    fields? If they are Text, the text does not match because the string
    "0000001234 5" is nothing like the string "12345"

    If they are Number type fields, be aware that Like is a string operator, so
    you are still performing a string match. Open the Immediate Window (Ctrl+G)
    and enter these lines:
    ? "12345" Like "*" & "0000001234 5"
    ? "0000001234 5" Like "*" & "12345"
    The first returns False, while the second returns True.
    The example you gave is like the first one, which does not match.

    The other think to keep in mind is that Nulls don't match with Like "*".

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Bruce Lawrence" <BL32375@gmail. comwrote in message
    news:1156428225 .891917.61200@7 4g2000cwt.googl egroups.com...
    Ok, I'm baffled...
    >
    I'm making a query in access 97 between 2 tables. There is a field in
    both tables called "DWGNO".
    >
    OPENORD has a record with a DWGNO of "0000001234 5"
    DIEDATA has a record with a DWGNO of "12345"
    >
    I'm not doing this with VBA right now. I'm doing it through the query
    gui.
    >
    My query:
    Both tables are in the upper section with no links between them.
    I have selected 4 fields. Only 3 are told to display results.
    The field that is not showing results is where the criteria is entered.
    The field is called DieData.DWGNO and the criteria is Like "*" &
    [openord]![dwgno]
    >
    This shows no query results.
    Please help me
    >

    Comment

    • Bruce Lawrence

      #3
      Re: Simple query with &quot;like&quot ; not working &gt;:(

      I see.

      Well, then I guess I need a VBA module to convert this to a regular
      number or remove the leading zeros.

      Unless you have some quick code to copy/paste here, I'll start hunting
      for a resolution.

      Thanks Allen

      Comment

      • Allen Browne

        #4
        Re: Simple query with &quot;like&quot ; not working &gt;:(

        Make a backup copy of the mdb file (while it is NOT in use.)

        Open the Relationships window (Tools menu), and remove any relationships
        that use these fields.

        Open the tables in design view, and change the Data Type to Number. Save.
        Verify the numbers are still correct (sans the leading digits.)

        Close the tables. Go back to the Relationships window again, and create the
        relations with the Referential Integrity box checked.

        They should now match correctly.

        BTW, you can still display them with leading zeros if you wish, even after
        converting them to numbers. Just set the Format property of the fields, to:
        00000000000

        --
        Allen Browne - Microsoft MVP. Perth, Western Australia.
        Tips for Access users - http://allenbrowne.com/tips.html
        Reply to group, rather than allenbrowne at mvps dot org.

        "Bruce Lawrence" <BL32375@gmail. comwrote in message
        news:1156434381 .244361.268060@ h48g2000cwc.goo glegroups.com.. .
        >I see.
        >
        Well, then I guess I need a VBA module to convert this to a regular
        number or remove the leading zeros.
        >
        Unless you have some quick code to copy/paste here, I'll start hunting
        for a resolution.
        >
        Thanks Allen

        Comment

        • Bruce Lawrence

          #5
          Re: Simple query with &quot;like&quot ; not working &gt;:(

          Theres a small snag with that. These aren't always going to be
          numeric fields. We could have "00012345R1 "

          The problem is the lady who updated the records in the DieData table
          left the 0's out of it. The openord records come out of our SAP system
          and I import those automatically through import specs.

          I'm going to need some type of function or whatever to take out the
          preceding zeros and then compare the 2 fields.

          I'm not having much luck with that part

          Comment

          • Bri

            #6
            Re: Simple query with &quot;like&quot ; not working &gt;:(

            Bruce Lawrence wrote:
            Ok, I'm baffled...
            >
            I'm making a query in access 97 between 2 tables. There is a field in
            both tables called "DWGNO".
            >
            OPENORD has a record with a DWGNO of "0000001234 5"
            DIEDATA has a record with a DWGNO of "12345"
            >
            I'm not doing this with VBA right now. I'm doing it through the query
            gui.
            >
            My query:
            Both tables are in the upper section with no links between them.
            I have selected 4 fields. Only 3 are told to display results.
            The field that is not showing results is where the criteria is entered.
            The field is called DieData.DWGNO and the criteria is Like "*" &
            [openord]![dwgno]
            >
            This shows no query results.
            Please help me
            The problem is that you are putting the wildcard on the field WITH the
            leading zeros. Try it the other way around:
            openord!dwgno Like "*" & DIEDATA!dwgno

            Another alternative is (assuming that these always are numbers):
            CLng(openord!dw gno) = CLng(DIEDATA!dw gno)
            or if they really will get into the nine or ten digit length:
            CDec(openord!dw gno) = CDec(DIEDATA!dw gno)

            The wildcard criteria and thes conversion criteria could be slow if
            there are a lot of records since none of them will use indexes on the
            dwgno fields. If these are truely numbers, then maybe you should store
            them as numbers and indes them. Then you could format the number to add
            in the leading zeros on output. This would be much faster and also more
            efficient in storage.

            --
            Bri



            Comment

            • Allen Browne

              #7
              Re: Simple query with &quot;like&quot ; not working &gt;:(

              Len() gives you the length of a string.
              String() gives you a number of characters.

              So you may be able to use an Update query to pad a field named F1 with
              leading zeros so it is 11 characters long. The expression would be something
              like this:
              String(11-Len([F1]), "0") & [F1]

              --
              Allen Browne - Microsoft MVP. Perth, Western Australia.
              Tips for Access users - http://allenbrowne.com/tips.html
              Reply to group, rather than allenbrowne at mvps dot org.

              "Bruce Lawrence" <BL32375@gmail. comwrote in message
              news:1156437723 .420418.306990@ h48g2000cwc.goo glegroups.com.. .
              Theres a small snag with that. These aren't always going to be
              numeric fields. We could have "00012345R1 "
              >
              The problem is the lady who updated the records in the DieData table
              left the 0's out of it. The openord records come out of our SAP system
              and I import those automatically through import specs.
              >
              I'm going to need some type of function or whatever to take out the
              preceding zeros and then compare the 2 fields.
              >
              I'm not having much luck with that part
              >

              Comment

              • Bruce Lawrence

                #8
                Re: Simple query with &quot;like&quot ; not working &gt;:(

                Sorry for dragging this out but I'm getting confused with which
                solution I use or even search for.

                They are text fields because of the alphanumeric values put into DWGNO.
                DWGNO in the OpenOrd table is automatically filled in with an import.
                DWGNO in DieData table is manually updated.

                In my database, I ask the user for an order number. That order number
                and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
                and try to find it in the DieData table so I can get information from
                that table as well.

                The challenge is finding the DGWNO in DieData. DieData's is smaller
                than OpenOrd's.

                I have posted an image of my query online so you can see what I'm
                doing.



                Comment

                • Smartin

                  #9
                  Re: Simple query with &quot;like&quot ; not working &gt;:(

                  Bruce Lawrence wrote:
                  Ok, I'm baffled...
                  >
                  I'm making a query in access 97 between 2 tables. There is a field in
                  both tables called "DWGNO".
                  >
                  OPENORD has a record with a DWGNO of "0000001234 5"
                  DIEDATA has a record with a DWGNO of "12345"
                  >
                  I'm not doing this with VBA right now. I'm doing it through the query
                  gui.
                  >
                  My query:
                  Both tables are in the upper section with no links between them.
                  I have selected 4 fields. Only 3 are told to display results.
                  The field that is not showing results is where the criteria is entered.
                  The field is called DieData.DWGNO and the criteria is Like "*" &
                  [openord]![dwgno]
                  >
                  This shows no query results.
                  Please help me
                  >
                  ?

                  Like "*" & trim(str([openord]![dwgno])) & "*"

                  --
                  Smartin

                  Comment

                  • Allen Browne

                    #10
                    Re: Simple query with &quot;like&quot ; not working &gt;:(

                    Bruce, let's identify the problem. It is that some fields have a fixed
                    number of leading zeros, and others are variable length, and therefore the
                    fields don't match?

                    Possible approaches:
                    A. Use an Update query to change the data so it does match. You can then
                    create a relationship between the table, and know that they will match for
                    sure. Use the information in the last post to pad everything with leading
                    zeros to (say) 11 characters in both tables. To make an update query, use
                    Update on the Query menu in query design. Then once the data matches, choose
                    Relationships on the Tools menu to create the relation, and check the box
                    for Referential Integrity. Problem permanently solved.

                    B. If the data is constantly being dumped and the bad data being imported
                    again, the permanent solution doesn't apply. You might solve it by repeading
                    the criteria under PRODORD on the next line again (the "Or" line in query
                    design), and adding this to the Or line under DWGNO from OPENORD:
                    Like "*" & [DieData].[Dwgno]
                    Now it doesn't matter which of the 2 tables has the leading zeros, the text
                    should match.

                    --
                    Allen Browne - Microsoft MVP. Perth, Western Australia.
                    Tips for Access users - http://allenbrowne.com/tips.html
                    Reply to group, rather than allenbrowne at mvps dot org.

                    "Bruce Lawrence" <BL32375@gmail. comwrote in message
                    news:1156449752 .663761.242880@ m73g2000cwd.goo glegroups.com.. .
                    Sorry for dragging this out but I'm getting confused with which
                    solution I use or even search for.
                    >
                    They are text fields because of the alphanumeric values put into DWGNO.
                    DWGNO in the OpenOrd table is automatically filled in with an import.
                    DWGNO in DieData table is manually updated.
                    >
                    In my database, I ask the user for an order number. That order number
                    and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
                    and try to find it in the DieData table so I can get information from
                    that table as well.
                    >
                    The challenge is finding the DGWNO in DieData. DieData's is smaller
                    than OpenOrd's.
                    >
                    I have posted an image of my query online so you can see what I'm
                    doing.
                    >
                    http://www.c-o-g.com/img/qryprob.jpg

                    Comment

                    • Bri

                      #11
                      Re: Simple query with &quot;like&quot ; not working &gt;:(


                      Bruce Lawrence wrote:
                      Sorry for dragging this out but I'm getting confused with which
                      solution I use or even search for.
                      >
                      They are text fields because of the alphanumeric values put into DWGNO.
                      DWGNO in the OpenOrd table is automatically filled in with an import.
                      DWGNO in DieData table is manually updated.
                      >
                      In my database, I ask the user for an order number. That order number
                      and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
                      and try to find it in the DieData table so I can get information from
                      that table as well.
                      >
                      The challenge is finding the DGWNO in DieData. DieData's is smaller
                      than OpenOrd's.
                      >
                      I have posted an image of my query online so you can see what I'm
                      doing.
                      >
                      http://www.c-o-g.com/img/qryprob.jpg
                      You have a decision to make. Either the field with the leading zeros
                      needs to lose those leading zeros or the other field needs to gain those
                      leading zeros. Otherwise you are trying to compare things that will
                      never be the same. I showed you a method for removing the leading zeros
                      by converting the field to a number, but it seems that this s an
                      alphanumeric field, so that won't work. Allen Browne showed you a
                      formula that will add in the leading zeros to the field without them.
                      His solution involves permanently changing the data in that field. The
                      problem with that is that if your data entry allows for the leading
                      zeros to not be entered, then the problem will recreate itself. Putting
                      the formula into the query will work, but it will be slow.

                      SELECT Openord.Prodord FROM Openord, DieData
                      WHERE openord!dwgno = String(11-Len(DIEDATA!dwg no), "0") & DIEDATA!dwgno

                      This does make the assumption that the fields are always 11 characters
                      long, but in your examples you've shown both 10 and 11 character fields.
                      I'm hoping that one of them was a typo.

                      --
                      Bri

                      Comment

                      • Bruce Lawrence

                        #12
                        Re: Simple query with &quot;like&quot ; not working &gt;:(

                        Just an update....

                        The problem has been resolved. The issue was that the specs I was
                        provided for importing the SAP report .txt file into Access was wrong.
                        I was importing 5 columns to the left too many which was giving me
                        preceding 0's in the field.

                        The access import spec was adjusted and no records come in with 0's any
                        longer so I can do direct comparisons now.

                        My apologies for dragging you guys through the mud on something as
                        silly as this. I did learn though so some good came of it

                        Thanks again guys

                        Comment

                        Working...