Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SELECT from DB to use as STRING (2000/03)

    Hi,

    Is it possible to use the value return from an SQL statement as a STRING

    For example:

    strDb = ("charliedocumentsLettersLetterLog.mdb")
    strSQL = "SELECT Address FROM QryF2Details " & "WHERE CRN = " & Chr(34) & strCrn & Chr(34)

    strAdd = strSQL Value ??????????

    I know the above line does not work but is there a way of making strAdd = the value of strSQL (Not the SQL itself but the value found in the database)

    Thank you all so much.
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SELECT from DB to use as STRING (2000/03)

    You'd have to open a recordset (DAO or ADO), as I suggested in <post:=730,599>post 730,599</post:>.

    For example:
    - Set a reference to the Microsoft DAO 3.6 Object Library in Tools | References.
    - Use code like this:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strDb As String
    Dim strSQL As String
    Dim strAdd As String

    strDb = "charliedocumentsLettersLetterLog.mdb"
    strSQL = "SELECT Address FROM QryF2Details WHERE CRN = " & Chr(34) & strCrn & Chr(34)
    Set dbs = DBEngine.OpenDatabase(strDb)
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.EOF Then
    MsgBox "No record found!", vbExclamation
    Else
    strAdd = rst!Address
    End If

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SELECT from DB to use as STRING (2000/03)

    Hi Hans

    Thanks very much

    I was missing the If rst.EOF Then
    MsgBox "No record found!", vbExclamation
    Else
    strAdd = rst!Address
    End If

    Thank you
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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