VBA Access 2003 Field Separation

Discussion in 'Software' started by Refurb, Dec 2, 2005.

Thread Status:
Not open for further replies.
  1. Refurb

    Refurb Private E-2

    I have a single field in an Access 2003 Database which contains name and address info and looks like this:

    John Smith
    123 Main Street
    Apt 3B
    Philadelphia, PA 19125

    I am trying to figure out how to separate the single field into the following fields:

    [FirstName]
    [LastName]
    [Address1]
    [Address2]
    [City]
    [State]
    [Zip]

    Has anyone ever encountered this situation and already written code for it? I would ideally like to end up with the data for each field in a separate Dim Variable so I can put the end separated values into their approiate fields directly on the open Access form.
     
  2. jewlzs

    jewlzs Corporal

    Ahhhh to write code great skill to have but for what you need to do lets keep the task at hand simple...
    unless this is a project for a computer course
    1 Copy and paste your single column of names and address's etc into a single column in Xcel.
    2. Be sure there is a space between the appropriate data segements within each cell example john smith 13 simple str apt 3b anytown st zip.
    3. Highlight column
    4. Click the Data pull down menu chose text to column feature then make sure you choose the file type "Delimited" then click next
    5. you then must choose in the Delimiters option only "Space" and click finish.

    This will of course give you exactly what you want but will seperate the street number from the street name and the apt from the apt number.

    Save your spread sheet and import it into access.
     
  3. Refurb

    Refurb Private E-2

    I really appreciate your reply. This doesn't quite do it. This is for a business application and copying to a spreadsheet and importing would be equally as much work as cut and paste, which is what I am currently doing. This would also separate the data into too many fileds and I would be then coming from the other direction in trying to combine multiple separated field into single fields.

    What I really need is VBA code which I can attach to a command button on a form That when executed will take the contents of a field on the form which has this info all together, and separate it into the appropriate fields on the form. which will do something along the lines of:

    1) Determine the # of lines in the field
    2) Start on line 1 in position 1 and select characters until the space
    3) Then Select characters from the end of the line And work backwards to the space (in case there is a middle name so it doesn't pick up the middle name.)
    4) Go to line 2 and put the entire line in field [Address 1]
    5) If there are 4 lines then take line 3 and put the entire line in field [Address 2]
    6) Go to last line and separate City, St and Zip into theiir respective fields.

    Any parts of this code would be greatly appreciated. Again, thank you so much for your reply.
     
  4. jewlzs

    jewlzs Corporal

    Ok, re-read my reply not one mention of copy paste sorry you miss understood my resolve.

    I initially understood you had pre-equisting data in on cell in table format and needed to seperate that data into name, address, etc. I have never worked or seen anyone combine complete names, address, etc. into one cell.
    You can design a form to appear as if the cells are one - very simple. This will take you one day max. To correct the current data the table and the form and report as well as any queries.

    I understand this is how the data is continuing to be entered into the dbase from single cell on a form - so instead of correcting the table to accomodate the cells you need you are compounding the error by choosing to create VBA code and attach it to the same form to rearrange bad data entry format like a magic button. KNOW THIS VBA CODE WILL NOT I REPEAT WILL NOT work humans error and were there occurs two or more spaces between data and / or missing data your results will be just that your mistake. Tell me are the keyboards the humans are using to fill out this form missing a tab key or enter key Does this form have more than one cell is the economy so bad you cant increase the table to accomodate any more columns

    DUDE IS THIS YOUR FIRST DAY IN DBASE - LET ME GET HARSH HERE !!! LOOK AT YOUR DEGREE YOU SHOULD KNOW A DBASE MUST HAVE GREAT TABLES TO PRODUCE GREAT DATA AND YOU SHOULD KNOW IT IS EASIER TO COMBINE THAN TO SEPARATE THIS IS NOT CHEMISTRY. THIS PLACE HIRED YOU FOR YOU SKILLS TELL THEM YOU CAN FIX IT BUT NOT WITH SOME VBA MAGIC CODE BUTTON INSERTED ON THE FORM AND SHOW THEM AN ENTER KEY.

    As for converting your existing data I never typed copy paste I referred you to using the tools EXPORT THE TABLE DELINEATE THE DATA as for middle initials and apt numbers sort your data insert a column to the right of the middle initial and to the right of the apt number or po box number for that matter to the right of the cells you need to merge use import into dbase and query them were you need them save table you should be setting field size, as well as required and input mask open your data entry form in design insert the new cells from the table. Open your report insert the fields as they want them and your are done.

    AGAIN!!!
    You will not find VBA Code to fix A BAD DESIGN problem with out compounding errors considering the data entry is manual and can not be expected to be with out errors example more than one space between even just one or more of your data fields or double entry between lines of data you can not even set a control to assure the data gets entered correctly.

    However you can write code to build pull down list boxes and/or combo boxes to the entry form and reduce the amount of physical typing something that would fill in relative data based on say a customer number or first and last name a zip code based on city and state states can be there own list box.
     
  5. Refurb

    Refurb Private E-2

    You have to give me a little credit here. You have made many erroneous assumptions with no basis.

    I never said we were entering the data. Why would we enter it wrong and then cut and paste it to make it right into obviously well designed fields and tables? This data is not something we are entering. This is generated from an outside company feeding us data through an email. I would rather have it via an export, but this is my only option. Although i am not happy about that, this has nothing to do with my data entry people and has nothing, I repeat nothing to do with my data table structures.

    Please, I can do without the condescending attitude. I am not a novice nor the flaming idiot you are suggesting, thank you though. I have been building Access databases for 5 years now. When I posted this, I was assuming that I would get at least enough respect that I did have a clue as to what I was doing. Your attacks are unnecessary. I am not going to banter back and forth with you. If you can't help me, don't reply.

    I requested help using code snipits from someone who had to deal with this or a similar issue from their past, not evaluate my sanity as a database designer. But thank you again.

    Again, I am currently cutting the data from an email and pasting it into a single field on an access form. Then I cut and past each peice of data into the appropriately designed fields it would have been entered into had we had a competent order entry person do it right in the first place.

    I assure you, this can be done. I have already written a peice of code to handle this right now but I can't get VBA to recognize a line break or I would have the problem solved.
     
  6. Kodo

    Kodo SNATCHSQUATCH

    the answer to this question is to use Regular Expressions to parse out the sections. If I had the time, I would write it for you, but I simply do not.

    If you're interested on learning how this can be done via regular expressions, I can drop you a bunch of links in this post.
     
  7. jewlzs

    jewlzs Corporal

    The VBA CODE
    What you are resolving is (by text book terms) Importing linefeed-delimited text files. You have explained you have written code to import the data from emails I am AGAIN going to assume the data is first imported in a LineDelimited Notepad and then you wrote code to table it...(?)
    Name
    address 1
    address 2
    city
    state
    zip

    The top experts of dbase have written this...

    A linefeed-delimited text file that has one field per line --the fields are delimited by a linefeed. Acess has no built-in functionality to import linefeed-delimited files. If you need to import a linefeed-delimited file, you have to write code to do it.

    1. learn the order of the fields in the text file
    2. define a table with the same structure as the data you are going to import
    3. learn the number of fields (lines) per record
    4. open the file with the open statement
    5. read one line at a time from the file, using the line input#statement
    6. write the string you have just read from the text file out to the appropriate field in your database
    7. if the line you have just read is the last field of a record, test for end of file
    8. if you are not at the last field of a record or the end of file tests false[//I]repeat steps 4,5,6, and 7

    You must start with a valid text file. Note: if any one of the data lines does not have for example an address 2 field and suppose that a blank line was not included where the address 2 field should be the import will fail because the import must read each line sequentially and assume that each line corresponds to a specific field in the dbase and will throw off all corresponding fields in every sequential record by one line.

    AS YOU CAN SEE EVEN ONE FIELD OUT OF PLACE IN A LINEFEED-DELIMITED FILE RENDERS THE FILE USELESS. TO IMPORT A FILE LIKE THIS ONE, YOU NEED TO EDIT THE FILE IN A TEXT EDITOR PRIOR TO WRITING CODE TO IMPORT IT.

    The first step to importing a text file through VBA code is to understand the data in the file and to create a table with a matching structure.

    You should create a new table to match the structure of the text file and import the data into the file so you can look at the data to make sure it was imported correctly and use append queries to move the data to the appropriate tables in your database. This technique can save you 6,000 headaches

    After you have created the table to match the structure of the text file write this code to import the text file this is a module for importing the sample text.

    Sub ImportLFDelimitedFile()
    On Error Resume Next ' You should always use error trapping!
    Dim szInput As String, db As DATABASE, rst As Recordset
    Set db = CurrentDb( )
    Set rst = db.OpenRecordset("tblLineFeed Import")
    Do
    rst.AddNew
    Line Input #1, szInput
    rst![ID] = Val (szInput)
    Line Input #1, szInput
    rst![Name] = szInput
    Line Input #1, szInput
    rst![Address 1] = szInput
    Line Input #1, szInput
    rst![Address 2] = szInput
    Line Input #1, szInput
    rst![City] = szInput
    Line Input #1, szInput
    rst![State] = szInput
    Line Input #1, szInput
    rst![Zip] = szInput
    Line Input #1, szInput
    rst![DateCreated] = szInput
    rst.UPDATE
    Loop Until EDF(1)

    Close #1

    End Sub

    When you write code to perform imports you need to make provisions for the data you are reading from the text file. When using the Line Input statement to read one line at a time, you always need to read the data into a string variable. Not all the data types in your file will be strings. Example in the code I provided the ID field is a numeric field yet the line is read from the tet file as a string. To work around this Simply encapsulate the string in the Val() function. The Val function returns the numeric equivalent of a string passed to it. You will use the val function whenever the data you are reading into a string variable needs to be converted to a number.

    Another provision you may need to make will be importing a blank line as in address 2 information access by default set the "Allow Zero Length" property to "False" and if you import a blank line the string will be set as " " - a zero-length string. You need to either change the property in the table itself or write code that converts all zero-length strings to nulls.

    I would suggest reconsidering and providing an exported form to dynamic HTML format and placed in a server-side folder defined with read/execute access on the server.
     
  8. Kodo

    Kodo SNATCHSQUATCH

    this is why you use regular expressions, because you will be able to get the data you need and it doesn't have to be in order in the address block.

    Provided it is in proper address format, then something like this could work
    Code:
    ((\w+)\s(\w\.)?\s(\w+)\n)
    ([\w\d].*)\n?
    ([\w\d\s].*\n)?
    (\w+),?\s([\w]+)\s([\d\w]+)
    
    Mind you this was a quick pattern which can be simplified and refined quite a bit more, but for brevity and example, I put this together.
     
  9. jewlzs

    jewlzs Corporal

    So the code you exampled will interpret the difference between an email imported address with a address 1 and address 2 line from an address with only an address 1 line?

    Because...I have read from the dbase experts that the variations and human error of data imported from text box format that access can not interpret and convert the variated data without error

    In my humble opion he should create a htlm form from access and send the form to all email senders or password access to the Companies web site for a data input form the email senders can use. Either will link the data directly to the appropriate table(s) in the correct cell format he needs.
     
  10. Kodo

    Kodo SNATCHSQUATCH

    As stated, the regex was brief and can be updated to support more variant type entries. But the short answer is quite possibly. RegEx uses pattern matching to find and match items. I built a parser for Verizon Super Pages that does a similar thing.

    For instance: (a format example from super pages)
    [FONT=geneva,arial,helvetica][SIZE=-1] Some Law Firm
    Some Slogan
    121 East Water Street, Syracuse, NY 13202
    (315) 555-5555
    [/SIZE][/FONT]www.somesite.com

    My regex will take a look at the above, sees that there is a slogan in it and removes it leaving only the pertinant information. Then I can go in and take the state, zip, phone and URL seperately..meaning those items could be in any order and I can still get them.

    Take some time to read about Regular Expressions and how they can help you. You'd be surprised how powerful they can be.
     
  11. jewlzs

    jewlzs Corporal

    I understand what you are saying. But??? cant you say that the best and easiest resolve to the intial query is to create a html form with direct or password direct access to input data????? Today's Micro Soft Access allows access to data in this format the easiest and most direct method of input.
     
  12. Kodo

    Kodo SNATCHSQUATCH

    Easy..sure.. scalable.hardly. With a pattern, if any changes are made to the format, it's less likely to go belly up than with string manipulation. Easy isn't always better. Maybe for the short term, but if you build software, you should try to make it easy for yourself LATER.. not now. Quick and dirty often leads to hacky code and if the code goes live, well short of rebuilding it, the typically fix for any bugs is also hacky and hard to maintain.

    Sure, there are times when easy wins out for the long term. I've had quite a few of those moments myself but the underlying cause for this was partly luck and partly a controlled environment.

    If I could build it so it caught 100% of the standard formatted address and 90-95% of the anomoly (non-standard) formatted address AND partials, and yours could do 100% of the standard and 25% of the non-standard with no partials.. who's software is the client likely to buy?
     
  13. jewlzs

    jewlzs Corporal

    When I referred to "easy" I hope you understand I used that term loosely to shorten the words: my true intention in that remark was to avoid spending sleepless nights writing vba code for data input sent via unstructered email this is a wheel already built (access has it written and is available in a form version) - and so the use of easy is the ITs' insight and knowledge of the softwares abilities to keep up with the companies needs knowing this task can simply be resolved using the built in tools.

    Show off your impressive expert knowledge. Change has to be made so tackfully articulate the current procedure is inefficient and outdated as well as time consuming and therefore not cost effective. Write up a simple proposal include the insignificant time it would take to build a multiuser form create the how to use guide for the off site users and stress the real-time access to this data and how this upgrade to companies procedure of data retrieved via a structured form from email will also significantly reduce any errors by at least 98%.

    (I truly believe writing code is impressive but save it for a real quandry)
     
  14. Kodo

    Kodo SNATCHSQUATCH

    That's very short term thinking.. if you can have something you can make once that is almost 100% agnostic from any changes to the source, then you rately have to do any modifications to it. I'd rather spend the time to make it work so it flexes with any underlying change than to tightly couple the interfaces. This is scalability. This also allows you to do other things while not having to worry too much about your app breaking if the source changes. I would also present the "Insight and knowlege of the softwares abilities" also includes the ability to program using VBA as it's part of the suite.

    In the software world, if you really want it done right, you have to get into code. ;)
     
  15. Scelera

    Scelera Private E-2

    So I understand that this thread is now 6 years old and I'm reviving a dinosaur.:p

    But I am in a similar problem where I need to split a field into at least 2 other fields. So I went trolling google to see what I could find and ended up here.

    All I have to say is WOW!!!!

    @jewlzs - you need to get off your fricking horse man!!!! We are all very happy that you have read so many experts, but that doesn't hold squat for comparison to real world aplication. The world isn't all puppies and candy canes, some of us have to do it the hard (and usually better) way of writing code.

    Such as myself. I cannot get the data in any other format. This isn't Burger King my friend, we can't always get it our way.

    So if there is anyone who would like to actually help I would greatly apprechiate it. And of course will be able to provide more details.:)
     
  16. LauraR

    LauraR MajorGeeks Super-Duper Administrator Staff Member

    Hi and welcome to MGs.:)

    While the OP's question may be similar to yours, you'll need to start your own new thread for it. It's always best to have Your question as the top one to avoid confusion since that is the question people think they are answering, not one in the middle of the thread that they may not even see. Plus, you'll get more of a response to a new thread if anyone knows the answer.

    Feel free to link to this thread if you want, but you should explain in as much detail as possible what your specific question is.

    I am going to lock this thread as it is very old.
     
Thread Status:
Not open for further replies.

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds