Left Join nonexistent date field shows #Error

  • Thread starter Thread starter Amelia
  • Start date Start date
A

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?
 
Hi,


Try

Nz( fieldName, CDate(0) )

rather than

CDate( Nz( fieldName, 0) )


Hoping it may help,
Vanderghast, Access 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.
 
I tried your suggestion, but the results are the same.

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

Thanks, anyway.
 
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
 
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).
 
Back
Top