• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Include Txt to a certain character in a string

I have the following EDI message and have the following statement in a query;  

Units: Mid([swift],InStr(1,[swift],":36B::SETT//UNIT/")+17,12)

In this particular line, the number of characters is variable until the ','.  So what I need to do is bring in 3493 in this case but if it was 99999, then I would need to bring in 99999 and so forth.  It is whatever value from the / to the ,.

The above code is bring in characters from the next line.   How do you stop at the ','?

I was trying the charindex function but without much luck.  Any ideas?

:35B:ISIN NO0010985
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you always look for the FIRST instance of the comma, and the get all characters BEFORE that comma up to the preceding slash?

If so, you can do this:

Units: mid([swift],Instrrev([swift],"/",instr([swift], ","))+1,   instr([swift], ",")- Instrrev( [swift],"/",instr([swift], ","))-1)

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
BTW, I'm assuming that [swift] if the field that contains your data above.
mtrussellAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now