Left Join nonexistent date field shows #Error


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?
 
Ad

Advertisements

M

Michel Walsh

Hi,


Try

Nz( fieldName, CDate(0) )

rather than

CDate( Nz( fieldName, 0) )


Hoping it may help,
Vanderghast, Access MVP
 
J

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.
 
A

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.
 
M

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
 
Ad

Advertisements

G

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).
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top