Question String/Substring Manipulation Access 2007

[bobdydd]

Hi Everybody

I have a form called "frmInboxSaleEbay_D" which has 9 fields

There are 8 text Fields named:
txtItemName
txtListingSalePrice
txtItemURL
txtBuyer
txtEmail
txtBuyerFirstName
txtBuyerLastName
txtBuyerAddress

And 1 Memo field named:
txtContents

txtContents is the data from the message body from an eBay
"You've sold your item" notification (seen below)

So my question is:
How do I extract strings (sub strings) from the txtContents Memo Field
to put them into the correct text field.

e.g.
txtItemName = TROLLEY JACK 2 Ton LONG REACH JACK
txtListingSalePrice = £160
txtItemURL = http://cgi.ebay.co.uk/ws/eBayISAPI.dll?ViewItem&item=200338671345
Etc until the whole 8 text fields contain the correct data

I have looked around the various MS Access help sites, this sort of
string manipulation
is a bit beyond the basic stuff

So I could use some help

Thanks in advance

PS The Contents of the txtContents field is shown below


*************txtContents Memo Field Starts Here**********************

-----------------------------------------------------------------
eBay sent this message to Elvis Presley (ElvisPresley).
Your registered name is included to show this message originated from
eBay.
Learn more at
http://pages.ebay.co.uk/help/confidence/name-userid-emails.html
-----------------------------------------------------------------

-----------------------------------------------------------------
You've sold your item on eBay.
-----------------------------------------------------------------

Dear stortvalley,
Congratulations! Your item just sold. Please ship your item as soon
as
possible after receiving cleared payment. You can send an invoice to
the
buyer as a reminder to pay for this item.

=================================================================
Send Payment Details
http://payments.ebay.co.uk/ws/eBayI...d=200338673643&transId=354019257010&buyerid=0
=================================================================

Item name: TROLLEY JACK 2 Ton LONG REACH JACK
http://cgi.ebay.co.uk/ws/eBayISAPI.dll?ViewItem&item=200338671345
End time: 03-Jun-09 13:30:48 BST
Sale price: £160.00
Quantity: 10
Quantity sold: 1
Quantity remaining: 9
Buyer: gladys clark
gladysclark (mailto: (e-mail address removed)) contact:
http://contact.ebay.co.uk/ws/eBayIS...ested=gladysclark&redirect=0&iid=200331234564
Buyer's shipping address:
gladys clark
29 gracelands crescent
Hackney
London e5 2bt United Kingdom



You've sold your eBay item:TROLLEY JACK 2 Ton LONG REACH JACK
(200338671345)



-----------------------------------------------------------------
Marketplace Safety Tip
-----------------------------------------------------------------
Trade safely. Beware of anyone who contacts you about buying or
selling
outside of eBay
http://pages.ebay.co.uk/help/policies/rfe-spam-non-ebay-sale.html.
When you
trade outside of eBay, you're not able to leave Feedback or take
advantage
of protection programmes and case resolution tools available on eBay.
Report http://contact.ebay.co.uk/ws/eBayISAPI.dll?ContactUs&wftype=3002
an
inappropriate email.

-----------------------------------------------------------------
Learn how you can protect yourself from spoof (fake) emails at:
http://pages.ebay.co.uk/education/spooftutorial/index.html

eBay sent this email to you at (e-mail address removed) about your
account
registered on http://www.ebay.co.uk.

eBay will periodically send you required emails about the site and
your
transactions. Visit our privacy policy at
http://pages.ebay.co.uk/help/policies/privacy-policy.html and User
Agreement at http://pages.ebay.co.uk/help/policies/user-agreement.html
if
you have any questions.

This email was sent by eBay Europe S.à r.l., which may make use of
its
affiliates to provide the eBay services. If you are a non-EU
resident,
please find the contact data of your contracting party in the User
Agreement.
Copyright © 2009 eBay Inc. All Rights Reserved.
Designated trademarks and brands are the property of their respective
owners.
eBay and the eBay logo are trademarks of eBay Inc.
eBay International AG is located at Helvetiastrasse 15/17 - P.O. Box
133,
3000 Bern 6,Switzerland.
eBay Imprint at http://pages.ebay.co.uk/aboutebay/contact.html

*************txtContents Memo Field Finishes
Here**********************

 

[Stefan Hoffmann]

hi Bob,

I have looked around the various MS Access help sites, this sort of
string manipulation
is a bit beyond the basic stuff

Basically it must look like this:

MsgBox ExtractValue("Item name", Me![txtContents])

Public Function ExtractValue(ATag As String, _
ASource As String _
) As Variant

Dim Result As Variant
Dim PosEnd As Long
Dim PosStart As Long

Result = Null

PosStart = InStr(ASource, ATag)
If PosStart > 0 Then
PosEnd = InStr(PosStart + 1, ASource, vbCrLf)
If PosEnd > 0 Then
Result = Mid(Me![txtContents], PosStart, PosEnd - PosStart)
End If
End If

ExtractValue = Result

End Function

Two things you may need to correct:

Depending on your e-mail import, the line break detection must use a
vbCr or a vbLf only.

And you need to correct the length calculation, it should be off by one.


mfG
--> stefan <--

 

[put_upon]

Thanks Stefan

It looks do-able ......That will get me started....I shall apply
myself to learning over the weekend

Thanks for the help....I'll let you know how I get on.

Regards
Bob

 

[kate]

Another command you may want to review is Split(). You can split a string
(memo) into an array.

 

[kate]

Another command you may want to review is Split(). You can split a string
(memo) into an array.