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!
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
' 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
' 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
' Move and loop
' Clean up
Set rst = Nothing
Set dbs = Nothing