Concatenate Into A List In A Single Field

Jun 29, 2007

To anyone who can help,
I have created a function that loops through an amount of records and concatenates them into a single comma-separated string. What I would like to do is concatente the values into a list. Instead of:

value1, value2, value3

I would like to see

value1
value2
value3

This needs to be in a single record only. The code I am using is:
----------------------------
while not rec.eof
string = string & ", "
rec.movenext
wend
string=left(string,len(string)-2)
--------------------------------

Thanks for all your help. I have tried to insert char(20), but this only seems to work in Excel. Access gives me invalid char symbol.

View Replies


ADVERTISEMENT

Forms :: Concatenate Entries From A Single Field Based On Another Field

Apr 11, 2013

I have a datase that has the following fields.

Name
Comment1
Comment2

I have about 36 names and each of those names appears anywhere from 2 7 times each. Each name entry has a comment1 and a comment2.What I would like to do is create a form that allows me to choosea name and the display the comments 1 and comments 2 in text boxes.

I have made the form to allow me to choose a name and it shows one of the comment1 and one of teh comment2 in the adjacent text boxes as planned but, I want all the comment1s for Bob to show in the text box for comment1 and all of the comment2s for Bob to show in the Comment2 text box.I believe I need to concatenate the results in the comment1 and comment2 boxes, but I do not know where or how I would do that.

View 5 Replies View Related

Modules & VBA :: Concatenate Data From Multiple Rows Into Single Row / Field

Nov 4, 2013

I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.

Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents

Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents

Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String

[Code] ....

View 2 Replies View Related

Concatenate 3 Rows Into Single Header Row

Jan 31, 2014

A third-party application I use exports data with 3 header rows. I usually have to go into Excel and concatenate the rows into a single header row, then export the file to Access to apply queries. I want to minimize the steps in this process, so I'm wondering if it's possible to do this concatenation in Access (with or without VBA).

View 13 Replies View Related

Concatenate Results Of A Single Column Query

Mar 14, 2007

I have a query which select the records who have a certain criteria.

The result is something like this.
A 0012345
A 0032432

Note that the records that have this criteria will be smaller or equal than 2.

I want the query to give me this answer instead.
A0012345, A 0032343
(in the same field that is).

Is this possible, if so can someone help me. My email address is russcami@yahoo.com

Thanks

View 2 Replies View Related

Forms :: How To Join (concatenate) Records Of Values In Single Line

Jan 15, 2015

In Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).

Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...

This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:

"These were the votes of the owners of fractions:

O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78

How can I do it?

View 1 Replies View Related

Making A Listbox Display A List From A Single Field (cell)

Sep 29, 2005

:confused: :confused: :confused:
Hello, I am new here to the forum.

I have a list box on a form that i want to display a list from a single cell or a table in my access database. The list in this cell is a list seperated by commas such as.....

Field 1 Field 2
example a, b, c, d

I want my listbox to display the list from the first cell in field 2.

Anybody have any ideas?

Thanks,

Kevin :)

PS I know something similar can be done in visual basic. For example, a simple program can be written to take a list (seperated by commas (,) ) and then display this list in a listbox on the form. Is there a way that the simple code such as that can be altered to work in access?

View 3 Replies View Related

General :: Concatenate Address List That Does Not Have Any Blanks

Aug 16, 2014

I have five address fields:

add_1
add_2
add_3
city
county
zipcode

What I want to achieve on my (Word) letters, forms, reports etc is a concatenated list that does not have any blanks!!

city, county and zipcode will always be filled however it is possible that add_2 and add_3 could be blank. I hope I am explaining this correctly?

How do I achieve this? I envisage a query which produces just one list that I can just add to my documents but I am struggling with the formula to ignore the blanks.

View 5 Replies View Related

Concatenate Field Or Group By In Report

Dec 15, 2012

I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.

Code:
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;

I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.

e.g.
ITEM
LOCATION
DATE

GORP
EAST
1/1/2012

GORP
EAST
2/1/2012

[code]...

View 7 Replies View Related

Concatenate Multiple Records Into Memo Field

Mar 9, 2007

Hi All,

I can use some help with a jumping off poit.

*Please see attached file*

I have 2 tables tblSFDC and tblTheCall

tblSFDC (contact information) includes CustID (primary key) and a memo field "Notes"

tblTheCall (call information) includes CustID (relates to tblSFDC) and "activity data fields" ie "Live", Left Message" , Sent email".

tblTheCall may in some cases have multiple records for each CustID

I need to :
Concatenate from tblTheCall Field Names and field values (that are not null)
Insert into tblSFDC.Notes
Where tblTheCall.CustID = tblSFDC.CustID

*Please see attached file*
TIA
Detrie

View 8 Replies View Related

Combo Box-concatenate Field And Hide Column

Jul 8, 2005

Hi,

I have a combo box which is populated by a table with the fields ID, First Name and Surname. What I want to do is display the first name and surname concatenated and hide the ID column, but populate the field with the ID number.

Is this possible?

View 3 Replies View Related

Query Field To Concatenate Fields With Nil Values

Feb 2, 2005

