Solved

SQL If Statement in Access Query

Posted on 2008-10-23
3
642 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Query Challenge 15 48
Loop within Select Case 3 22
Access MDB/PDF 21 31
Multiple queries for a form 14 22
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now