dlookup - exclude a particular option

[tagteam]

I have a query that searches for all line items before a certain date. I use that in a report that is grouped by invoice number. I then use an unbound field and dsum to add up the values for a particular code. This all works fine for my users.

However, my users want to also see the customer associated with that invoice. The problem is that these are third party invoices and some is paid by my company and some by my customer. If I use dlookup it will only give me , i believe the first one. Sometimes the customer will be the first line item and sometimes my company will be.



I need a way to look up the name other than my company UNLESS my company is the only name on that invoice.

Is this possible with a dlookup or will i need to rebuild this whole report some other way?

[Bulzie]

So is there one field that will either list your company name or a customer name? Can you give a sample of your data and another of how you want it to appear.

[aytee111]

Create a query that contains the customers where it is NOT your company. Then left join that query to your report record source and have an IIf statement which states that if the customer from this new query is null then use your company, otherwise use the customer.

[tagteam]

data is like this
invoice responsibilitycode customer amount
a232 1 us 25
a232 6 company1 30
c334 1 us 54
c334 6 company2 85
c334 6 company2 50
d223 1 us 45

Data is like that with a few other fields
The report my users want is:
InvoiceNumber OurCost TheirCost TheirCompanyName TotalInvoice

I have tried to do a query with a left join but it ends up creating duplicates of a bunch of the records and the totals are way off.
You can see from the sample data there are three scenarios for payment.
1. All us
2. All them
3. Split between the two

I have done a dsum to get the amounts =Sum(IIf([UpdatedResponsibilityCode]=1,[NetCharge],0))

However, I cant get their company name to show up consistently because dlookup just picks one of them and since the single line is a summary of the lines the summary just picks one too.

The query idea seems like it may be on the right track but I am probably just doing it wrong.

[aytee111]

The query will produce these results:
a232 company1
c334 company2

Now when you left join this query in with the main one, joining on invoice, at times invoice will be null such as d223. This should not affect anything that your query already does as it is only interested in the company name.