in tblMain I have a field that contains txt in the following format:
"firstname lastname (misc)"
Not all entries have the (misc).
I need to break out the three fields
firstname, lastname, and other when included in the entry.
Thanks
11 1411
How do you want to break out this data? In a new table?
One way to do it is to create the new table with the fields you want, then, in VBA create a recordset based on the old table, and cycle through each string, looking for the space characters (" "), then split up the string into the Left, middle and right (if any) portions and save those substrings to the new table.
This should point you in the right direction. Let us know where you might need additional help.
NeoPa 32,556
Expert Mod 16PB
This is a perfect illustration of why you never store combined data in a single field. These values should be stored in three separate fields and put together when required.
Nevertheless, you've asked about splitting them so we'd better deal with that. Not so easy as you haven't explained where you want the data broken out to or even exactly the logic which determines which point at which to break the data (Is it by the first and second spaces? Are the parentheses a more reliable indicator?). All these are important points that need to be included in a question if you actually want a usable answer. In very simple terms you can use the Split() function in VBA to produce an array of items split by the spaces. This may help you, but I suspect you need more than that. If so, let us know the full question so we can help.
Thanks for the reply, folks. Exactly, Neo...perfect illustration. I'm just a dabbler and don't get to use these programmings function as often as I would like, but I am sorry for not being more clear.
the original table has the fields:
uid, person_info
uid is autonumber and person_info is "firstname lastname (misc)", a horrible piece of data.
I would like to run a make table query to dump these informations as follows:
uid
pFirstName
pLastName
pCoded
The parenthesis aren't a delimiter, but actually in the text field and the problem is, is that not all line items have the (misc)in there entry, making a trim by space make some functions errors.
I am once and again lost.
thanks for all the help!
NeoPa 32,556
Expert Mod 16PB
What you must do for yourself, before we even get involved, is to decide exactly what logic should be used to separate out the sub-fields from the existing data. This cannot be done on a per-record basis unless you want to get a human to do it. A computer needs a clear and defined understanding of what it is that is required before it can work for you.
This is essentially the heart of the problem - The definition. Without it we cannot help you to implement it in code (apart from very general instructions that won't actually be very helpful).
Having said that - once you do have the logic clearly defined, then implementing it shouldn't be too difficult.
Thanks, NeoPa.
I'm looking to get the following. Ihope this next information helps.
Easiest is to just display the following in a query.
Each record looks like this from tblMaster: uid, person_info (uid not shown in results)
arthur smith (18_7)
josephy and (87_3)
patrick murphy (8_e3/2)
ed sully
mark vignion
mark jalus (7_3_0)
I would like that data from these entries to be displayed as follows, in four separate fields:
uid, pFirstName, pLastName, *pCoded
*pCoded - null if none in string
I'm hoping this makes a little more sense.
Thanks you agian
Using either the Split() function, as described by NeoPa, or evaluating each string character by character should work. As long as every record has FirstName and LastName and some records have the pCoded information, you should be ok.
I would also recommend you not use a "*" in the field name (unless the asterisk was just alerting us to a detail about the field...).
Thanks for the reply. I'm not using an asterisk in the field name; that was just for show that additional information for this field. like "See below"
Neo is right with split function, I am just not so sure on how to use this.
There is a first space from left, after first name. There is last name after this, then space before parentheses on some records.
NeoPa 32,556
Expert Mod 16PB Artemetis:
There is a first space from left, after first name. There is last name after this, then space before parentheses on some records.
Bingo! This is what we were asking for. What you needed to provide. What you need to understand yourself. Example data isn't a specification. It's simply some example data.
You don't specify exactly whether the third space or the open parenthesis should be used to recognise the pCoded value so we'll assume ' (' is required.
Unfortunately (as the Split() function is a very good way to handle this), the Split() function works only in VBA and not in SQL. It appears that an APPEND query is required here. As is sometimes the way in SQL, the code is somewhat kludgy when compared to VBA. - INSERT INTO [NewTable]
-
( [UID]
-
, [pFirstName]
-
, [pLastName]
-
, [pCoded] )
-
SELECT [UID]
-
, Left([Person_Info],[Space1]-1) AS [pFirstName]
-
, Mid([Person_Info],[Space1]+1,IIf([Space2]=0,Len([Person_Info]),[Space2]-1)-[Space1]) AS [pLastName]
-
, IIf([Space2]=0,Null,Replace(Mid([Person_Info],[Space2]+2,99),')','')) AS [pCoded] )
-
FROM (SELECT [UID]
-
, [Person_Info]
-
, InStr(1,[Person_Info],' ') AS [Space1]
-
, InStr([LenFN]+2,[Person_Info],' (')-1 AS [Space2]
-
FROM [tblMaster]) AS [qM]
Thank you!
I've added this to access query; first it kicks back Enter Parameter Value "LenFN" box. I leave it blank and it displays uid and pFirstName. pLastName and pCoded display #Error using this code -
SELECT qM.[UID], Left([Person_Info],[Space1]-1) AS pFirstName, Mid([Person_Info],[Space1]+1,IIf([Space2]=0,Len([Person_Info]),[Space2]-1)-[Space1]) AS pLastName, IIf([Space2]=0,Null,Replace(Mid([Person_Info],[Space2]+2,99),')','')) AS pCoded
-
FROM (SELECT [UID], [Person_Info], InStr(1,[Person_Info],' ') AS Space1, InStr([LenFN]+2,[Person_Info],' (')-1 AS Space2 FROM tblMain) AS qM;
Try replacing "[LenFN]+2" in your SQL with "[Space1]+1".
I don't think LenFN was defined in the query, but I may be mistaken.
NeoPa 32,556
Expert Mod 16PB Artemetis:
I've added this to access query
Wouldn't it make more sense to add what I suggested? You've changed much of what I wrote and I can see no reason for it. It's also formatted poorly so I can't even read it without much effort.
Excuse me, but I'm confused by that.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ASIF |
last post by:
Does anyone klnow of a quick way of exporting data from access to a
text doucument, the data needs to be tab seperated, and the data to be
exported needs to be selected using parameter queries.
I...
|
by: gssstuff |
last post by:
Hi
I have a need to send data from Access to an existing Excel template
that I am using for reporting purposes. The nature of the data in the
Access data table does not lend itself to a simple...
|
by: Marcus |
last post by:
I created a VB.Net 1.1 application that iterates through all the tables
in any basic Access 2000 database passed to it and generates the same
table structure in a SQL Server Express database. The...
|
by: TC |
last post by:
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP,...
|
by: Mohsin |
last post by:
Hi,
any one can help me to save visual Basic Data in Access to link through
Data control or ODBC. when i am trying to save vb data in Access after
making link through data control it is not saving...
|
by: sharper |
last post by:
Hi to all ! I have problem with C# and access databasses!
I can't to insert new record in database , ( I mean I made grid view and connect him with a access database and made all things to...
|
by: =?Utf-8?B?QkFUT04=?= |
last post by:
I have realy big problem I try lot of things but never success!
I need a HELP!
Problem is in that I made database in Microsoft access and connect her to C#
form and datagridview. and it...
|
by: gudipati |
last post by:
hi,i have one problem.actually we can retrieve data from access.
what is my requirement is when i retrieve data from access i want to store in text areas.
finely,we can store if they r textboxes...
|
by: perksyno1 |
last post by:
Hi there,
I've got a problem I was hoping someone may be able to help with. I need to get some data from access and use it to create a pivot table in an excel file.
I've recorded myself doing...
|
by: Zak1234 |
last post by:
Can any one please help me developing the tool.
I have the data in access which I will load every month. My query is I want excel to run as front end and access as back end.. As data grows excel...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |