I'm a beginner with SQL and i'm not quite sure how i would write a query which does the following:
I have three tables involved:
1) Program Info - Holds information about a credit card program. Program_ID is the key. Contains a field which points to Country_Code in country.
2) Countries - Holds a list of countries which the card program will be marketed in. Key is Country_Code. Holds a region code which points to the geographic region the country resides in called Region_Code
3) Regions - Holds 5 geographic regions in which a country may reside in such as Europe or Asia/Pacific. Primary Key is region_Code
For example, American Express Membership Rewards would contain a 1 under country_code pointing to the USA in the Countries table. USA would have a field pointing to North America in the Regions table.
My problem is this. I have a report which filters programs by geographic region, but i do not know how to detect if 0 programs exist in say, latin america or asia/pacific. If the user chooses to look up programs in Asia/Pacific i want to be able to display a msgbox to the user and canceling the report.
Since it is a one-Many relationship into a one-many relationship i cant code the SQL query that would do this. I wrote one which searched for 0 countries lying in region X but it is not a good way of accomplishing it. Thanks for your help ahead of time.
Okay so you want to display a Msgbox to user and Cancle the Report from runnning if there are no records in the recorset?
If that is what you are trying to do then try this:
(RS1 will be the Recordset)
(SQL will be a string with your SQL for the query that drives the report)
set RS1 = CurrentDB.OpenRecordset(SQL)
If RS1.recordcount = 0 then
Msgbox ("your message")
Put this code before you open the report. If the recordcount is 0 then it won't make to the code to open your report. Hope it helps...I am not sure I really understood the question in first place, but i tried....