Results 1 to 8 of 8

Thread: missing numbers

  1. #1
    Join Date
    May 2002
    Posts
    28

    Unanswered: missing numbers

    Hi,
    I have some IDno. from 1 to 20 idno some
    i entey according to Idno. every month.some time their are some idno
    miss for entery some i need a quer or function to show the miss id no.

    example:-

    In the month idno,1,5,8,9,4,15,20,14,13, are being entry into
    table. and 2,3,5,7,10,11,12,16,17,18,19 are missing to be enter
    i need qury or code function to show the miss numbers


    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    You could create a table with the IDno you want to have (i.e. 1,2,3...), Saying it's called IDNoList and your table with your entered IDNo is called IDNoEntry in this example:

    SELECT IDNo FROM IDNoList WHERE IDNo NOT IN (SELECT IDNo FROM IDNoEntry);

    not sure if it's exactly what you wanted but this will give you all the numbers in the list with no equivalent in your other table.

    Hope this helps a bit

    Michael

  3. #3
    Join Date
    May 2002
    Posts
    28

    but see comment

    Hi,
    yes that what i want but i that table you create how u will put number from 1 to 100 for example.I must enter whose number to compare
    with the orginal i have.I hope you show way also to create number from
    1 upto 100.


    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    you could fill the table with a loop once and use it again and again then if applicable

    Code:
    function fillIDNoList(ByVal vintStartValue, ByVal vintEndValue)
    
    dim dbs as database
    dim rst as recordset
    
    dim i as long
    
    set dbs = currentdb()
    set rst = dbs.openrecordset( _
        "IDNoList", dbopendynaset, dbappendonly)
    
    for i = vintStartValue to vintEndValue
        
        rst.addnew
        rst!IDNo = i
        rst.update
    
    next i
    rst.close
    dbs.close
    set rst=nothing
    set dbs=nothing
    
    end function

  5. #5
    Join Date
    May 2002
    Posts
    28

    Hi Apel

    Plz if you kindly tell where to put your code.is it in model
    or where.


    Thanks alot for you kind help

  6. #6
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    You would only need to run it once to build the table. Create the table, then paste the code into a new module run it. The parameters specify the start and end number, so if your call is:

    fillIDNoList 1, 100

    it will fill the table with the values 1 to 100

  7. #7
    Join Date
    May 2002
    Posts
    28

    Hi apel

    1-I have table call TABLE
    2-In table I create idnolist
    3-I past your code in model
    4-I want to know how i run and who the will connection bewteen my table and ny funcation.


    You can call byyahoo massanger ssalih200
    for discuss.


    thanks alot for you kind help

  8. #8
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    sorry, have no yahoo messanger.

    open the immediate window (hit ctrl+g) and type: fillIDNoList 1, 100 and hit return. After a few secs it should be finished.

Posting Permissions

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