Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54

    Unhappy Unanswered: Arrays and recordsets

    I have got a dynamic array that get populated with property reference numbers at the users request.

    i am using this as a temporary store for an sql that is needed later.

    once the user has finished selecting the property reference numbers they move on to another screen where they are to be shown what they have selected.

    i need to use the property refernce numbers in the array with the sql ie,

    select * from propertyTable where propertyReferenceNumber = the contenets of the array

    how can i do this.

    any help will be much appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    temporary table would probably be the best solution....

    another way is constructing the WHERE part of sql query with contents of array. I think that this would slow down query a lot....

  3. #3
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    what is teh syntax for adding the contents of the array onto the sql

  4. #4
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    well you would create a string to hold sql

    sqlstr ="select * from propertyTable where "

    then have a loop go thru array and append to the string

    sqlstr = sqlstr & "propertyReferenceNumber = " & item of the array & " OR "

    this will give you complete sqlstr string that you can use same as query....

    remember to remove last " OR ".....

    sqlstr = left ( sqlstr, (len(sqlstr)-4))

  5. #5
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    just for information this is what i have used and it seems to be working.

    Dim strSQL As String
    Dim strSQLArray As String
    Dim counter As Integer
    counter = 0

    For counter = LBound(propertyListArray) To UBound(propertyListArray)
    strSQLArray = strSQLArray & "(((propfixedMade.propref="
    strSQLArray = strSQLArray & propertyListArray(counter)
    strSQLArray = strSQLArray & ")) OR "
    Next

    strSQLArray = Left(strSQLArray, (Len(strSQLArray) - 4))

    strSQL = "SELECT propfixedMade.propref, propfixedMade.houseno, propfixedMade.proadd1, propfixedMade.proadd2, " & _
    "propfixedMade.proadd3 , propfixedMade.propstcde" & _
    "FROM dbatest_propfixed INNER JOIN propfixedMade ON dbatest_propfixed.propref = propfixedMade.propref " & _
    "WHERE ((propfixedMade.proclass)='R') AND ((propfixedMade.rentgpcode)='G1'))" & _
    "AND "

    strSQL = strSQL & strSQLArray
    lstCPLPropertiesInList.RowSource = strSQL

Posting Permissions

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