Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Pulling data from MS Access

    A database "Country"
    A Table "Country" two Fileds, "Country" with Countries Names and "Cities" With three or four cities separated by comma.

    I have this code:

    Response.Write "<tr >"
    Response.Write "<td align=center>" & "Number" & "</td>"
    Response.Write "<td align=center>" & "Country" & "</td>"
    Response.Write "<td align=center>" & "Cities" & "</td>"
    Response.Write "</tr>"

    SQL = "SELECT * FROM Country"
    SET RS = CreateObject("ADODB.RECORDSET")
    RS.Open SQL, "Country"
    i = 1

    Country = RS("Country")
    Cities = RS("Cities")

    Response.Write "<tr >"
    Response.Write "<td>" & i & "</td>"
    Response.Write "<td>" & Country & "</td>"
    Response.Write "<td>" & Cities & "</td>"
    Response.Write "</tr>"
    End If


    This displays all the Countries and all the cities, Now I want to write codes to display All the cities in alphabetical order, I am confused becuase Cities are listed in one filed and they have comma in between.

    Database looks like this:

    Country: USA
    Cities: New York, Los Angeles, Chicago, Washington DC, Philadelphia.

    Country: Canada
    Cities: Toronto, Montreal, Ottawa, Vancouver

    Country: Australia
    Cities: Sydney, Melbourne, Brisbane, Canberra

    and so on...

    Last edited by AM5786; 09-20-06 at 13:52.

  2. #2
    Join Date
    Mar 2006
    south jersey, usa
    i suggest creating an array for the cities. Populate the array with the country's cities. Resort the array alphabeticallly. write the country and the array. rinse and repeat.
    "They say Moses split the Red Sea
    I split the blunt and rolled the fat one, I'm deadly"
    -- Tupac 'Blasphemy'

  3. #3
    Join Date
    Dec 2005
    Even before sorting it I am trying to display a list, tell me what I am doing wrong.
    Cities: New York, Los Angeles, Chicago, Washington DC, Philadelphia.

    pos = InStr(Cities, ",") ' defining first comma position, pos will return 8 in our example.
    While pos <> 0
    Cities = Mid (Cities, 1, pos-1) ' moving city name from position 1 to 7, means Value New York.
    Response.Write(Cities &"<br>") ' Displaying first City name, which in our example is New York.
    Cities = Mid (Cities, pos+1, Len(Cities) - pos) ' Moving 2nd city name from current position which is 8+1 to 59-8.
    pos = InStr(Cities, ",") ' defining second position.
    Last edited by AM5786; 09-22-06 at 14:47.

Posting Permissions

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