looping through sql and skip if not result set found

dj59

Registered User.
Local time
Today, 15:30
Joined
Jul 27, 2012
Messages
70
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.

What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.

For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.

Then, in the loop vba code:
after item 1 is run, do an export of the results.
after item 2, do an export.
etc.

Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], 
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2  where ucase(a2.last_name) = ucase(a.last_name)
   and ucase(a2.first_name) = ucase(a.first_name)
   and a2.birth_dt = a.birth_dt
   and a2.south_index_id <> a.south_index_id
   and a.southwest_name_source_cd = [COLOR=red][enter cty id][/COLOR]
   and a2.southwest_name_source_cd <> [COLOR=red][enter cty id][/COLOR]
   and c.cnty_cd = [COLOR=red][enter cty id][/COLOR]
   and c.south_index_id = a.south_index_id
   and c.south_index_id = sp.south_index_id
   and sp.deceased_dt is null
   and sp.ms_pmi is null)
ORDER BY [a.LAST_NAME] & ', ' & [a.FIRST_NAME];

Any help would be greatly appreciated.
 
I don't think you need to use VBA. If you put your list of cities in a table, then you can just join that table to your query above and it will return the results for all cities in your list.

StepOne
 

Users who are viewing this thread

Back
Top Bottom