Simple If IsNull query question

Pitabread

Registered User.
Local time
Today, 01:06
Joined
Jun 25, 2009
Messages
13
I'm making a simple membership database for a nonprofit org. When a new donation is entered there is a drop down box that shows all of the current members in the database. It's populated by a query that pulls the names from a table...
my issue is that some of the donor name fields are blank because the donation was made by a company, so I want to use an if statement that will make the query pull the company name from the table if the donor name is blank.

This is in access 2007 and the current query is coded thus:
SELECT [Contributors Extended].ID, [Contributors Extended].[Contributor Name]
FROM [Contributors Extended]
ORDER BY [Contributors Extended].[Contributor Name];

What should I add to it?

Thank you in advance
 
SELECT [Contributors Extended].ID, Nz([Contributor Name], CompanyName) AS TheContributor
 
Thanks for replying so quickly, I tried that and I still got the same result...
First I tried it exactly as you typed it although I knew it needed editing
Next I tried:
SELECT [Contributors Extended].ID, Nz([Contributor Name], [Company]) AS Contributor
Then I tried:
SELECT [Contributors Extended].ID, Nz([Contributors Extended].[Contributor Name], [Contributors Extended].[Company]) AS Contributor
Then I tried:
SELECT [Contributors Extended].ID, IIf(IsNull([Contributors Extended].[Contributor Name]), [Contributors Extended].[Company], [Contributors Extended].[Contributor Name]) AS Contributor
...as well as a few more similar variations

Any idea what the problem might be?

Thanks again
 
If the contributor name could be a ZLS ("") instead of Null:

SELECT [Contributors Extended].ID, IIf(Len([Contributor Name] & "") = 0, [Company], [Contributor Name]) AS Contributor

FYI, the table name is optional, unless there are is than one table in the FROM clause, and even then is only required if the same field name exists in multiple tables.
 
Yeah, I was thinking that (that Nz or IsNull didn't apply to blank strings), but I wasn't sure nor did I know how to code it in SQL.
That worked perfectly, thanks for saving me from further aggravation...
+1 for pbaldy's rep... also nice atlas shrugged quote
 
No problem, and welcome to the site by the way!
 

Users who are viewing this thread

Back
Top Bottom