Query Field Appearing Empty When Using Criterea

[kd2017]

I have two similar queries, identical SELECT clauses, the only difference is that one has a WHERE clause while the other does not.

There is one record that both queries correctly retrieve, HOWEVER one of the fields in the query with the WHERE clause is mysteriously blank???



Here is a screenshot of the returned data and their SQL code. Let me know if you would like me to upload the database.
(This is Access 2016, latest updates as of today didn't fix the problem.)



FYI: The query in question is based off of a UNION query. There are two types of "billings", pay applications and invoices, each have their own table, their own reports, their own problems... Hence the null PayAppID OR InvoiceID depending on which table the data came from. Note that the record in question comes from the invoice table and InvoiceID SHOULD NOT be null.

[rpeare]

try

try using either ISNULL or NOT ISNULL in your criteria rather than using > 0

[kd2017]

That didn't work. The receivable field shouldn't ever return a null anyway...

In case it's relevant: From the previous UNION query that the query in question selects from:
Receivable = InvoiceAmount - Payments
where
InvoiceAmount = IIF( [some field] is null, 0, [some field] )
Payments = IIF( [some other field] is null, 0, [some other field] )

SELECT qryPayAppAmounts.PayAppID, null AS InvoiceID, qryPayAppAmounts.InvoiceNumber, qryPayAppAmounts.MonthWorked,
IIF(qryPayAppAmounts.InvoiceAmount is null, 0, qryPayAppAmounts.InvoiceAmount) AS InvoiceAmount,
IIF(qryPayAppPayments.Payments is null, 0, qryPayAppPayments.Payments) AS Payments,
qryPayAppAmounts.InvoiceAmount - Payments AS Receivable,
qryPayAppAmounts.IsPrivate, qryPayAppAmounts.Tier
FROM qryPayAppAmounts LEFT JOIN qryPayAppPayments ON qryPayAppAmounts.PayAppID = qryPayAppPayments.PayAppID

UNION ALL
SELECT null AS PayAppID, qryInvoiceAmounts.InvoiceID, qryInvoiceAmounts.InvoiceNumber, qryInvoiceAmounts.MonthWorked,
IIF(qryInvoiceAmounts.InvoiceAmount is null, 0, qryInvoiceAmounts.InvoiceAmount) AS InvoiceAmount,
IIF(qryInvoicePayments.Payments is null, 0, qryInvoicePayments.Payments) AS Payments,
InvoiceAmount - Payments AS Receivable,
qryInvoiceAmounts.IsPrivate, qryInvoiceAmounts.Tier
FROM qryInvoicePayments RIGHT JOIN qryInvoiceAmounts ON qryInvoicePayments.InvoiceID = qryInvoiceAmounts.InvoiceID;

[rpeare]

I was talking more about your invoiceID field, where you actually are applying the criteria.

[kd2017]

I'm applying the criteria to the Receivable field. I want to return unpaid invoices.

[June7]

The criteria is applied to Receivable field, not InvoiceID.

This is bizarre. Think I will need the db to analyze issue.

[rpeare]

Sorry I took this:

Note that the record in question comes from the invoice table and InvoiceID SHOULD NOT be null.

to mean you wanted only non null invoiceID records.

my guess is that you have a record appearing multiple times in the union query (one for both receivable and payable), but as june said, probably need an example of this database to find out why.

[kd2017]

See attached. The problem query is Query 1 under Unassigned Objects category. It pulls data from all the queries under the Lien Deadlines category.

[rpeare]

I think there is a non printable character gumming up the works. I am not sure how that's getting into your query because the data looks clean right up until the point you generate query1 but attached is the screenshot I get when I run the query.

I can get rid of the non printable characters using the TRIM function

Try using TRIM(INVOICEID) instead of just INVOICEID. Your behavior is different than mine (you don't see anything, I see a hex character) but it should work with the TRIM function.

[kd2017]

Interesting. So then maybe the db engine is having trouble figuring out the datatype of the field? I went back to the UNION query and changed it to return 0 instead of null for PayAppID and InvoiceID respectively (when appropriate). Now Query 1 works as I've told it to. But this feels like a hack, I hesitate to call this solved... It returns IDs that in fact don't exist in the related tables.



I tried using TRIM as you suggested, it makes the InvoiceID appear but it's returned as a string instead of a number. CLng(Trim(... fixes that but then we get error's on the null fields. I suppose we could do IIF( InvoiceID IS Null, Null, CLng(Trim(InvoiceID))) ... this is getting hairy. Would I need to do this on the PayAppID field too?

IIF(qryPayAppsAndInvoices.InvoiceID IS NULL, NULL, CLng(Trim(qryPayAppsAndInvoices.InvoiceID))) AS InvoiceID

[June7]

The queries all run fine for me, no weird characters.

Remove ALL from the UNION ALL and there is still a record for InvoiceNumber 105 but there is no InvoiceID and no weird character. So now to determine where this record comes from.

Never had issues with UNION queries.

[rpeare]

I wouldn't use cint, I'd use clng if you intend to do anything with the ID field. You also do not need to convert it to a zero. For instance you can just do InvID: clng(trim(InvoiceID)). I'll look at it tonight but I can't answer about PAYAPPID, I suspect it will but I am not basing that on anything other than the source is a union query which sometimes behave weirdly.

[kd2017]

This bothers me a little bit. I'm thinking I'm going to take a different approach entirely in order to avoid the unions all together...