Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: adding to a field

    Is it possible to put a field in one table into another field in another table. I have a note field in a table called DOWNLOADS that states,"Account suspended due to returned mail. Previous status code was XXX; account must be put back in previous status code."
    The 'XXX' is a field in another table. Is it possible to have a query or piece of code grab the status code field from the main ACCOUNTs table and apply it to this note field in my DOWNLOAD table. It would have to insert it in the middle of the sentence. HELP!

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Need more details to solve this but have a look at the code below.
    (lacks any error trapping)

    >>It would have to insert it in the middle of the sentence. HELP!

    Where in the sentence? Inserting in or replacing existing characters?
    If replacing existing characters - is the length fix or variable?
    If you are replacing some random XXX characters that you used as a place marker, you can use the Replace function. Otherwise you will need to use a combination of string functions (Left, Instr, Right) to strip and rebuild the string. Lookup the new value on each pass through the recordset.

    ' Declare variables
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strAcountStatus As String
    Dim strStatusCode As String
    Dim strSQL as String

    ' Build our SQL string
    strSQL = "SELECT AcountID, AcountStatus FROM [DOWNLOADS]"

    ' Set our database and recordset objects
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    ' Loop the recordset
    Do While Not rst.EOF

    ' Edit the recordset
    rst.Edit

    ' Grab the AcountStatus string
    strAcountStatus = rst.Fields("AcountStatus")

    ' Lookup the StatusCode based on a relational field between the two tables
    strStatusCode = DLookup("[StatusCode]", "ACCOUNTs", "[AcountID] = " & rst.Fields("AcountID"))

    ' You can use the Replace function if the original is constant
    'rst.Fields("AcountStatus") = Replace(strAcountStatus, "XXX", strStatusCode)

    ' Here we rebuild the string with the first 65 characters, insert the replacement text,
    ' and then all the text on the right starting with the ";" character
    rst.Fields("AcountStatus") = Left(strAcountStatus, 65) & strStatusCode & Right(strAcountStatus, Len(strAcountStatus) - (InStr(1, strAcountStatus, ";") - 1))

    ' Update the record
    rst.Update

    ' Move and loop
    rst.MoveNext
    Loop

    ' Clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Last edited by Rockey; 04-12-03 at 02:30.

Posting Permissions

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