merging a value in a text field with each line in a memo field

I am trying to merge a value in a text field with the information in a memo
field using a query to create a report. 

This is the query I am using:

SELECT AVRelay+","+IPRange
FROM T_Sites

AVRelay is the text field and IPRange is the Memo. 
IPRange has multiple lines, each of which I need appended with the value in
AVRelay for the report. It should look like.

2,192.168.1.0/24
2,192.168.0.0/24
2,192.168.2.0/24

Instead it looks like

2,192.168.1.0/24
192.168.0.0/24
192.168.2.0/24

I have searched far and wide but has come up empty. Please help!

0
cdiaz1116
12/15/2009 7:08:27 PM
access 16762 articles. 3 followers. Follow

6 Replies
1253 Views

Similar Articles

[PageSpeed] 48

I would guess that:

AVRelay is null in some records. Notice that the comma is also missing in 
those records. When used with a + concatenation operator, it will evaluate 
to null when concatenated with a null.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"cdiaz1116" <u56890@uwe> wrote in message news:a0a342f509ce8@uwe...
>I am trying to merge a value in a text field with the information in a memo
> field using a query to create a report.
>
> This is the query I am using:
>
> SELECT AVRelay+","+IPRange
> FROM T_Sites
>
> AVRelay is the text field and IPRange is the Memo.
> IPRange has multiple lines, each of which I need appended with the value 
> in
> AVRelay for the report. It should look like.
>
> 2,192.168.1.0/24
> 2,192.168.0.0/24
> 2,192.168.2.0/24
>
> Instead it looks like
>
> 2,192.168.1.0/24
> 192.168.0.0/24
> 192.168.2.0/24
>
> I have searched far and wide but has come up empty. Please help!
> 


0
Arvin
12/15/2009 7:33:55 PM
Actually the example I gave it a single record. Since IPRange is a memo field
with multiple lines the concatenation is only occuring on line 1 which is
what I am trying to solve. I may not have explained myself right. 

Arvin Meyer [MVP] wrote:
>I would guess that:
>
>AVRelay is null in some records. Notice that the comma is also missing in 
>those records. When used with a + concatenation operator, it will evaluate 
>to null when concatenated with a null.
>>I am trying to merge a value in a text field with the information in a memo
>> field using a query to create a report.
>[quoted text clipped - 20 lines]
>>
>> I have searched far and wide but has come up empty. Please help!

0
cdiaz1116
12/15/2009 7:41:59 PM
As far as Access is concerned, a memo field is just one value. Access does 
not recognize 'separate lines'. So I think you need to first separate out the 
'lines' in the memo field into some other columns. You'll need to code this 
in VB.
Maybe someone can come up with a better solution.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"cdiaz1116" wrote:

> I am trying to merge a value in a text field with the information in a memo
> field using a query to create a report. 
> 
> This is the query I am using:
> 
> SELECT AVRelay+","+IPRange
> FROM T_Sites
> 
> AVRelay is the text field and IPRange is the Memo. 
> IPRange has multiple lines, each of which I need appended with the value in
> AVRelay for the report. It should look like.
> 
> 2,192.168.1.0/24
> 2,192.168.0.0/24
> 2,192.168.2.0/24
> 
> Instead it looks like
> 
> 2,192.168.1.0/24
> 192.168.0.0/24
> 192.168.2.0/24
> 
> I have searched far and wide but has come up empty. Please help!
> 
> .
> 
0
Utf
12/15/2009 8:03:01 PM
You could do something like the following. This assumes
that the memo field only contains values like you described
and that each value is on a new line within the memo field
so we can use Carriage Return Line Feed as a delimiter.

First, create a function that will split the memo field into an array
and then combine tha value from AVRelay with each element
of the array;

Function SplitIP(AVRelay As String, IPRange As String) As String
On Error GoTo HandleError

    Dim MyArray
    Dim i As Long
    Dim strResult As String
    
    MyArray = Split(IPRange, vbCrLf)
    
    For i = LBound(MyArray) To UBound(MyArray)
        strResult = strResult & AVRelay & ", " & MyArray(i) & vbCrLf
    Next i
    
    SplitIP = strResult
    
BailOut:
    Exit Function
    
HandleError:
    MsgBox Err.Number & Err.Description
    Resume BailOut
    
End Function

Then use the function in a query like;

SELECT tblYourTable.AVRelay, SplitIP([AVRelay],[IPRange]) AS Expr1
FROM tblYourTable;

-- 
_________

Sean Bailey


"cdiaz1116" wrote:

