Null fields in a query

[Michaelchessking]

I am trying to get a query to return all records even if the field is null.
The query has multiple tables and is only returning records with non null
fields. I am using the following line in the query. The result were the
same. I would be greatful for any help. Thank you Michael

Claim Number: IIf(IsNull([tblClaimant]![Claim Number]),"Missing
Data",[tblClaimant]![Claim Number])

 

[John W. Vinson]

I am trying to get a query to return all records even if the field is null.
The query has multiple tables and is only returning records with non null
fields. I am using the following line in the query. The result were the
same. I would be greatful for any help. Thank you Michael

Claim Number: IIf(IsNull([tblClaimant]![Claim Number]),"Missing
Data",[tblClaimant]![Claim Number])

Please post the SQL of the query. It may be a problem with the joins or with a
criterion - can't tell from this info!

Actually though... it sounds like you have circular referencing here; you're
defining the field Claim Number using the field Claim Number as a source. The
calculated field needs a different fieldname from the actual table field!

 

[John Spencer]

John,
As LONG AS you refer to the field with the syntax TableName.FieldName you
can alias the calculation with the same name as a field.

Valid - (at least in Access 97 and up)
Claim Number: IIf(IsNull([tblClaimant].[Claim Number]),"Missing
Data",[tblClaimant].[Claim Number])

Invalid - circular reference error
Claim Number: IIf(IsNull([Claim Number]),"Missing Data",[Claim Number])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am trying to get a query to return all records even if the field is
null.
The query has multiple tables and is only returning records with non null
fields. I am using the following line in the query. The result were the
same. I would be greatful for any help. Thank you Michael

Claim Number: IIf(IsNull([tblClaimant]![Claim Number]),"Missing
Data",[tblClaimant]![Claim Number])

Please post the SQL of the query. It may be a problem with the joins or
with a
criterion - can't tell from this info!

Actually though... it sounds like you have circular referencing here;
you're
defining the field Claim Number using the field Claim Number as a source.
The
calculated field needs a different fieldname from the actual table field!

 

[John W. Vinson]

As LONG AS you refer to the field with the syntax TableName.FieldName you
can alias the calculation with the same name as a field.

Eeeuwwww... I didn't know that, John!

Good to know, but I'd still be very leery of ever skating that close to the
edge.

 

[John Spencer]

I hardly ever use it, but it is a good piece of knowledge to have.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michaelchessking]

Thanks for the reply. Here is the SQL
SELECT tblEvent.[Trailer Number], tblEvent.[Tractor Number], tblEvent.ID,
[Master Employee List].Terminal, tblEvent.Memo,
IIf(IsNull([tblClaimant]![Claim Number]),"Missing Data",[tblClaimant]![Claim
Number]) AS [1Claim Number]
FROM ([Master Employee List] INNER JOIN tblEvent ON [Master Employee
List].[Driver Name] = tblEvent.[Driver Name]) INNER JOIN tblClaimant ON
tblEvent.ID = tblClaimant.EventID;

If it makes a difference the Claim Number is on a subform on the the parent
Event Form. That is how the information gets entered.

Thanks again Michael

 

[John W. Vinson]

If it makes a difference the Claim Number is on a subform on the the parent
Event Form. That is how the information gets entered.

First off, the ! delimiter works on forms but should not be used to delimit
tablenames from fieldnames - use . instead. Secondly, if you want to retrieve
records from tblEvent whether or not there is a corresponding record in
tblClaimant you must use a Left Outer join instead of the default INNER join.
Try

SELECT tblEvent.[Trailer Number], tblEvent.[Tractor Number], tblEvent.ID,
[Master Employee List].Terminal, tblEvent.Memo,
NZ([tblClaimant].[Claim Number],"Missing Data") AS [1Claim Number]
FROM ([Master Employee List] INNER JOIN tblEvent ON [Master Employee
List].[Driver Name] = tblEvent.[Driver Name]) LEFT JOIN tblClaimant ON
tblEvent.ID = tblClaimant.EventID;

 

[Michaelchessking]

Thanks for the fast response. I tried the SQL and got a Syntax error in join
operation. INNER JOIN tblEvent ON [Master Employee
List].[Driver Name]

[Master Employee List] was the highlighted section in the SQL syntax error.

Thanks again, Michael

If it makes a difference the Claim Number is on a subform on the the parent
Event Form. That is how the information gets entered.

First off, the ! delimiter works on forms but should not be used to delimit
tablenames from fieldnames - use . instead. Secondly, if you want to retrieve
records from tblEvent whether or not there is a corresponding record in
tblClaimant you must use a Left Outer join instead of the default INNER join.
Try

SELECT tblEvent.[Trailer Number], tblEvent.[Tractor Number], tblEvent.ID,
[Master Employee List].Terminal, tblEvent.Memo,
NZ([tblClaimant].[Claim Number],"Missing Data") AS [1Claim Number]
FROM ([Master Employee List] INNER JOIN tblEvent ON [Master Employee
List].[Driver Name] = tblEvent.[Driver Name]) LEFT JOIN tblClaimant ON
tblEvent.ID = tblClaimant.EventID;

 

[John W. Vinson]

Thanks for the fast response. I tried the SQL and got a Syntax error in join
operation. INNER JOIN tblEvent ON [Master Employee
List].[Driver Name]

[Master Employee List] was the highlighted section in the SQL syntax error.

Well, you know your table's structures and table fieldnames. I don't. Is there
a field [Driver Name] in the Master Employee List table? What is the intention
of this join? What are the actual contents of [Driver Name], and how is the
table tblEvent related to the table Master Employee List?