?
Solved

SQL If Statement in Access Query

Posted on 2008-10-23
3
Medium Priority
?
654 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 61

Accepted Solution

by:
Kevin Cross earned 2000 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 61

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

621 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