Hi,
I apologise for not providing a code basis for my question, but i really am stuck.
Does anyone know how i can split a memo field, such as below, into separate records. (The data comes from the body of daily system generated emails that the database is linked to)
Single Field:
AUS 123 $450 $396 -4 N
AUS 456 $2560 $2860 300 N
AUS 789 $987 $2 -985 P
and so on....there can be up to 500 lines in each memo field, and a field for each day for the past couple of years. There are specific spaces between each value a carriage return at the end of each row.
What i ultimately need to do is separate each line into a different field, from which i can split using instr and mid etc.
Unfortunately some other software is not an option due to the system controls in place by my employer.
I'm not expecting a solution, but a point in the right direction would be greatly appreciated. I've already spent a day on it, so even if someone says not possible, that would be excellent.
Thanks
Adrian
2 5520
Without any code it is difficult for us to know what you are having difficulty with. You seem to have some knowledge of the instr() function so I am not sure this will help but hey I am throwing it out here. -
Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"), InStr(InStr("YourAUSMemoFieldToPullAUSFrom", "AUS") + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"))
-
Or: -
Dim i As Integer
-
i = InStr("YourAUSMemoFieldToPullAUSFrom", "AUS")
-
Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", i, InStr(i + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - i)
-
Hope this helps.
If the field is only delimited by spaces and CRs, then shouldn't you be able to just take the entire memo field, split it by the CRs [Chr(13)] and then split the results by the spaces [Chr(32)]?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jeremy |
last post by:
I have a pretty straightforward database that is designed to record
free-form information about products (date, source and a memo field).
This is searched and updated via a form. For new records,...
|
by: ICU |
last post by:
Hi,
I have an Access 2000 app that is correctly updating a memo field in
the database from an ActiveXCTL but when I try to export that data to
a RTF the file is created but it is blank. It...
|
by: bu |
last post by:
I have a database field that represents an item description and varying
length. I am using the database to import the item description into another
program (the other program is ODBC-aware ). The...
|
by: MLH |
last post by:
I'm trying to type
(a), (b) or (c)
into an Access 97 memo field.
Access keeps turning it into a copyright symbol ( © )
How can I override that? I don't want a copyright symbol.
|
by: Mark |
last post by:
Hello.
I am attempting to use AppendChunk() to write binary data to a memo field
in Access 2000. My initial call to AppendChunk() results in a data type
conversion error. Any suggestions? Here...
|
by: Jason.Forget |
last post by:
I have created a 4 Reports using the Reports Wizard in Access 2003 and
the text in a memo field is being cut off. This is an inconsistent
issue being that it is affecting just one of the reports. ...
|
by: kaplan.gillian |
last post by:
Hi everyone,
I currently have an Access database that includes quite a few
long memo fields. When I create a report of my data, Access
does not allow the memo fields to be split with the page...
|
by: ARC |
last post by:
This is taken from the "What's New in Access 2007" page. However, I've
looked through all the properties of a text field memo box, and cannot find
the append only option. Does anyone know how to...
|
by: Jeremy Goodman |
last post by:
Access 2007; Merging records containing multivalue drop down lists.
I have a database showing legislation information divided by State/territory. The database needs to be able to show the info...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
| |