Group or filter report by unbound field/control

[buratti]

I have a report that is an almost exact replica of an invoice form. The only difference is that the report has an unbound/calculated field that displays a shorter description of the first line/record in the reports subreport (the first item/product listed). i created that field only because I need a report of invoices (preferably in the exact same layout as the invoice form) and be able to filter by the first item/product in that invoice.

Example... Show me all invoices that include the product "coffee" as the first item.

I have one of two problems I cant figure out. First in a separate control (button) that opens the report I need to filter by that unbound field.

DoCmd.OpenReport "Invoices", acViewReport, , "[FirstItem] = 'Coffee'"

Upon clicking the control I get a dialog to enter parameter for FirstItem...
No matter what I do I cannot get rid of that dialog. I am assuming I am getting this "error" because the field [FirstItem] is not in the reports underlying query but is an unbound/calculated field, and hence has no value until a record is opened and no record is being opened to have a value to filter by.

I can live with a workaround of opening all records and just grouping by the field [FirstItem], but there is no option to group by that field. Again assuming that it is because it is an unbound field.

Does anyone have any suggestions on how to solve either of the above problems?

 

[Rich]

Use a query to get the value of the first item, base the report on the query

 

[buratti]

I did try that before posting here, but rather, it is not possible in my situation or I just couldn't figure it out. Here's a little more info...

The Main Invoices report (and form) is based on the [scheduled invoices] query which is based only on the [invoices] table. The Invoice subreport (and subform) is based on the [Invoice Line Item] query, which is based only on the [InvoiceLine] table.

For your suggestion, (and what I couldn't figure out) I would need to add a calculated field to the [Scheduled Invoices] query that looks up the value of the FIRST line item in the rather the [Invoice Line Item] query or [InvoiceLine] table with the same Invoice ID as the current record. That is my problem... I don't know how to reference that table or query without including it in this original query.
(Adding the table to the query technically works, but then I would have a line/record for each line item in the invoices, and I only want 1 record per invoice. Ex. If I have 3 invoices each with 3 line items, that query would now display 9 records, I wold need it to display only 3.)
Is it possible to reference this field without including the table in the query?
Added note: I don't think it makes a difference, but the unbound field in question is not just the exact value of what it's looking up. I use a right() and instr() functions to lookup a phrase within the string of a value in that field.

Did I make any sense in this response, or did I confuse people more???

 

[buratti]

Ok, I was able to add the field to the query using a dlookup function, but cant exactly get it working correctly. The Dlookup retrieves the correct value, but fills all of the records with the same value of whatever the first value is.
I used query design view and entered the following expression in the field section of the desigh grid:
Size: DLookUp("[InvoiceLineItemRefFullName]","[InvoiceLine]","[txnID] = [TxnID]")
I also tried different variations like:

Size: DLookUp("[InvoiceLineItemRefFullName]","[InvoiceLine]","[txnID] =" & [TxnID])
Returns error

Size: DLookUp("[InvoiceLineItemRefFullName]","[InvoiceLine]","[txnID] =" & " [TxnID]")
returns same as first variation above

Is anything wrong with this syntax?