Data Type Mismatch - Concatenated Field

[Guest]

Hello,

I have concatenated two fields, both of the Text Data type. When I do an
unmatched query to check this concatenated field in two recordsets, the
expected results come up for a few seconds, at which point I get the infamous
"Data Type Mismatch in Criteria Expression" error, and all of the data in the
query turns to #Name?

What is going on here?

TIA,
Harry

 

[Guest]

Could be nulls:

Debug.Print "ABC" & Null returns ABC

Debug.Print "ABC" + Null returns Null

Then there's the possibility of a corruption issue. Try a compact and repair.

 

[Guest]

Hi Jerry,

Thank you for the reply. Compacted and repaired, and that didn't help
(Also this DB is a week old with very little data in it so corruption is
unlikely).

Can you expand a bit on the nulls - how can I dance around this - should i
query the two record sets and eliminate nulls before comparing?

Thanks,
Harry

 

[Guest]

Post the SQL where you do the compare.

 

[Guest]

You might try wrapping both field with the Nz function in the concatenation

Concat: Nz(FirstField,"") & Nz(SecondField,"")

 

[Guest]

This just caused the Data Type Mismatch error to appear before the query
finished running.

Here's the SQL...


SELECT qryNewAccountsReconciliation1.cconsol,
qryNewAccountsReconciliation1.Screen, qryNewAccountsReconciliation1.Field,
qryNewAccountsReconciliation1.Xref, qryNewAccountsReconciliation1.Value,
qryNewAccountsReconciliation1.[Changed From],
qryNewAccountsReconciliation1.[Changed To],
qryNewAccountsReconciliation1.[Changed By],
qryNewAccountsReconciliation1.[Changed At],
qryNewAccountsReconciliation1.[Account Name],
qryNewAccountsReconciliation1.[AW Reference]
FROM qryFulfilledNewAccounts RIGHT JOIN qryNewAccountsReconciliation1 ON
qryFulfilledNewAccounts.xrefpair = qryNewAccountsReconciliation1.xrefpair
WHERE (((qryFulfilledNewAccounts.xrefpair) Is Null));

Thanks,
Harry