Merging Data from Column B into a Target Spot in Target A

[CJSwed]

Is there a way to put a place marker inside some information inside one column to merge another colummns data into that spot?

Another words if

-----------------------------------------
Product | Manufacturer
-----------------------------------------
This product is made | Sony
by [manufacturer] and |
is for use indoors |
only. |


I know you can merge two columns in a query but is there a way to merge and put that merged info into a target spot like above?

 

[PHV]

Something like this ?
Replace([Product], "[manufacturer]", [Manufacturer])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

The query by form applet at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

has code that does this. The user creates a "query" and then edits text in a memo field. A hot-key pops up a list of fields to insert into your text. A routine using Replace() is used to "merge" the data with the text.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CJSwed]

dhookom... which report would do that by Duane? Also I have XP .. can I still use those?

Thanks...

 

[CJSwed]

Ph.. exactly something like that... I am assuming it would need to start with an update syntex though? and calling in a table name?

Thanks,
Chris

 

[PHV]

Not an UPDATE but a SELECT.
Depending of your version of access you may have to write your own function:

Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
  myReplace = Replace(myString, myFind, myRepl)
End If
End Function

And in the query grid:
Expr1: myReplace([Product], "[manufacturer]", [Manufacturer])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

In the query by form, your results display in a subform with option buttons to set the destination of the records. The rightmost button provides this functionality.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CJSwed]

Thanks for your help guys...

PH.. if I am using Access XP I assume it would look something like this? Tablename being the name of the table...



SELECT [Tablename].*
FROM [Tablename];Replace([Product], "[manufacturer]", [Manufacturer])
If Trim(myString & "") <> "" Then
Replace([Product], "[manufacturer]", [Manufacturer])
End If
End Function



I keep getting an error message though saying characters found after end of SQL statement? I tried removing the End If and the End Function but still keep getting it...

Sorry for all the questions..

Thanks,
Chris

 

[PHV]

In a standard code module paste the following code:

Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
  myReplace = Replace(myString, myFind, myRepl)
End If
End Function

In the SQL view of your query:
SELECT *, myReplace([Product], "[manufacturer]", [Manufacturer]) As EditedProduct
FROM [Tablename];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CJSwed]

Thanks for the clarification. I have written very limited queries using help books...
Can you poin me to how to create a standard code module? I just did a google hoping not to have to ask another question but I couldnt find anything on that...

Thank you very much.. I really appreciate all of your help...

Chris

 

[PHV]

When in VBE (Ctrl+G) menu Insert -> Module

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CJSwed]

PH.. Thank you!!! Its coming up with some error message about ambigous name .. Ill have to mess with it more in a bit or tonight but just wanted to say Thanks for your time and your patience!!!!

Thank you,
Chris

 

[CJSwed]

Hey.. I just got this to work without the error message.. but what is happening is it is just adding a column to the table called EditedProduct and it is copying the "product" column into it but without the changes...


Just to make it easier.. what I have is a table shall we call xyz similiar to below...

Inside the product column I have a code of abc listed as a place holder.. what I am trying to do is insert the manufacturer from the column "manufacturer" which is in the next column over in the same table and merge (or should I say insert) the information that is in the manufacturer column into the taget spot of where ABC is in the product column. (sort of doing a find and replace, for each item in the product column with the abc place holder, delete the abc and put the information in the manufacturer column where the abc is)
-----------------------------------------
Product | Manufacturer
-----------------------------------------
This product is made | Sony
by abc and |
is for use indoors |
only. |





I did the CTRL+G brought up a blank sheet.. posted in

Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
myReplace = Replace(myString, myFind, myRepl)
End If
End Function

(I even saved it and named it.. then re-entered access.. opened the module and then clicked on the little icon of access on the top task bar which then brought up the control panel in which I went into queries, said create new.. SQL mode and entered in

In the SQL view of your query:
SELECT *, myReplace([Product], "abc", [Manufacturer]) As EditedProduct
FROM [xyz];


After doing this I just get that new column called EditedProduct which copies the info in column abc but dosent make the changes (I am looking for it to take the sentence

This product is made by abc and is for use indoors only.

and turn it into


This product is made by Sony and is for use indoors only.


Thanks for listening...

Chris

 

[CJSwed]

If theres anyone around who can write a query to do what I mentioned above, please contact me.. I am willing to pay for it.


Thanks,
Chris

 

[dhookom]

I just reviewed my query by form download mentioned earlier to confirm the use of Replace() within code to update a memo field with the text from a field or fields. Here is the relevant code section that performs the merge. It might be all done for you if you insert the applet into your application. If you really want to pay for this, I would be willing to make arrangements ;-)

Private Sub cmdOutput_Click()
' Code Header inserted by VBA Code Commenter and Error Handler Add-In
'=============================================================
' Form_frmMyQueryResults.cmdOutput_Click
'-------------------------------------------------------------
' Purpose
' Author : Duane Hookom, Saturday, March 06, 2004
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
'
'-------------------------------------------------------------
' Returns:
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' Saturday, March 06, 2004 dkh:
'=============================================================
' End Code Header block
    'merge the fields from the query and the letter into a temporary table
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intFldNum As Integer

On Error GoTo HandleErr

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qmakLettersTemp"
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM ttblLettersMerge")
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do Until .EOF
                For intFldNum = 4 To .Fields.Count - 1
                    .Edit
                        !ltrBody = Replace(!ltrBody, _
                            Chr(171) & .Fields(intFldNum).Name & Chr(187), _
                            .Fields(intFldNum) & "")
                    .Update
                Next
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    DoCmd.OpenReport "rptQBFLetters", acPreview

ExitHere:
    On Error Resume Next
    'Close objects and set to nothing
    Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at Saturday, March 06, 2004 9:23:01 PM
HandleErr:
    Select Case Err.Number
      Case Else
         MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmMyQueryResults.cmdOutput_Click"
    End Select
    Resume ExitHere
' End Error handling block.
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CJSwed]

Duane,
Thanks.. I just downloaded your file but couldnt figure out how to get it going...

If I copy and paste this code into a module this would work? Im confused with your applet.

I would be willing to pay at this point.. hopefully not an outrageous amount but this would come in really handy and Ive already spent close to a day in trying to find something that will work... grrrr.

Thanks,
Chris

 

[dhookom]

The applet that is in the Query By Form works as a system. You set up some basic "data source" queries with names that begin with "<". Users can then create custom queries based on your data source queries. They select fields, sorting, criteria, etc. One of the output destinations is a mail merge. This allows the user to enter the text for the merge and select fields from a popup form. When merging, the field "place holders" are replaced with field values from your query.

You may not need all this functionality. I only meant it as an example of how to insert place holders and then run code to update the place holders with the field values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CJSwed]

Duane,
Thanks! I also have found another way to do this too in case this post comes up for others...

a module like...


Function myReplace(target As String, toFind As String, toReplace As String) As String
myReplace = Replace(target, toFind, toReplace)
End Function



With a query like...

SELECT myReplace( Product, "[manufacturer]", Manufacturer ) AS EditedProduct FROM TableName


Thank you all for your help!! Its very much appreciated!!!


Thanks guys..

Chris