Solved

SQL If Statement in Access Query

Posted on 2008-10-23
3
644 Views
Last Modified: 2013-11-27
I'm not sure what I am doing wrong, but here is my SQL Code within my Access Query:

SELECT Count(qryVendorX.[Item #]) AS [TotalItems]
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName
HAVING (((qryVendorX.VendorNumber) = [forms]![frmPOTransHeader].[Vendor #]))

IIF (qryVendorX.[TotalItems])>0
SELECT qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description, qryVendorX.[Vendor Item #]
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description, qryVendorX.[Vendor Item #]
HAVING (((qryVendorX.VendorNumber)=[forms]![frmPOTransHeader].[Vendor #]))

ELSE
SELECT tblItemMaster.[Item #], [Department] & "-" & [Account] & "-" & [Sub-Account] AS [G/L Account], tblItemMaster.Description
FROM tblItemMaster
ORDER BY tblItemMaster.Description;

My goal is to display a list of items specific to vendors if the information is in the Vendor Cross Reference Table.  If the Vendor is not in the cross reference table, just display all Items.  Any help is greatly appreciated.

Paula
0
Comment
Question by:Monterey
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22791981
You would have to use this syntax in MS Access:

IIF(condition, true-statement, false-statement)

If you are trying to display a full different query based on some condition, you may be better off moving to VBA code and then it is of syntax:

IF condition THEN
    'true-statement
ELSE
    'false-statement
END IF
0
 

Author Comment

by:Monterey
ID: 22843281
Thank you for the comment.  I didn't even think to go that way.  I am trying to populate a lookup based on the condition being met with one query and if not a different query.  I was thinking that I had to make the query do the work rather than the event on the lookup.  Thanks again.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22844518
No problem.  You are most welcome.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question