Replace text before and including - to be empty

[Kirtap]

Hello,
I'm trying to get numbers in some text after a -.

Here is how the text is formated:


D##-###.
So like an example would be: D14-013

I thought this query would get that last bit:

SELECT Replace(GPCL_Asset_ID,'D%-','') AS Expr1
FROM Assets
WHERE Assets.GPCL_Asset_ID Like 'D%' AND Assets.GPCL_Asset_ID NOT Like '%IH%';

However it returns the text as it was (i.e. D14-013 instead of 013).

I'm not sure how to approach this. I thought that this would be the best approach.
My ultimate goal is to find the largest number after the -.

So it end up like:


SELECT Max(CInt(Replace(GPCL_Asset_ID,'D%-',''))) AS Expr1
FROM Assets
WHERE Assets.GPCL_Asset_ID Like 'D%' AND Assets.GPCL_Asset_ID NOT Like '%IH%';

Any help would be appreciated.

Thank you in advance.

Edit: I could do Right(GPCL_Asset_ID, 3). But I'm trying to also account for the possibility it becomes four digits long.

[JoeM]

I'm trying to get numbers in some text after a -.

Try this:

Expr1: Mid([GPCL_Asset_ID],InStr([GPCL_Asset_ID],"-")+1,Len([GPCL_Asset_ID]))

[Kirtap]

Try this:

Expr1: Mid([GPCL_Asset_ID],InStr([GPCL_Asset_ID],"-")+1,Len([GPCL_Asset_ID]))

This works perfectly! Thank you so much!
I'll have to take a look at the Mid function to get a better understanding. Thanks for sending me in the right direction!

[JoeM]

The LEFT, RIGHT, and MID function are just Text functions that tell you where to start in taking characters from a text string. MID tells it to start somewhere in the middle.
The INSTR function tells it to locate where the "-" can be found in your string. Once you found that, you want to start taking the characters just after that (hence the "+1").
So that is how we find the starting point of our MID function.

The last argument of the MID function tells you how many characters to take. Using the LEN function to get the Length of our string. It actually tells it to take more than we need (since we are starting in the middle and not at the beginning), but that doesn't hurt anything.


See:
http://www.techonthenet.com/access/f...string/mid.php
and
http://www.techonthenet.com/access/f...ring/instr.php

[Kirtap]

Thank you very much for the explanation! I really appreciate it. Now I have a better understanding of these functions.

[orange]

??? Are you trying to change the record, or trying to change the display only??