Left Join nonexistent date field shows #Error

[Amelia]

Table1: DummyID, Name, City
111, John, Chicago
233, Nancy, Omaha
422, Sam, Cincinnati

Table2: DummyID, StartDate, Quantity
111, 02/11/2002, 53
233, 03/12/2003. 75

SELECT Table1.DummyID, Table1.Name, Table2.StartDate,
Table2.Quantity
FROM Table1 Left Join Table2 ON (Table1.DummyID =
Table2.DummyID)

Results are 3 records
111, John, 02/11/2002, 53
233, Nancy, 03/12/2003. 75
422, Sam, #Error,

I've tried converting null values to zero:
SELECT Table1.DummyID, Table1.Name, CDate(Nz
(Table2.StartDate,0)), CLng(Nz(Table2.Quantity,0))
FROM Table1 Left Join Table2 ON (Table1.DummyID =
Table2.DummyID)

This is an approach that I've used before, but it is not
working this time.
Results are 3 records
111, John, 02/11/2002, 53
233, Nancy, 03/12/2003. 75
422, Sam, #Error, 0

How can I get rid of the #Error result. Zero is an
acceptable value.

I've tried
IIf(IsNull(Table2.StartDate),0,Table2.StartDate)
IIf(IsMissing(Table2.StartDate),0,Table2.StartDate)
IIf(IsError(Table2.StartDate),0,Table2.StartDate)

All of these result in a #Error value Table2.StartDate,
where the Table1.DummyID is not in Table2.

Any suggestions?

 

[Michel Walsh]

Hi,


Try

Nz( fieldName, CDate(0) )

rather than

CDate( Nz( fieldName, 0) )


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

Are you attempting to apply any formatting to the StartDate or do any
manipulation of it? What you've shown us appears to be an example and not the
real query.

 

[amelia]

I tried your suggestion, but the results are the same.

The date field for records in Table1 not Table2 appears as
#Error.

Thanks, anyway.

 

[Michel Walsh]

Hi,


Can you send me the two tables and the query you use? vanderghast 'at'
msn dot com can be used.

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Table2 is actually a query that converts a text value to a
date formatted value if the string is not "99/99/99"
(placeholder from data provider for empty date field).
SelectDate:IIf([zttxtMailQty]![SelectDateText]
="99/99/99",Null,CDate([zttxtMailQty]![SelectDateText]))
or
SelectDate:IIf([zttxtMailQty]![SelectDateText]
="99/99/99",0,CDate([zttxtMailQty]![SelectDateText]))
(I've experimented with setting the date value
for "99/99/99" to Null and to 0, but neither has an impact
on the #Error result in the subsequent query)

Note that there is not problem with the number fields
(Long).