Null Value in table need Value from Second field in record

[billk]

I have a table that I'm working with and explaining maybe be a little tough for me.
The table has USER_ORD_NUM and INVOICE_NO



The table I have shows one User_Ord_NO that matches to an invoice number. Afterward I have a series of parts that fill in that only show the invoice number with the USER_ORD_Number being blank. I tried to filter the results based on USER_ORD_NUMBER where the number contains the word "AS" which in no way will work for my dilemma.

Is there any way I can rifle through the records find the USER_ORD_NUM match it to an INVOICE_NO and then change filtering to use INVOICE_NO which would then create my report. I would like to have one form with two buttons one to print a report without "AS" orders and the other to print only "AS" orders.

I would use just the INVOICE_No but the invoice number doesn't have any keywords I could use to separate all orders from "AS" orders. The access program I'm working with was created some time ago by another person and a complete rework would take too much time.

The reason I need to do this is because our assembly (AS) orders are pulling in all parts that go into making the assembly with a UNIT_PRICE of 0.00. So the current report will not work for invoicing because of all the line items being shown and causing confusion with our customers
.
THE DATA IS BEING DATAMINED FROM AN OLDER ERP SYSTEM into a table within access. The USER_ORD_NUM isn't carried entirely accross all tables in the ERP system. However as you can see with the example the INVOICE_NUMBER carried nicely.

USER_ORD_NUM, | INVOICE_NO | PART| UOM| UNIT_PRICE
10AS111 | 111111 | 123 | 23 | 230.00
NULL | 111111 | 222 | 20' | 180.00
NULL | 111111 | 333 | 10' | 100.00
10FB21 | 222222 | 444 | 20' | 150.00
NULL | 222222 | 555 | 20' | 170.00
NULL | 222222 | 666 | 10' | 100.00
10FB22 | 333333 | 444 | 20' | 150.00
NULL | 333333 | 777 | 5' | 150.00
10AS333 | 444444 | 888 | 1' | 50.00
NULL | 444444 | 999 | 2' | 60.00

[ssanfu]

I'm not sure I understand, but maybe something like the attached mdb???

[billk]

Thanks for the reply however I already figured out a solution.

In the query criteria for invoice_no I put the following statement: In (select invc_no from Tablename where user_ord_no like '*AS*') I then the unit_price field query criteria >0.

This pulled in all the invoice in correlation with Like "AS" (assembly) regardless if the user_ord was null and eliminated all line items with a price of 0.00

I reversed my criteria so I could pull in all NON-AS invoices on a second query showing all items except "AS" orders and included all line items with 0.00 unit price.

I made two queries one for AS and one for NON-AS and two Reports. I have one form with two buttons one to preview AS orders and one to preview NON-AS numbers


The method is working nicely. See the attached very generic access file I made up to show the example.

Regards,

[ssanfu]

Great!

However, I modified the mdb to demonstrate another way to have one report/one query to be use several times if the fields and layout are the same, but only the selected records are different.

I have one query and one report in my mdb that is used to generate 4 reports, differentiated by division. (same data..different dates and /or division).

This really cuts down on the number of queries/report I have to modify...

Anyway, just wanted to show another method.