> Actually the example I gave it a single record. Since IPRange is a memo field
> with multiple lines the concatenation is only occuring on line 1 which is
> what I am trying to solve. I may not have explained myself right. 
> 
> Arvin Meyer [MVP] wrote:
> >I would guess that:
> >
> >AVRelay is null in some records. Notice that the comma is also missing in 
> >those records. When used with a + concatenation operator, it will evaluate 
> >to null when concatenated with a null.
> >>I am trying to merge a value in a text field with the information in a memo
> >> field using a query to create a report.
> >[quoted text clipped - 20 lines]
> >>
> >> I have searched far and wide but has come up empty. Please help!
> 
> .
> 
0
Utf
12/15/2009 9:14:02 PM
That worked out nicely. Thanks!



Beetle wrote:
>You could do something like the following. This assumes
>that the memo field only contains values like you described
>and that each value is on a new line within the memo field
>so we can use Carriage Return Line Feed as a delimiter.
>
>First, create a function that will split the memo field into an array
>and then combine tha value from AVRelay with each element
>of the array;
>
>Function SplitIP(AVRelay As String, IPRange As String) As String
>On Error GoTo HandleError
>
>    Dim MyArray
>    Dim i As Long
>    Dim strResult As String
>    
>    MyArray = Split(IPRange, vbCrLf)
>    
>    For i = LBound(MyArray) To UBound(MyArray)
>        strResult = strResult & AVRelay & ", " & MyArray(i) & vbCrLf
>    Next i
>    
>    SplitIP = strResult
>    
>BailOut:
>    Exit Function
>    
>HandleError:
>    MsgBox Err.Number & Err.Description
>    Resume BailOut
>    
>End Function
>
>Then use the function in a query like;
>
>SELECT tblYourTable.AVRelay, SplitIP([AVRelay],[IPRange]) AS Expr1
>FROM tblYourTable;
>
>> Actually the example I gave it a single record. Since IPRange is a memo field
>> with multiple lines the concatenation is only occuring on line 1 which is
>[quoted text clipped - 12 lines]
>> 
>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
cdiaz1116
12/16/2009 8:34:07 PM
Hi,

     You could do something like:

select AVRelay & "," & Replace(IPRange, Chr(10), Chr(10) & AVRelay & ".")
FROM T_Sites;

           Clifford Bass

cdiaz1116 wrote:
>I am trying to merge a value in a text field with the information in a memo
>field using a query to create a report. 
>
>This is the query I am using:
>
>SELECT AVRelay+","+IPRange
>FROM T_Sites
>
>AVRelay is the text field and IPRange is the Memo. 
>IPRange has multiple lines, each of which I need appended with the value in
>AVRelay for the report. It should look like.
>
>2,192.168.1.0/24
>2,192.168.0.0/24
>2,192.168.2.0/24
>
>Instead it looks like
>
>2,192.168.1.0/24
>192.168.0.0/24
>192.168.2.0/24
>
>I have searched far and wide but has come up empty. Please help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
Clifford
12/18/2009 7:03:57 PM
Reply:

Similar Artilces:

Stored Procedure with array of values
I have the query below in a stored procedure in MS SQL 2005 server. My problem is that I would like to be able to pass in multiple questionID values. So I would like to replace A.QuestionID = @QuestionNumber with a method where I can pass in a list of question numbers. For example instead of passing in 286 I would like to pass in 286, 289, 412, and 513. The number of values passed in would vary from 1 to 15 or 20. I imagine the line A.QuestionID = @QuestionNumber would be edited to something along the lines of A.QuestionID IN ( @QuestionNumber ) SELECT Coun...

e-mail merge privleges
I am the adminstrator for our CRM and when I test the e-mail merge function it works great. However, our sales personnel get an error message that says they do not have the privleges to perform the task. Can someone tell me what permissions to make sure they have to peform this task? ...

Multiple If True Values?
Does anyone know of a way to check for a value in two different cells, and if a certain combination of values exists, change the format of another cell? Any help would be much appreciated. Thank You. Start by experimenting with Format|Conditional Formatting Then come back if more help is needed best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lee" <Lee@discussions.microsoft.com> wrote in message news:07DC594F-6045-4A81-B8B9-19F295A2AAA5@microsoft.com... > Does anyone know of a way to check for a value in two different cells, and &g...

Hotkeys for text & background coloring
Hi. Does anyone know the hotkeys for text coloring (font color) & background coloring (fill color)? Eg: First you need to select the font color & fill color in the related toolbar incons. - When I highlight "I am great", then press "Ctrl+O" for example, the text will be colored as what it's displayed in the "font color" toolbr icon. - The simliar way applies to background coloring If the above is not available, any workaround is still apreciated. -- Additional information: - I'm using Office XP - I'm using Windows XP You could use a sma...

