Referencing CDbl Value in Stored Query from a new query

[xtcal]

Hello - am hoping someone can help with the below query.

I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.

I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.

I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?

Thanks!

 

[Jon K]

When there's no match, CDbl() works on a Null value and returns #Error.


You can put an embedded Nz() function in the converted ID so that when there's no match, it returns a zero.

ConvertedID: CDbl(Nz([ID]))

The Find Unmatched Query:-
SELECT [Table 2].*, [qryTable 1].*
FROM [Table 2] LEFT JOIN [qryTable 1] ON [Table 2].ID=[qryTable 1].ConvertedID
WHERE [qryTable 1].ID Is Null;


Note the Where Clause in the above query.

As now ConvertedID is never null, we can't put WHERE [qryTable 1].ConvertedID Is Null there. We have to put another field from [qryTable 1] and I have chosen the original ID text field.

Alternatively, we can put WHERE [qryTable 1].ConvertedID =0.
.

 

[xtcal]

Hi Jon - thanks for your response. The #Errors are now converting to 0s, and I changed my new query syntax to find those IDs that have converted to 0s. Works like a charm! :> Thanks again.