I'm in Not Responding Rut

[twinnyfo]

My code was just to replace certain parts of your code--not a complete replacement.

Line 1 should be added to your declarations.

Lines 3-8 replace your 16-17.

Lines 10-11 replace your line 22.

My apologies for not making that more clear.

[MC42015]

twinnyfo - I have made GREAT progress! All of your tips to loop me through Access land and simplify this data into a concise database! So happy I achieved what you and I worked on!
However, I found some missing info that I am now trying to fix.....Grr.
My new problem is data type mismatch in union query.

In my invoice table from the client - they include all fields on every record for that invoice, the sales rep number,name and the customer number, name. The invoices are made up of five categories: Credits, cost Open, Freight, 100%, 50/50.
My goal being that I am trying to report to the sales rep how much commission they have billed (that is the expression I add, the client does not) and when the commission is due - I have to peel through those five categories from one table.

I need to query missing info in that I maintain to do my expressions on the five categories -
Sales rep for commission rate
Customer for terms, currency, and discounts

first three removed from invoice table, I union the invoice table three times w/three sets of criteria:
credits = doesn't need commission or due date, and sales rep field is blank
cost open = can't calculate commission because there is no cost
freight = does not pay commission
*desired results

I query the invoice table against that union to return InvID that is not in the union, then step through select query 1 adds in customer info fields, then select query 2 adds in sales rep info fields
From select query 2, I separate to two more select queries:
100% is commissioned at 100% of the profit
50/50 is commissioned at 50% of the profit for two different sales rep
*desired results

I the query I need to send weekly, the sales rep should see all five categories as a table with the expression I've added. I want to bring the five categories back together after expressions in a union query.

The union query is erroring data type mismatch in criteria expression.
Each query runs with desired records, accurate expression, etc.
It will not query any of them together. The two most similar structure, 100% and 50/50, won't union and neither of them will union to the first group!

*How can I update the invoice table to show the SalesRep Number for the credits? Is it a problem to pull that field in from customer instead of invoice?
*What would cause this error when the fields are same data type?
*Am I approaching the union backwards?

SELECT [2SalesOrderChar geRFS].BOIID, [2SalesOrderChar geRFS].CustID, [2SalesOrderChar geRFS].CustName, [2SalesOrderChar geRFS].PrimaryRep, [2SalesOrderChar geRFS].SalesRep2, [2SalesOrderChar geRFS].CustPO, [2SalesOrderChar geRFS].InvoiceNumber, [2SalesOrderChar geRFS].PGOrder, [2SalesOrderChar geRFS].CreditMemo, [2SalesOrderChar geRFS].InvoiceDate, [2SalesOrderChar geRFS].ShipDate, [2SalesOrderChar geRFS].InvoiceBalance , [2SalesOrderChar geRFS].InvoiceAmt, [2SalesOrderChar geRFS].TotalTax, [2SalesOrderChar geRFS].TotalMiscChrgs , [2SalesOrderChar geRFS].TotalCost, [2SalesOrderChar geRFS].InvcAge, [2SalesOrderChar geRFS].CostCompleteFl ag,[2SalesOrderChar geRFS].ExpiredDeferra l,[2SalesOrderChar geRFS].MemoDescriptio n, [2SalesOrderChar geRFS].MemoText, [2SalesOrderChar geRFS].InvSplit, [2SalesOrderChar geRFS].Terms, [2SalesOrderChar geRFS].Discount, [2SalesOrderChar geRFS].CURRENCYCODE, [2SalesOrderChar geRFS].DUE, [2SalesOrderChar geRFS].CommRate, [2SalesOrderChar geRFS].OC, Null As PROFIT, Null As ESTCOMM
FROM 2SalesOrderChar geRFS
UNION
SELECT [2SalesOrderChar ge].BOIID, [2SalesOrderChar ge].CustID, [2SalesOrderChar ge].CustName, [2SalesOrderChar ge].PrimaryRep, [2SalesOrderChar ge].SalesRep2, [2SalesOrderChar ge].CustPO, [2SalesOrderChar ge].InvoiceNumber, [2SalesOrderChar ge].PGOrder, [2SalesOrderChar ge].CreditMemo, [2SalesOrderChar ge].InvoiceDate, [2SalesOrderChar ge].ShipDate, [2SalesOrderChar ge].InvoiceBalance , [2SalesOrderChar ge].InvoiceAmt, [2SalesOrderChar ge].TotalTax, [2SalesOrderChar ge].TotalMiscChrgs , [2SalesOrderChar ge].TotalCost, [2SalesOrderChar ge].InvcAge, [2SalesOrderChar ge].CostCompleteFl ag, [2SalesOrderChar ge].ExpiredDeferra l, [2SalesOrderChar ge].MemoDescriptio n, [2SalesOrderChar ge].MemoText, [2SalesOrderChar ge].InvSplit, [2SalesOrderChar ge].Terms, [2SalesOrderChar ge].Discount, [2SalesOrderChar ge].CURRENCYCODE, [2SalesOrderChar ge].DUE, [2SalesOrderChar ge].CommRate, [2SalesOrderChar ge].OC, ([InvoiceAmt]-Nz([TotalTax]))-[TotalCost] AS PROFIT, ([PROFIT]*[CommRate])-[OC] AS ESTCOMM
FROM 2SalesOrderChar ge
WHERE ((([2SalesOrderChar ge].SalesRep2) Is Null) AND (([2SalesOrderChar ge].InvSplit) Is Null));
UNION
SELECT [2SalesOrderChar ge].BOIID, [2SalesOrderChar ge].CustID, [2SalesOrderChar ge].CustName, [2SalesOrderChar ge].PrimaryRep, [2SalesOrderChar ge].SalesRep2, [2SalesOrderChar ge].CustPO, [2SalesOrderChar ge].InvoiceNumber, [2SalesOrderChar ge].PGOrder, [2SalesOrderChar ge].CreditMemo, [2SalesOrderChar ge].InvoiceDate, [2SalesOrderChar ge].ShipDate, [2SalesOrderChar ge].InvoiceBalance , [2SalesOrderChar ge].InvoiceAmt, [2SalesOrderChar ge].TotalTax, [2SalesOrderChar ge].TotalMiscChrgs , [2SalesOrderChar ge].TotalCost, [2SalesOrderChar ge].InvcAge, [2SalesOrderChar ge].CostCompleteFl ag, [2SalesOrderChar ge].ExpiredDeferra l, [2SalesOrderChar ge].MemoDescriptio n, [2SalesOrderChar ge].MemoText, [2SalesOrderChar ge].InvSplit, [2SalesOrderChar ge].Terms, [2SalesOrderChar ge].Discount, [2SalesOrderChar ge].CURRENCYCODE, [2SalesOrderChar ge].DUE, [2SalesOrderChar ge].CommRate, [2SalesOrderChar ge].OC, ([InvoiceAmt]-Nz([TotalTax]))-[TotalCost] AS PROFIT, (([PROFIT]*[CommRate])-[OC])/2 AS ESTCOMM
FROM 2SalesOrderChar ge
WHERE ((([2SalesOrderChar ge].SalesRep2) Is Not Null)) OR ((([2SalesOrderChar ge].InvSplit) Is Not Null));