missing numbers

muzori · #1 (**permalink**) 05-27-02, 07:15

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

Apel · #2 (**permalink**) 05-27-02, 09:51

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

muzori · #3 (**permalink**) 05-27-02, 11:32

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

Apel · #4 (**permalink**) 05-28-02, 03:30

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

muzori · #5 (**permalink**) 05-29-02, 02:37

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

Thanks alot for you kind help

Apel · #6 (**permalink**) 05-29-02, 03:31

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

muzori · #7 (**permalink**) 05-29-02, 04:36

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

Apel · #8 (**permalink**) 05-29-02, 05:33

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech