An Easier Way? Maybe an array?

[DonP]

Certainly there has to be an easier way than this! Can anyone help? I have several lists that need to be pasted into a single form variable with a comma in between. Right now with only four lists, I made a series of "if else" statements to try to cover all combinations but I want to add more lists which will be too much to do it this way. It works now but it's a mess. Thanks to anyone who has a better way to do it that an obviously non-programmer such as myself can do!

Don

[tt]
IF (request.form("ALL&quot = "0&quot then
CITY0 = "'Cupertino','Los Altos','Los Altos Hills','Mountain View','Palo Alto','Stanford','Sunnyvale','Campbell','Milpitas','Santa Clara','Los Gatos','San Jose','Monte Sereno','Saratoga','Gilroy','San Martin','Morgan Hill'"
ELSE
IF (request.form("NORTH&quot = "1&quot then
CITY1 = "'Cupertino','Los Altos','Los Altos Hills','Mountain View','Palo Alto','Stanford','Sunnyvale'"
END IF
IF (request.form("CENTRAL&quot = "2&quot then
CITY2 = "'Campbell','Milpitas','Santa Clara','Los Gatos','San Jose','Monte Sereno','Saratoga'"
END IF
IF (request.form("SOUTH&quot = "3&quot then
CITY3 = "'Gilroy','San Martin','Morgan Hill'"
END IF
END IF

IF CITY0 <> &quot;&quot; then
CITY = CITY0
ELSE
IF CITY1 <> &quot;&quot; AND CITY2 <> &quot;&quot; then
CITY = (CITY1 & &quot;,&quot; & CITY2)
ELSE
IF CITY1 <> &quot;&quot; AND CITY3 <> &quot;&quot; then
CITY = (CITY1 & &quot;,&quot; & CITY3)
ELSE
IF CITY2 <> &quot;&quot; AND CITY3 <> &quot;&quot; then
CITY = (CITY2 & &quot;,&quot; & CITY3)
ELSE
IF CITY1 <> &quot;&quot; then
CITY = CITY1
ELSE
IF CITY2 <> &quot;&quot; then
CITY = CITY2
ELSE
IF CITY3 <> &quot;&quot; then
CITY = CITY3
ELSE
IF CITY1 <> &quot;&quot; & CITY2 <> &quot;&quot; & CITY3 <> &quot;&quot; then
CITY = (CITY1 & &quot;,&quot; & CITY2 & &quot;,&quot; & CITY3)
ELSE
CITY=Request.Form(&quot;CITY&quot
END IF
END IF
END IF
END IF
END IF
END IF
END IF
END IF
[/tt]

 

[Sensibilium]

You could put the cities within a database, and then you can just query the correct data and then run through the recordset with a Do Until rsMyRecordset.EOF.

It would certainly cut down on all that static code!

 

[DonP]

First of all, by the code above, it is obvious that I am not a programmer even though it works! But I'm not sure I can do what you suggest at this point since there are not yet any queries to the database and no record set is yet open. That's done later on by a different script. Also, I'm not sure how I could get it to give me the cities I need. I don't have the option of changing the database with &quot;regional&quot; choices but as you say, I could create a new table with only the cities. I'm not sure what to do with it though. It would be much preferrable to send the cities at the form level if I could get rid of all the static code above somehow. I'll try your suggestion though to see if I can do it. Maybe I can figure it out and it would be a good learning experience! Thanks!

Don

 

[DonP]

Help! I created a table with a list or cities and a name to the region of each one. I wrote an sql statement, but I am not sure what to do with it.

sql = &quot;SELECT [CITY].[REGION],[CITY].[CITY] FROM [AGENCY]&quot;
searchfor = REGION

The form where the regions are selected is sending the Regional value in numeric format. That is,

&quot;0&quot; = ALL
&quot;1&quot; = North
&quot;2&quot; = Central
&quot;3&quot; = South

But I don't know how to apply that.
Don [sig][/sig]

 

[DonP]

OK. I've created a table with the City names and each one is assigned a region in the database table. I created an sql statement that is pulling out the information and I can write it to the screen, but I'm not sure how to name it as a variable that I can use elsewhere in the script. My simple loop is as follows:

rs.MoveFirst
while not rs.EOF
Response.Write(&quot;'&quot; & rs(&quot;City&quot & &quot;',&quot
rs.MoveNext
wend

but I tried things like:

rs.MoveFirst
while not rs.EOF
CITY = Response.Write(&quot;'&quot; & rs(&quot;City&quot & &quot;',&quot
rs.MoveNext
wend

which works the same but still does not give me a value for CITY.

Also, ultimately I am going to need to call the regions in various combinations so need to be able to remove the ,, (double commas) that some combinations will get between them and the single , (comma) at the end. Is there some way to loop the City names without Response.Writing the commas that will do it dynamically, or is it necessary to put them in that way and then trim the extras?

Thanks! Any help is appreciated!

Don [sig][/sig]

 

[DonP]

Well, this is an improvement of the code I had written originally (at the top of this posting), though I think it can be simplified ever more. I don't know how to do it though. There should be a way of eliminating the four loops into an array. Here is what I have now:

[tt]
CITY = &quot;&quot;
If Request.Form(&quot;All&quot <> &quot;&quot; then
rs.MoveFirst
while not rs.EOF
CITY = City & &quot;'&quot; & rs(&quot;City&quot & &quot;',&quot;
rs.MoveNext
wend
end if

If Request.Form(&quot;North&quot <> &quot;&quot; then
rs.Filter = &quot;Region = 'North'&quot;
rs.MoveFirst
while not rs.EOF
CITY = City & &quot;'&quot; & rs(&quot;City&quot & &quot;',&quot;
rs.MoveNext
wend
end if

If Request.Form(&quot;Central&quot <> &quot;&quot; then
rs.Filter = &quot;Region = 'Central'&quot;
rs.MoveFirst
while not rs.EOF
CITY = City & &quot;'&quot; & rs(&quot;City&quot & &quot;',&quot;
rs.MoveNext
wend
end if

If Request.Form(&quot;South&quot <> &quot;&quot; then
rs.Filter = &quot;Region = 'South'&quot;
rs.MoveFirst
while not rs.EOF
CITY = City & &quot;'&quot; & rs(&quot;City&quot & &quot;',&quot;
rs.MoveNext
wend
end if

rs.close
[/tt]

[sig][/sig]