RE: [MS_AccessPros] Using ISNULL in a query

I think I would use
Nz([employee name],VendorName)

Duane Hookom

To: MS_Access_Professionals@yahoogroups.com
From: john@viescas.com
Date: Thu, 3 Nov 2011 08:36:43 +0100
Subject: RE: [MS_AccessPros] Using ISNULL in a query

I would do:

IIf(IsNull([VendorName]),[Employee Name], [VendorName]) AS Vendor

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

-----Original Message-----

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Stuart Luckman

Sent: Thursday, November 03, 2011 3:23 AM

To: MS_Access_Professionals@yahoogroups.com

Subject: RE: [MS_AccessPros] Using ISNULL in a query

Forgot an extra ) at the end of Employee Name:

IIf(IsNull([VendorName]),[Employee Name], IIf(IsNull([Employee

Name]),[VendorName), [VendorName] & ", " & [Employee Name])) AS Vendor

Stuart

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art

Sent: Thursday, 3 November 2011 11:49 AM

To: MS_Access_Professionals@yahoogroups.com

Subject: [MS_AccessPros] Using ISNULL in a query

I have the following query:

SELECT tblTransactions.TransactionID, tblAccount.AccountNumber,

tblAccount.AccountName, tblTransactions.TransactionDate,

tblTransactions.TransactionNumber, tblTransactions.CheckNumber,

tblTransactions.InvoiceNumber, tblTransactions.PONumber,

tblTransactions.VoucherNumber, tblTransactions.TransactionMemo,

IIf(IsNull([VendorName]),[Employee Name] & ", " & [Employee

Name],[VendorName]) AS Vendor

FROM ((tblTransactions LEFT JOIN tblAccount ON tblTransactions.AccountID =

tblAccount.AccountID) LEFT JOIN tblVendor ON tblTransactions.VendorID =

tblVendor.VendorID) LEFT JOIN qryEmployeeExtended ON

tblTransactions.EmployeeID = qryEmployeeExtended.EmployeeID;

This piece of it is causeing me a headache:

IIf(IsNull([VendorName]),[Employee Name] & ", " & [Employee

Name],[VendorName]) AS Vendor

What I want is it to display the Vendor Name is the Employee name is blank

or the Employee name id the Vendor name is blank. Right now if the vendor

name is blank it repeats the employee name twice. I know I am close but can

someone pushe me over the hump?

Thanks,

Art Lorenzini

Sioux Falls, SD

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/