Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: How would i write this query?

    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.

  2. #2
    Join Date
    Oct 2003
    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")
    Exit Sub
    end if

    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....

    Good Luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts