Finding character to parse a string in query

Question

I have a query that uses a project tracking code (text string) from a data table and i want to be able to use only part of the string  as part of the queries results

example strings

MH-10-1112:P2

DART-01.A-1213:Q4

CCS-15.2-1112:R3

Part one of my question is I want to be able to extract the first part of the string prior to the first "-"

e.g

MH

DART

CCS

Any pointers?

Part Two: It would be realy useful to know how to extract other substrings from this project code

The four parts of the code are deliniated by -, -,:

The  fisrt part is alphabetic charecters only

The second part is alphnumeric and can optionally have another substring or alphabetic charecters indicating either a repeat of a project .01, 02 etc)  or alphabetic indicating a subproject (.A, .B, etc).

Third part is a four character financial year represntation (e.g. 1213)

The fourth part a plan code (P2, Q3) allways single character and single number

Again any pointers.

Many thanks for reading my question.

Trevor

Answer 1

For question 1, you'd do something like:

=Left([YourFieldName], InStr([YourFieldName],"-")-1)

For question 2, you need to create a VBA procedure to do what you need in which you will utilize functions like Split(), Mid(), InStr(), InStrRev(), Left(), Right(), ...

The bigger issue here is why is such a string being stored in your db in the first place?  Each component should be stored in its' own field, and you can concatenate them as required to display them however you need on your forms, queries, reports,...  This is a fundamental flaw in design that you should addressed before moving forward.  It will continue to cause you headaches (refer to questions 1 & 2) until you do.

Answer 2

Part 1 is easy. Use the Left() function with the Instr() Function

Left(string,Instr(1,string,"-")-1)

The Mid and Instr functions can be used for any parsing operation within a string.