I have a table that I would like to concatenate three name fields:
Last Name, First Name, Middle Name. I inserted a field between each one to create a space when the fields are brought together. The table looks like this:
Last Name, Blank 1, First Name, Blank 2, Middle Name
When these five fields are brought together they can not exceed 25 characters, so the middle name will cut off when the field reaches 25.
Any help will be appreciated.
I have a problem I am working and and am not sure what the best approach is, hoping someone can lend me some insight:I have two fields on a table:SerialNumber....LocationI need to concatenate these two (no problem there). However my situation is such that my serial numbers vary in the number of digits. For example:serial - 55124 (five digits)serial - 552356 (six digits)serial - 5514235625 (ten digits)the serial number field is 10 characters and the serial numbers can be anywhere from one to ten in length (this is external data I am working with, not data I created)When I concatenate the two fields I need the location value to always be in the same place, so if I have a location called - TEST - I need the end result of my concatenate query to place the location always after the tenth place of the serial number position, like this:55124_____TEST552356____TEST5514235625TEST(Without the underscores)Don't ask me why...it's a long story.
View 2 Replies View RelatedHoping someone can help me with this:
I have a table with two fields that I am wanting to concatenate. The two fields are: Order and Line#. The line numbers are sequential by tens, like this:
10, 20, 30...etc.
So I have order lines that are from two to four digits. Like this:
20
120
1020
All order numbers are six digits. I want to concatenate the order and line (easy to do) my problem is I want to append zeros in front of any line number less than four digits. For example, using the lines above and an order number of 111111 I want the result of the concatenation to be like this:
1111110020
1111110120
1111111020
Sorry to ask...but I don't know how to do it.
Thanks for looking!
I have two fields in a table.
client_id and property_id
client_id is an autonumber with a format of 0000000 (meaning the first record is 0000001)
property_id is made up from the first 4 letters of and address line (for example Smith Street would be shown as 'Smit')
I am trying to 'concatenate' the two id fields together, via an expression in a query, to get 'Smit / 0000001' but can only seem to get 'Smit / 1'
How can I solve this ?
My database has FirstName and LastName fields.
I would like to initialize a new field called UserID with the first letter of the first name concatenated to the last name. I have found references that point to using Left([FirstName],1)&[LastName] but am unsure if this can be done in the table definition or if it must be done using a query. Either way I could use some advise on initializing this fields since I have 3500 registered users.
Thanks
Here’s my goal.
1.)I have one column and want to add a 633 in front of if.
a.Expr3: "633 " & [Grp] & ""
b.This only shows 633 and asks for an entry for the GRP? Why? I want the format to be 633 xxx. If I do add, lets say, 123 in the popup, all of the columns are 633 123.
2.)After combining the following, I want to take that number, and look it up in another query. If the number matches a number in the other query, than I want it to enter the number in column 1. If not, I want it write no number. I have gotten this part to work using an IIF function but I just wanted to let you know what I am after.
I have this query
SELECT [9A].DO, [9A].WORK, [BATCH] & " " & [SOURCE] & " " & [NO] & " " & [SEQ] AS RECEIPT,
FROM 9A;
It returnes the results like this:
RECEIPT
6/2/2003 P 29 10
1/15/2003 P 54 55
3/3/2003 P 42 7
1/6/2003 P 39 35
12/30/2002 P 23 30
What changes are needed to the query to return it like this:
06022003P0029010
01152003P0054055
03032003P0042007
01062003P0039035
12302002P0023030
In other words
The date must have 8 charactors with no /
Then P with no spaces before or after
The next number must have 4 charactors no spaces
and the last number must have 3 charactors
Thanks
Hi ...
I have a table called new items which has these fields
Name (Primary)
Serial No
Tag Type
Description
Status
I want the name to be unique and generates automatically and be SerialNo + Tag Type + Description <<<< NAME
I tried a querie which doesnt work
Code:[Description] & ", " & [SerialNo] & " " & [AssetType]
I have a table with a bunch of columns (call it Table A), one of those columns being a look-up (foreign key) to another table (call it Table B). I would like to have another column in Table A that is a concatenation of the value in 1 column on Table A with the look-up value from Table B. What is happening though is a concatenation of the value from Table A and the KEY from Table B. I am not aware of a way to get the concatenate to use the lookup value instead of the foreign key.
View 1 Replies View RelatedI concatenated two fields in a query for a cbo, one field has cpt# the other descriptions. CPT# can vary with respect to maybe a four digit, five digit, etc. Because of this, when joining these fields, descriptions are not lined up. Is it possible to fix this?
View 8 Replies View Relatedi'm using Master and Detail table. Master table i have TaskId, ResId where ResId is mapped to Detail table which contains resource working on the task. i want to generate a query where for each task i want to display resource id in single column like below.
Task Id Resource Id
1 222,233,244,255
Do i need to use recursive query or any other method to get results like above.
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer
Dim testcheck as Boolean
Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount
If Me.selectedproduct.Column(testcount) = True
Me.testnumber.Visible = True
Else:
Me.testnumber.visible = False
End If
testcount = testcount + 1
WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
Any help would be appreciated.
I have a table called Export_Car which contains Data such as License Number and Make and Model.I am calling a VBA function (in my query) that I have found online.This works fine and concatanates all the License Numbers for that Dealer.
ConcatRelated("License & Chr(13) & Chr(10)","Export_Car","[Dealer_ID] = """ & [Dealer_ID] & """")
My question is how do I add more fields to concatanate, such as Make and Model.
I would like to join 3 values to one new column called incident number. Problem is that one of the value is from a lookup field.
If the table look like
ID Type PNumber
1 INR 2477
I generated Incident number as [ID] "-" [Type] "-" [PNumber]. the result should be 1-INR-2477 but its shows 1-1-2477 because the Type column is a lookup field and INR is on the first value of the same lookup table.
How I could resolve this in the table or Query.
I have written a query to concat two strings,
select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]
THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).
I receive reports formatted as a .txt (flat) file.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:
Date Time User Status Customer Name ID#
06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf
1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.
When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.
I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.
2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?
At the end of each record in the report there is a space separating the records.
I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.
I have created a Table Design
Name
Last Name
ID Number
NameID
I import data from CSV xls file which has Columns of Name, Last Name, ID Number.
What I want to achieve is while importing it should Concatenate Name and ID number and store in Name ID.
I have searched it well but havent been able to find solution. Any suggestion how to achieve this.
Thanks
NIRI
Is it possible in Access SQL to concatenate a control into the select statements table or fields? Such as:
SELECT fldtotal & [Forms]![frmTest]![lstYears] & fiscal
FROM tblTotals & [Forms]![frmTest]![lstYears] & Archived
The statement would normally be:
SELECT fldtotal2003fiscal
FROM tblTotals2003Archived
Unfortunately, I am querying somebody elses tables. They archive the data yearly into a new table to control bloat. Instead of yearly having to create a new query for the new year, I was wanting to just have a form that had a list box of years. Then the user can just select what year they wish to query, and the query is automatically updating the field, table, where condition, etc.
I do not know if this is available in Access as it is in Oracle / Coldfusion. Thanks for any help though.
After searching through 18 pages of search results I have not found the answer. Would someone please tell me why this expression is not working in my query?
RCStatement: """&[RootCauseStatement]&"""
The result is "&[RootCauseStatement]&"
Hi,
Thanks in advance for your help.
I'd like to concatenate 2 name fields being TeacherFName and TeacherLName. No problem with that bit.
The bit I'm not sure on is that I'd like to have just the first 4 letters from the last name and the first 2 from the last, unless the last consists of less than 4 letters then I will fill with the first name. IE Dianne Cox becomes coxdia.
Can anyone help please?
Regards
Matt
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.
How to concatenate a number of fields from a table into a query.
The fields are:
Prefix
Firstname
MiddleInitial
MiddleInitial2
MiddleInitial3
Surname
Note that there will be varying combinations: some people will have a Prefix, Firstname then surname (no middle initials), so I would want to display "Mr Joe Bloggs". But others could have no Prefix but two middle initials, so "Joe P J Bloggs".
I have a table Pmt in Ms Access with following information:
Table Pmt
Cust no Inv no T no Date Amt
123 ABC T1 20110131 1000
123 ABC T2 20130228 1000
123 ABC T3 20130331 1000
789 XYZ A1 20130131 2000
789 XYZ A2 20130228 2000
Fields "Cust no", "Inv no" and "T no" are in Text format, whereas "Date" and "Amt" are in Number.
My question: how can I concatenate row values using a query. The query results that I wanted are as follows:
Cust no Inv no T no Date Amt
123 ABC T1, T2, T3 20130131, 20130228, 20130331 3000
789 XYZ A1, A2 20130131, 20130228 4000
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105
I am trying to achieve below:
Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104
How do I concatenate a row in a query and put in a text box in a form? I have a Query returning up to 10 dates the row is called [Date] I need to put them all together with a space and a comma between them and place that in a text box on a form.
View 2 Replies View RelatedI'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]...