Need part of string, having trouble

T

Tasha

I have a table with a field SVCCD, and I only need the information from the
left to the - and from the right to the . See below for examples

SVCCD Needed Information
01-30000.002-0110 01-002-0110
1419622-30100.013-0220 1419622-013-0220
EJ-30000.007-277 EJ-007-277

I have tried variations of MID, LEFT and INSTR and nothing I've used is
working....HELP please!!!
 
D

Dale Fye

Tasha,

Try:

Left(SVCCD, instr(SVCCD, "-") & Mid(SVCCD, instrrev(SVCCD, ".") + 1)

If you are not familiar with the Instrrev( ) function is is just like the
INSTR( ) function except it searches from the end of the data. Based on what
the limited dataset you have provided, you could use either one.


Dale
 
T

Tasha

Thanks David, I am getting an invalid sytax error though. I tried putting
brackets around the field name, but got same error???
 
D

Dale Fye

I dropped a parenthesis.

Left(SVCCD, instr(SVCCD, "-")) & Mid(SVCCD, instrrev(SVCCD, ".") + 1)

HTH
Dale
 
T

Tasha

well, I tried that and am still getting Invalid Syntax. The cursor goes back
to and highlights Left...does that mean anything?
 
D

Dale Fye

That implies to me that one of your references is missing.

Open a code window (ctrl-g). Then go to Tools -> References.

Look for one that says Missing, write down the name, uncheck it, then click
OK.
Then go back into the references, find it and check the reference.

Dale
 
O

Ofer Cohen

When you get an error in a query about a build in function it usually
indicate that you have a missing reference.
Open Code any where (or press Ctrl+G), in the menu bar select Tools > Refernce
check if the word Missing apear next to one of the objects that has a
reference, if that the case UnCheck it.

Then try Dale suggestion, it should work
 
T

Tasha

didn't find anything that said Missing, so I started a new query and pasted
your functions in, is working now, guess there was something wrong with the
query....Thanks so much for your help Dale!!!
 
Top