I have an Access 2000 project and am trying in a query view to join 3 values into one.
2 of the values come from tables, the third comes from another query view (all linked in the query design screen)

Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty.
All values are strings.

I have tried this formula in the query design grid:

dbo.Value1 + '/' + dbo.Table2.Value2 + dbo.vieTable3.Value3


..in an attempt to give the result Value1/Value2 or value1/Value3 according to the
particular record.

Unfortunately it doesn't work! ...just returns blank results when the query view is run.


Any suggestions would be greatly appreciated

thanks....nick

View 4 Replies View Related

Modules & VBA :: Concatenate Multiple Rows Into One Field

Nov 16, 2013

I have a table:

"tb_skybox_Types", with the fields, [Policy_URN], [Box_Type]

Sample:

Policy_URN Box_Type
001 Standard
001 Sky HD
002 Sky +
002 Sky + HD

I'm mail merging this, so naturally it's giving me a row per box type against which I need to be one. Desired output:

Policy_URN Box_Type
001 Standard | Sky HD
002 Sky + | Sky + HD

I'm then going to make this into a temp table, then match the URN back to the customer table to get a single row per customer.

View 3 Replies View Related

Reports :: Concatenate Multiple Rows Into 1 Field

Jun 18, 2013

My database has a 'markets' table, where there could be multiple markets per campaign.

So right now in my report, it's displaying like this

campaign 1
Market 1
Market 2
Market 3
Etc.

Is there a way, to Concatenate multiple rows into 1 field. So it would look something like this

Campaign 1
Market 1, Market 2, Market 3

That would save a lot of space in the report, and make it easier to read.

View 11 Replies View Related

Tables :: Concatenate Field Names But For Each Record Order Can Be Different

Jul 10, 2014

I have
Field1
Field2
Field3

I need to concatenate these three fields into one but for each record the order can be different. What I want to do is have another field in the table to store the concatenation order as the field names not the field values.

I also need to display the concatenated field as the actual field values and not field names in a form or a report

So if field4 was the concatenated field store it would store the information like this

[Field1] & " " & [Field3] & " " & [Field2]

In a form or a report

Field4 should show as Values of those fields

Monkey Dog Cat

View 1 Replies View Related

Forms :: MultiSelect Or Single Select List Box?

Oct 15, 2013

I have inherited a database that is used to track equipment that comes in for repair. One of the fields in this database is a memo field that lists the parts that are replaced on a piece of equipment. It has been requested that I leave this field as it is. However, I would like to regulate the data that is entered into to some extent. I need to ensure that there is a comma between every part number listed (there is another group that wants data from this database and they want to be able to separate this field out into individual fields). Is there a way to have Access look at the data in a memo field, find all the periods or ampersands, and replace them with commas?

The other thought I had was to provide the person filling in the database with a multi-select list box that lists all the available part numbers for a given piece of equipment. That person could select as many as she wants and when she was done, the database would take those values, put a comma between each one, and paste them in the memo field. Is that possible? What I have tried isn't working. I tried to get it to loop through the selected items and put them in the memo field, but I just end up with the last value selected instead of all of the items selected.

I don't know what to try next, Either the person entering the data is going to be upset because she has to type every part number in a different field, or the people receiving the data are going to be upset because there isn't a comma between each number.

View 5 Replies View Related

Tables :: Survey File - Concatenate 2-3 Field Values In Table

Sep 6, 2014

I have a project that in Access. I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year. My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.

View 5 Replies View Related

General :: Concatenate Two Text Fields Into A Memo Field In Query?

Sep 5, 2014

I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.

View 3 Replies View Related

Queries :: Concatenate Related Records Into One Expression / Field In A Form

Feb 13, 2014

In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.

Currently my query looks like this;

As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.

View 14 Replies View Related

How To List All The Column Names For A Given Table Using A Single Query?

Dec 16, 2005

:mad: :mad:
Hi folks,

I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.

Thanks.

View 1 Replies View Related

Queries :: Concatenate Field Data Into One Cell In Query According To Linked Table ID?

Mar 8, 2014

i need to Concatenate a fields data into one cell in a query according to linked table ID....

View 3 Replies View Related

Displaying Data From Combo And Text Boxes In A Single List

Feb 6, 2005

I have several combo boxes and textboxes showing quanity,productname,
size, and price. How can I pass all of this info in a single row that also calculates
the quanity and price. Also, multiple selections can be added, so several items can
can added in other rows. THe ability to cancel each row would be required as well.
Thanks

View 2 Replies View Related

Forms :: Single List Box Option To Input Data To Text Box

May 9, 2013

I have a form with a list box on it that has 4 teams (Team1-4)..Below this list box I have a text box:"People on shift tonight"..I want to be able to select a team from the listbox and this prepopulate the people on shift box with the names of the colleagues from the team specified, is there a way to do this?

View 2 Replies View Related

Forms :: Single Column Combobox To List Values From Multiple Fields

Apr 8, 2013

I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.

All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.

View 1 Replies View Related

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

Forms :: List Boxes - Single Column Versus Multi Column

Apr 3, 2013

How do I select the first column of a multi-column list box (called "List1") for a query.

A single column list box works fine.

Code:

SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved