select specific character from memo field

  • Thread starter AlanO via AccessMonster.com
  • Start date
A

AlanO via AccessMonster.com

I hope some one can help.

I need to take a specific character(code) from a memo field and place it into
another field. The placement in the memo field is random but the code is
always surrounded by "".
 
J

John Spencer

So you are always looking for one character that is always surround by
exactly one set of quote marks? Does the field ever contain quote marks
surrounding more than one letter?

Assumption there is only one set of quote marks and they are always
surrounding one character.

SELECT MID(SomeField,Instr(1,SomeField,"""")+1,1) as OneChar
FROM SomeTable
WHERE SomeField Like "*""?""*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
A

AlanO via AccessMonster.com

Thanks John-

the code works for some of the records but not all. here are some of the
record it didn' work on.

In 2004, the site has been reclassified to “e†Site Properly Closed, No
further Action Required.

In 2001 the classification of this site was revised to “d†Site properly
closed, requires continued management.

In 2001 the site was reclassified as “d†Site Properly Closed Requires
Continued Management.



the code is pulling out 39 charactere from a table that has 74 records.
 
J

John Spencer

Are those quote marks " or are they “? Those are two different quote
marks? You will probably have to do two queries and union them or use a
custom VBA function to handle the different types of quote mark characters.

Paste the following function into a vba module and call it in your query.

SELECT fGetCharacter(SomeField) as OneChar
FROM SomeTable
WHERE SomeField is not null


Public Function fGetCharacter(strIn)
'Get first character after a quote mark
Dim lPos As Long

lPos = InStr(1, strIn & "", Chr(34)) 'a quote mark "

If lPos = 0 Then
lPos = InStr(1, strIn & "", Chr(148)) ' a quote mark â€
End If

If lPos = 0 Then
lPos = InStr(1, strIn & "", Chr(147)) ' a quote mark “
End If

If lPos = 0 Then
fGetCharacter = Null
Else
fGetCharacter = Mid(strIn, lPos + 1, 1)
End If

End Function

Save the module with a name that is NOT fGetCharacter

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Top