Query Omits Selected Field

[Rick4077]

If the [Second Owner First Name] field is null, then nothing it returned in the [OwnerName] field.

How can I fix this?

Thanks . . . Rick

SELECT Castille.[First Owner First Name] + " "+"&" +" "+ Castille.[Second Owner First Name] +" "+ Castille.[First Owner Last Name] AS [OwnerName]
FROM Castille

 

[PHV]

This is standard behaviour for the + used as concatenation operator.
Either use the & operator or play with the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Thanks . . . I'll try it.

Rick

 

[Rick4077]

Okay here are my text fields

First Owner Full Name
First Owner First Name
First Owner Last Name
Second Owner Full Name
Second Owner First Name
Second Owner Last Name

NZ as far as I can determine (with my limited knowledge) does not work well in Queries.

I'm trying to query . . .

SELECT [First Owner Fisrt Name]+" & " &
       [Second Owner First Name] +" " &
       [First Owner Last Name] AS [OWNER]
From Castille as C

If there is no Second Owner First Name I get First Owner First Name & First Owner Last Name. Like Rick And Williams

Other results are Rick & Kathy, no last name etc . . .

If just one of the fields are null it throws the results off as deescribed.

Ideas???

Rick

 

[lespaul]

NZ works fine in queries. Is this what you tried?

SELECT [First Owner Fisrt Name]+" & " &
NZ([Second Owner First Name], "") +" " &
[First Owner Last Name] AS [OWNER]
From Castille as C

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Rick4077]

Hi Leslie . . .

Just what is NZ doing in this exapmle?

It works fine where there are two First Names, ie. husband and wife, but if there are only one owner (no second owner first neme) it returns names like Joey & Williams in lieu of Joey Williams.

Somehow in the query I have to say . . . if there is no second owner first name then drop the " & " and the the second owner first name field, and just use the first owner second name field.


This is very tricky!

Any ideas?

 

[Golom]

SELECT ([First Owner First Name] & 
        IIF(IsNull([Second Owner First Name]), "", 
            " & " & [Second Owner First Name]) & " " &
        [First Owner Last Name]) AS [OWNER]
From Castille as C

 

[Rick4077]

Darn this looks good Golom but I keep getting "wrong number of arguments" for this part . . .

([First Owner First Name] & 
        IIF(IsNull([Second Owner First Name]), "", 
            " & " & [Second Owner First Name])

I'v tried switching a few things but can't seem to fihgure it out. Doesn't look to complicated. Ha!

Rick

 

[Rick4077]

Okay it's working for FIRST OWNER FIRST NAME and SECOND OWNER FIRST NAME but it's not adding FIRST OWNER LAST NAME to the end.

Castille.[First Owner First Name]+" "&  IIF(IsNull(Castille.[Second Owner First Name]), "", "& " & Castille.[First Owner Last Name]) AS [OWNER NAME],

What could it possibly be?

Rick

 

[PHV]

SELECT [First Owner Fisrt Name] & (" & " + [Second Owner First Name]) & (" " + [First Owner Last Name]) AS [OWNER]
FROM Castille

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

This is taken right out of Access 2003 help file on Can Shrink Can Grow but it fails in my field control source in my report.

Trim([First Owner First Name] & ("& "+[Second Owner First Name]))

Any ideas . . ?

Thanks . . . Rick

 

[Rick4077]

You know . . . CanShrink/CanGrow are for verticle movement on a field.

What about sizing the field horizontally to expand or contract based on the amount of text in the field.

Al would shrink from William, and whereas william would grow from Al.

How is this accomplished in Access 2003?

Thanks . . . Rick