Tutorial on line available?
Need to learn this fast. Is there an on line tutorial (free, of course) that I may use? Thanks in advance There are quite a few general ones, here's one: http://www.itts.ttu.edu/documentation/excel/excel2/exceltxti.html also, there are a lot of sites with useful information. John Walkenbach has a good list here: http://j-walk.com/ss/excel/links/ HTH tim "Semudemu" <Semudemu@aol.com> wrote in message news:03e201c34bd7$e55c9e50$a301280a@phx.gbl... > Need to learn this fast. Is there an on line tutorial > (free, of course) that I may use? > Thanks in advance &...

Can't display all text in cell
I'm trying to display text in a cell. I've got Wrap Text on, and I hav several paragraphs of text. All of it appears in the formula bar, bu not in the cell displayed on the screen. And yes, I increased the ro height, but it still doesn't show. Any ideas? Thank -- Message posted from http://www.ExcelForum.com I am having the same problem. Apparantly there is a 1024 character limitation per cel in excel. The only advice I have received so far, which does work, is to press alt+enter to create a line break. If you find another work around, I would be interested. Hi besid...

Creating a summary page without blank lines
I have a project where I need to determine if the billing for supplemental charges for employees matches what is actually deducted from the employee's pay check. I just started this position and what I am working with is a separate spreadsheet for each month's bill. What I have done so far is to create a spreadsheet with a tab for each month that just has the supplemental information being billed for in the left most columns and what was deducted from each paycheck in the columns to the right, a tab that lists employee names and employee numbers, and a summary page that lists...

Does anyone use "location" field in Outlook?
After all these years, I just noticed when I go to make an appointment in Outlook (2003version, with BCM), that there's a second field after "subject" which would seem to be the one everbody mostly uses. The appointment fields are: 1. SUBJECT: (example: "Conference call with Joe and Mary) 2. LOCATION: (does anyone use this?) Is the idea to put something like "Annapolis, MD" meaning maybe a meeting there or something? I know it's a hokey question, but wondered about some examples of how other people are using it. Thanks, G Standard following us...

how to define scroll bar's page size value?
Hi all, I have a confusion about calculating scroll bar's page size. In my application, I am drawing an image according to scroll bar position. m_iGeneratedImgWidth - Width size of the image (this value is always bigger) rect.right - Width of the drawing area. how to define scroll bar's page size value? SCROLLINFO info; info.cbSize = sizeof(SCROLLINFO); info.fMask = SIF_RANGE|SIF_PAGE|SIF_POS; info.nPos = 0; info.nMin = 0; info.nMax = m_iGeneratedImgWidth - rect.right; info.nPage = ? In my OnHScrollBar() function, for SB_PAGELEFT / SB_PAG...

Empty From: field
Hi. We use a exchange 2003 server and I have notice that my outlook 2003 client behaves strange if the From tag in the header looks like this; From: �� <sender@domain.com> I�ve hooked up Thunderbird and it shows the From field as I think it should, instead of a blank from field it shows sender@domain.com If the header looks like this; From: sender@domain.com or like this; From: �name� <sender@domain.com> it works just fine. The problem is the first example� Any ideas? Cheers Jonas Jonas <barre@gargamel.nu> wrote: > We use a exchange 2003 server and I have notice th...

Add lines to a renewed contract
Whenever I try to add a new contract line while renewing a contract (ie the customer has purchased an item during the year and the contract template is coverage dates), the system rejects the new line with "invalid coverage dates". Doesnt seem to matter what dates are entered. Please advise how to add new lines while renewing a contract. Thanks Prue ...

