Extract 'weight' from memo field stored in multiple lines as shown in example. Appreciate any help. 1

[zsyed]

PartID: XYZ rev:1
Custom memo field data:
- Hydraulically controlled hose reels
- Hydraulic tank: 43 US Gal
- Fuel tank: 22 US Gal
- Single Point Lift with 7 1/2" x 3 1/2" ID Forklift Pockets
- 2" x 2" reinforced square tubular frame
- Floor pan with 2" lip for spill containment
- Hydraulic Connection: Standard CT Style
- Powder coated: LS Standard - Yellow Frame w/ Red Internals
- Approx. Dims (L x W x H): 99" x 55.75" x 72.5"
- Approx. Dry Weight: 4900 lb. (2222 kg.)


I need an output such that:
PartID Rev Data from Memo field
XYZ 1 - Approx. Dry Weight: 4900 lb. (2222 kg.)

 

[simian336]

There are better minds here then mine... but if all you data is that clean you could probably export each row of you "memo" field (that is an Access term that I find myself using as well, probably varchar max?) table to indivdual rows then query it from there.

Just a thought.

Simi

 

[dhookom]

I expect you could use SUBSTRING and CHARINDEX function like:

SELECT PartID, Rev, Memo, SUBSTRING(Memo, CHARINDEX('- Approx. Dry Weight',Memo,1),100) ApproxDryWeight
FROM ttzsyed

This assumes the text is no longer than 100 characters and is at the end of the memo field text.

Duane
Hook'D on Access
MS Access MVP

 

[zsyed]

Thanks a lot! SQL code helps.