473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How extract certain parts of data from memo and make it a variable?

VBA question for Access 2010:
I have a MEMO field in a form where users will paste a few lines of information. The structure/characters of the pasted information will always be in the same format, however the data will change (For example, 05/12/2012 may be 01/01/2013, but it will always be in the format of 99/99/9999. The information will also always be in the same order.(For example: the word "amount" will always come before $0.00.) There are multiple lines in the information that contain currency, dates, categories, etc. How can I use VBA to read the entire set of information that is pasted in the MEMO field, but set certain parts of the information to Variables. I want to call the extracted variables and place them in certain form fields.

My goal is to have VBA code read through the few lines of information and separate the data into different fields without the USER manually doing it.

Thanks for your help!
May 12 '12 #1
8 5037
zmbd
5,501 Expert Mod 4TB
Courious, why are you using a memo field for this information instead of seperate fields within the data table?
-z
May 12 '12 #2
zmbd- Because of the amount of data and frequency of input, my goal is to have VBA code read through the few lines of information and separate the data into different fields without the USER manually doing it.
May 13 '12 #3
zmbd
5,501 Expert Mod 4TB
Once again... I still don't understand the use of the memo field.

The reason I propose for the separate fields is to help ensure that the data is entered into the correct fields all of the time... what happens if the user enters the "Amount" first when the date is supposed to be first?

I have several very picky users in my work-group.... I have forms with as few as 1 or 2 controls and as many as a dozen... the entries number from one or two a year to somewhere in the hundreds per day... the users are happy and the data can be verified; thus a properly designed form should make your life SO much easier and the users shouldn't have an issue as the "learning-curve" should be fairly easy.

Can you give me an actual example of the user's data entry, obviously using some "Safe" data.
May 13 '12 #4
Mihail
759 512MB
If the data in your memo field have the same format (I understand: length and words positions) must be easy to design code to extract certain information by simple using MID() function.
Of course, depending your data, can be other (better) approaches.

I have a question:
You say that the user paste data in this field. So, the information is, somewhere, in your computer before the user copy it.
Are you think about using directly the source ? (Avoiding the copy-paste process and even avoiding to store in your database the memo field ?)
May 13 '12 #5
Hi ZMBD and MiHail- Thanks for your responses. The information they will copy and paste will always be in the same format/position, so there will be no issue with the information being in a different order. Here is an example of what they will copy:

5/1/2012 Category1 $99.99
5/1/2012 Category2 $99.99
5/1/2012 Category3 $99.99
5/1/2012 Category4 $99.99
5/1/2012 Category5 $99.99

As you can see the data will always have the date, then tab, then the Category, then tab, then the dollar amount. Then a new line will be a different field containing data for category 2.

The user will copy all the data from a website as if it were a paragraph. Then they will paste it in a form field, and it will read through the code and organize it into the respective fields.

I chose MEMO field, because I'm not sure what would be a better option for this type of task. Thank you for your help!
May 13 '12 #6
MiHail- I have them manually pulling the data because it will be available online, but not always at the same time/date. So I figure it would be better to just go to the site and copy the info.
May 13 '12 #7
zmbd
5,501 Expert Mod 4TB
@ryaanmichael

Text String:
If this is a tab delimited string then something along the following:
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
might work... it reads like stereo instructions. I found this while looking for a solution to a somewhat similar problem.

It would be nice if I could help you with automating the update; however, I'm not sure how one would open the web-page and then pull the data.
-z
May 13 '12 #8
TheSmileyCoder
2,322 Expert Mod 2GB
I have created a small database that should serve fine as an example of how it can be done. Depending on your regional settings you may need to make small changes to how the text is parsed.

The sample contains a table to store the parsed information, based on your example data. It also contains a main form handling the processing, as well as a bound subform to display the parsed data.

Note that I don't use a memo field, I simply use a unbound textbox on the unbound main form, to paste the data into. I use the textbox OnChange event to trigger the processing. You may want to switch it, so it only happens on button click, but thats up to you.

Code behind the form, in case someone does not want to open the attachment:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5.    'Delete any records in temporary table
  6.       CurrentDb.Execute "DELETE * FROM tbl_Parsed"
  7.  
  8. End Sub
  9.  
  10.  
  11.  
  12. Private Sub tb_ParseInput_Change()
  13.    On Error GoTo err_Handler:
  14.    Dim strInput As String
  15.    'Get pasted string
  16.       strInput = Trim(Me.tb_ParseInput.Text) 'Important, use Text, not value
  17.  
  18.    'Remove any linebreaks that preceeds parse info
  19.       Do While (Left(strInput, 1) = Chr(13) Or Left(strInput, 1) = Chr(10))
  20.          strInput = Trim(Mid(strInput, 2))
  21.       Loop
  22.  
  23.    'Store each row in an array
  24.       Dim strRecords() As String
  25.       strRecords = Split(strInput, Chr(10)) 'May have to replace chr(10) with chr(13)
  26.  
  27.    'Check that info is present
  28.       If UBound(strRecords) < 1 Then
  29.          MsgBox "No valid information pasted", vbOKOnly + vbInformation
  30.          GoTo exitSub
  31.       End If
  32.  
  33.    'Open recordset so we can store data
  34.       Dim rsParse As DAO.Recordset
  35.       Set rsParse = CurrentDb.OpenRecordset("tbl_Parsed", dbOpenDynaset)
  36.  
  37.    'Loop through strRecords adding each record to table
  38.       Dim intI As Integer
  39.       Dim strRecord() As String
  40.       For intI = 0 To UBound(strRecords) - 1
  41.          strRecord = Split(Trim(strRecords(intI)))
  42.          rsParse.AddNew
  43.             rsParse!dt_Date = strRecord(0)
  44.             rsParse!tx_Category = strRecord(1)
  45.             rsParse!cur_Amount = Replace(strRecord(2), "$", "")
  46.          rsParse.Update
  47.       Next
  48.  
  49.  
  50.  
  51. exitSub:
  52.    On Error Resume Next
  53.       rsParse.Close
  54.       Set rsParse = Nothing
  55.       Me.tb_ParseInput = Null
  56.       Me.ctrlSubform_DisplayParsedInfo.Requery
  57.    Exit Sub
  58.  
  59.  
  60. err_Handler:
  61.    MsgBox "An error has occured" & vbNewLine & Err.Number & " - " & Err.Description, vbOKOnly
  62.    Resume exitSub
  63.  
  64. End Sub
  65.  
Attached Files
File Type: zip TextParser.zip (45.4 KB, 218 views)
May 14 '12 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Scott | last post by:
I have a text field that I am using in a query. It looks like this "123456". What I want to do is pull out the "1" "2" "3" "4" "5" "6" separately and put it in a report. I can pull out the...
3
by: Sashafay | last post by:
Hi, How I can count certain word in memo field? For example I have 15 words in one field and "PLUMING" word repeats in it 3 times. So I would like my code give me number three as the answer....
5
by: klall | last post by:
Hello. I need to extract date information from a memo field entered in the following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004 01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The...
8
by: Fabian Braennstroem | last post by:
Hi, I would like to remove certain lines from a log files. I had some sed/awk scripts for this, but now, I want to use python with its re module for this task. Actually, I have two different...
0
by: Sandeep Khandelwal | last post by:
Hi All. I want to extract and Load data from PostgreSQL using Libpq C API. Please let me know which approach will be good to load large number of rows into PostgreSQL(Insert or COPY FROM) and,...
0
by: mix01 | last post by:
Hi, I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help. Many thanks, Mix01 Version of the program
1
by: samiul | last post by:
HI, Here is a question that is , is it ever possible to pass data from PHP variable to Javascript variable? if yes then please let me know and if not why? I think it is possible... Samiul haque
1
by: Computernut234 | last post by:
Hi, i'm doing a project for my Java class and I know how to add text to a .txt document and remove the entire text but I do not know how to only remove certain parts of text. My code is supposed to...
0
by: neeru29 | last post by:
I'm using Pcapy and impacket module for packet sniffer. I'm able to capture the whole data in a variable and display it. I want extract the IP addresses , Port no's and Payload data into separate...
6
by: Peter Thomson | last post by:
I have a memo field with a mixture of numbers and text. I would like to add up all the numeric values to obtain a total. Numbers can be in the format 01 or 1, all numbers are no more than 2 digits,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.