[Access] How to obtain non-null data from a recordset?

[spidermman]

I have a recordset(?lifting program?) which contains 60 fields of data.
Between the fields of 25 to 40, it contains the quantity of products which are being sold to the customer. However, in these fields, it only contains records of those products that are required by the customers. For those products that are required by the customer, it will show null records.

For example, if the customer requires product D and product H, then for field28 which is product D, the record will show 1000 and field32 which is product H, the record will show 800. The rest of the fields (between 25 to 40) will have null records.

For each row, there is a unique shipment_no (field1) being assigned. There is a max of only 8 non-null datas from each row.
How do you obtain the non-null data from the fields between 25 to 40 for shipment_no = 3471 ? I would like to display the non-null data on controls in a form.

Thanks.




Private sub extract_Click()
Dim db As Database Dim Rst As Recordset Set db = CurrentDB() Set Rst = db.OpenRecordset("lifting program")

With Rst
.findfirst ( ?shipment_no? = 3471)

? how to write the loop to obtain non null data
? For .fields(25) to .fields(40)
? Me.txt_pd1 = 1st non-null record
? Me.txt_pd2 = 2nd non-null record
? ?.
? Me.txt_pd8 = 8th non-null record
? Next

End With

End Sub

[CreganTur]

Simple answer: base your recordset on a query instead of the entire table.

Explination: In your db.OpenRecordset() method, you write out a SQL statement that pulls records according to your specifications. Test out your query in Query Design mode first to make sure it pulls what you want. Then take the query's SQL and put it in your OpenRecordset() method as a string value.

HTH:thumb