 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008select specific character from memo field
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 "". 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 '==================================================== > 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 "". 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. >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 [quoted text clipped - 19 lines] >> another field. The placement in the memo field is random but the code is >> always surrounded by "". 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 '==================================================== > Thanks John- > [quoted text clipped - 35 lines] >>> another field. The placement in the memo field is random but the code is >>> always surrounded by "". |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.