Insatantiate Generic Collection with Initial Values?
You know how you can instantiate an array of, say, strings and specify intial values at the same time with something like: string[] myStrings = new string[3] { "value1", "value2", "value3" }; Can the same type of thing be done for a List<string> object? I've done a lot of web searching but I can't find any sample code that illustrates this. Joe Cool wrote: > You know how you can instantiate an array of, say, strings and specify > intial values at the same time with something like: > > string[] myStrings = new string[3] {...

counting same text from different cells
Hi I was Trying to count text such as "p" for pass from different cell in excel but i am not sure what is the formula for that. It will be really helpful if u could send me the solution thank you om Hi =COUNTIF(A1:A100,"p") -- Regards Frank Kabel Frankfurt, Germany "om" <om@discussions.microsoft.com> schrieb im Newsbeitrag news:96B25715-6B88-40F8-8360-9602A05E3B30@microsoft.com... > Hi I was Trying to count text such as "p" for pass from different cell in > excel but i am not sure what is the formula for that. > > It will be rea...

Like numbers in a field
I am working with a 25,000 row parts issue listing that was created by parts issue date. There are many repeat numbers in the part number column as the same part would have been issued on several different dates. I would like to be able to combine the like part numbers to know how many parts were issued over the course of all of the dates. Any ideas??? The Column headers are Part Number, Quantity, Unit Price, Total Price and Transaction Date. Create a new query based on your parts issue listiong table. Drag the Part number field into the query grid twice. Click on the Total...

pulling cell "value"
in the cell C4 i have the formula =now() and I have that cell formatted to custom mmmm so that it displays as January. I am now trying to write some VBA code to look at cell C4 and take the January and then do a vlookup on the array that i have named months. months is the cells D14:E25 column D has a list of the months and column E has a list of numbers that corresponds to these months. This is a custom list so I can not use the standard numbers that excel uses for the months. The vlookup in vba should take the January and find the number in col E that corresponds to it th...

Linking Outlook fields from the "Contact Folder" to a custom form in Outlook...
Using Exchange Server 2003/Outlook 2003, we have created a custom Help Desk form in Outlook. We would like the ability to link specific fields from the Contacts Folder (Example B), in a drop-down list box if possible. We are far from knowing that something like this is even possible. Would appreciate *any* suggestions. Thank you. ...

Merge Cells, but keep backgrounds
In excell 2003 is there a way to merge cells so the text spans them all, but keep the current background of the cells (which are not all the same color)? No Try Center Across Selection and avoid merged cells whenever possible. Gord Dibben MS Excel MVP On Sat, 30 May 2009 09:16:12 -0700 (PDT), "dan@danirwin.net" <dan@danirwin.net> wrote: >In excell 2003 is there a way to merge cells so the text spans them >all, but keep the current background of the cells (which are not all >the same color)? Is there a way to center across selection vertically or just horizonta...

Showing "Meeting point" of lines in Chart
Hi, I am trying to create a chart (with a data from a table). I'm using excel 2003. I manage to do it pretty well, I'm just facing one problem: I have 2 lines which are crossing eachother in a curtain point. Is there an option to show this exact point on the chart? (meaning, with the exact X and Y location) I couldn't find such option in the chart properties and settings... Many thanks! JJ. -- jinjon ------------------------------------------------------------------------ jinjon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28903 View this thr...

AutoFill Changing Wrong Value
I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa Presumably you want to sum data from that range? Try this: =3DSUM(INDIR...

How set totals of various cells with per colour fill value?
I have a sheet containing values as follows. April May June company 1 43 company 2 5 company 3 17 The cell values are also coured to indice a status. Somewhere beside the table I want to mention the totals of the cells, per status (so per colour). How to perform the latter? Thank you. Bart Excell 2003 See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- HTH Bob Phillips (remove xxx from email address if mailing direct) "AA Arens" <bartvandongen@gmail.com> wrote in mes...

Can my form fill in a field, yet allow me to override?
Each customers is sent to us from a salesman, and we provide service for 1-7 days before the activity ends. A new charge is generated for each day of service. Employee #1 uses the customer service form to generate a bill, and includes the name of the salesman. Employee #2 uses the form to generate a bill on the next day, and has to look up the first bill, in order to know which salesman to record. The same thing happens every day until the activity ends. With each new referral for service, there's a strong chance it will be due to a different salesman, so the customer number an...

Sorting Spreadsheet with Merged Fields
I have a huge spreadsheet that currently fits comfortably on an 11 x 17" page making it difficult to work with and understand....just too big. I'd like to make the spreadsheet fit on an 8.5 x 11" (landscape) page by stacking fields. This is marketing pursuit data so most fields hold text. When I stack the fields some fields must be merged. So far...not tough....I have a beautiful spreadsheet with all the data in a convenient format, easy to understand. The problem is sorting the first field (priority) per field grouping....can't sort because of merged fields. I t...

DrawText() and text orientation
Hi, I have owner draw fixed CMyTabCtrl derived from CTabCtrl. It has overriden DrawItem() and I tried to draw text on tabs with help of DrawText() or TextOut(). On my control I need vertical tabs but horizontal text on tabs. When I set vertical tabs in resource editor then in run-time all texts has vertical orientation on tabs which I dont want. How to change text orientation from vertical to horizontal with DrawText() or TextOut() ? Where is stored such property ? How to enlarge tab size in CMyTabCtrl ? (I need to have larger than standard tab sizes) Peter If you do it using GM_ADVA...

mail Merge
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have made labels previously with no problem but now I am having difficulty. Originally I used a Mac database and converted it to Word. Now I can't remember how I did that so I went to the trouble of typing the list of names I needed into an Excel workbook. When I try to create the labels, the <br> sheet says &quot;next record&quot; in all the cells except the first one, which remains blank. <br> When I try to enter the merge fields, it just shows the first name on my list. What am I doi...

date in a text cell
I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have a regular date format in a text cell. Thanks If you format the cell as Text, then you'll have to type in what you want--exactly the way you want to see it. If you don't format it as text, you could type the date in anyway that's a date, then use a custom format of: dd-mmm-yyyy (format|cells|number tab|custom category) Dajana wrote: > > I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have > a regular date format in a text cell. > > Thanks -- Dave Peterso...