Results 1 to 6 of 6

Thread: Special query

  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Question Unanswered: Special query

    I have a table that contain a field with 8 digit numbers. (ex. 54650001) The first four numbers identify a location and the last four number identify an item number. I need a query that will just look at the last four numbers and ignore the location. Is this possible?

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    are you trying to select by a certain item number? if so, if your number were 4987, you could put:
    Code:
       SELECT * FROM tblTable WHERE ((Right$([Field],4)="4987"));
    that would select all the info from the records where the last 4 digits in the field were 4987. hope it helps

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    i guess i should put the whole story....

    I have a table with 8-digit numbers- they number from ####0000- ####9999. We use these numbers to identify items in the warehouse. i need have a query that will give me all the UNUSED numbers so that i can assign them to products.

  4. #4
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    i can compare this table to a table where i have product numbers WITHOUT THE FOUR DIGIT LOCATION NUMBER

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Quote Originally Posted by cc3658
    i guess i should put the whole story....

    I have a table with 8-digit numbers- they number from ####0000- ####9999. We use these numbers to identify items in the warehouse. i need have a query that will give me all the UNUSED numbers so that i can assign them to products.
    I'm thinking the following should work:
    Code:
        Dim strTemp As String
        Dim i As Integer
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
        
        Set db = CurrentDb
        i = 0
        
        For i = 0 To 9999
          'puts the value of i into a string
          strTemp = CStr(i)
        
          'makes the string 4 digits long, with leading 0s to fill the spaces
          'so when i = 2, strTemp = 0002 and when i = 12, strTemp = 0012
          Do While Len(strTemp) < 4
              strTemp = "0" & strTemp
          Loop
          
          strSQL = "SELECT * FROM Table2 WHERE ((Right$([Field1],4)=" & strTemp & "))"
          
          'see if the number is already in the list
          Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)
          
          'if not then put them in a
          If rs.EOF Then
            lstBox.AddItem strTemp
          End If
    
        Next i
        
        'free resources
        rs.Close
        db.Close
    Note: you'll have to have DAO included in your references. If you dont know how to do that, in the Code window go to Tools->References and select Microsoft DAO 3.6 Object Library.
    Also, to put them to a listbox on the form the way i have it coded, you'll have to include a Microsoft Forms 2.0 Listbox on your Form. Just click the More Controls button in the toolbox and select it from the list, then draw it on the form.
    hope this helps you out. it'll still end up doing 10000 checks against the table, but that's the fewest i can figure, since you have 10000 possible numbers. good luck

  6. #6
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Thanks! I will try this out and post the results.

Posting Permissions

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