ACCESS FEED: RE: [MS_AccessPros] Using ISNULL in a query

Kamis, 03 November 2011

RE: [MS_AccessPros] Using ISNULL in a query

Duane's NZ suggestion would be slightly more efficient, but perhaps don't mess with success!

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 Art Lorenzini
Sent: Thursday, November 03, 2011 1:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Using ISNULL in a query

This is the final statement:

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], [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;

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

I think I was just overthinking the whole thing...

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: Stuart Luckman <stuartluckman@alpinenurseries.com.au>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 2, 2011 9:23 PM
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]

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

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

Yahoo! Groups Links