By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,569 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

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

P: 24
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
Share this Question
Share on Google+
8 Replies

Expert Mod 5K+
P: 5,397
Courious, why are you using a memo field for this information instead of seperate fields within the data table?
May 12 '12 #2

P: 24
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

Expert Mod 5K+
P: 5,397
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

P: 759
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

P: 24
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

P: 24
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

Expert Mod 5K+
P: 5,397

Text String:
If this is a tab delimited string then something along the following:
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.
May 13 '12 #8

Expert Mod 100+
P: 2,321
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
  4. Private Sub Form_Open(Cancel As Integer)
  5.    'Delete any records in temporary table
  6.       CurrentDb.Execute "DELETE * FROM tbl_Parsed"
  8. End Sub
  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
  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
  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)
  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
  33.    'Open recordset so we can store data
  34.       Dim rsParse As DAO.Recordset
  35.       Set rsParse = CurrentDb.OpenRecordset("tbl_Parsed", dbOpenDynaset)
  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
  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
  60. err_Handler:
  61.    MsgBox "An error has occured" & vbNewLine & Err.Number & " - " & Err.Description, vbOKOnly
  62.    Resume exitSub
  64. End Sub
Attached Files
File Type: zip (45.4 KB, 172 views)
May 14 '12 #9

Post your reply

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