If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query INSTR function?
I'm using a query and need strip out data.
Here is an example of the data I have to work with: 85123147/RS08 85123147W/RS08 TEX-M/SS09 I need my query to strip off everything after & including the "/" so that I get the following results. 85123147 85123147W TEX-M Do I accomplish this using the INSTR function and if so how would it work? I would appreciate some guidance. Thank you! |
#2
|
|||
|
|||
Query INSTR function?
Type an expression like this into the Field row in query design:
IIf([F1] Like "?*/*", Left([F1], Instr([F1], "/") - 1), [F1]) Substitute your field name for F1. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "William" wrote in message ... I'm using a query and need strip out data. Here is an example of the data I have to work with: 85123147/RS08 85123147W/RS08 TEX-M/SS09 I need my query to strip off everything after & including the "/" so that I get the following results. 85123147 85123147W TEX-M Do I accomplish this using the INSTR function and if so how would it work? I would appreciate some guidance. Thank you! |
#3
|
|||
|
|||
Query INSTR function?
hi William,
William wrote: I need my query to strip off everything after & including the "/" so that I get the following results. Do I accomplish this using the INSTR function and if so how would it work? Create a standard modul: Public Function StripAfter(AValue As Variant, _ Optional ADelimiter As String = "/" _ ) As Variant Dim Result As Variant Result = AValue If Not IsNull(Result) Then Result = Left(Result, Instr(Result, "/") - 1) End If StripAfter = Result End Function You can call it in your query: Stripped: StripAfter([fieldToStrip]) mfG -- stefan -- |
Thread Tools | |
Display Modes | |
|
|