Remove Characters After WildCard

[Charles2019]

I've got a data string that has an * in it followed by a comma with data with characters after it. I need to remove the * and all of the characters to the right after it. The range of characters vary in length. Data Sample;
CSQ_ACNY_Team1*, CSQ_ACNY_AllOther
CSQ_ACNY_Team1*
I've tried using this statement Replace([CSQ Names],Chr(42),"")

 

[plog]

Use InStr (https://www.techonthenet.com/access/functions/string/instr.php) to find the location of the asterisk, and then Mid(https://www.techonthenet.com/access/functions/string/mid.php) to get all the characters up to that point:

NewField: Mid([YourField], InStr([YourField], "*")-1)

Also, don't use spaces in field names. Just makes coding and querying that much more difficult.

 

[Deleted member 147267]

Because a limit isn't imposed by that, Mid will return everything right of the find, yes? We want Left, no?

 

[Charles2019]

Because a limit isn't imposed by that, Mid will return everything right of the find, yes? We want Left, no?

Yes, I want everything to the right returned

 

[plog]

Yes, I messed up my initial post, but according to Charles' second post I screwed up and got it right. Here's the right code depending on what you actually want:

First post:

I need to remove the * and all of the characters to the right after it.

NewField: Mid([YourField], 1, InStr([YourField], "*")-1)



Second post:

Yes, I want everything to the right returned

NewField: Mid([YourField], InStr([YourField], "*"))

 

[The_Doc_Man]

There is also a RIGHT(string,n) function that you could use to recover the rightmost N characters.

MS Access: Right Function

This MSAccess tutorial explains how to use the Access Right function with syntax and examples. The Microsoft Access Right function extracts a substring from a string starting from the right-most character.

www.techonthenet.com

Right Function - Microsoft Support

support.office.com

 

[Deleted member 147267]

Hmmm, I wonder how I got the wrong idea?

I need to remove the * and all of the characters to the right after it.

In that case, forget Left function.

 

[Galaxiom]

BTW Another variation is using InStrRev() to find the last instance of a character. Useful if the character occurs more than once.

 

[arnelgp]

use Update Query:

Update yourtable Set [CSQ Names] = Left([CSQ Names], Instr([CSQ Names], "*")-1) Where Instr([CSQ Names], "*") > 0;

 

[Charles2019]

There is also a RIGHT(string,n) function that you could use to recover the rightmost N characters.

MS Access: Right Function

This MSAccess tutorial explains how to use the Access Right function with syntax and examples. The Microsoft Access Right function extracts a substring from a string starting from the right-most character.

www.techonthenet.com

Right Function - Microsoft Support

support.office.com

Running the RIGHT(string,n) function removes the characters, just the varying length that it doesn't cover.

 

[Charles2019]

use Update Query:

Update yourtable Set [CSQ Names] = Left([CSQ Names], Instr([CSQ Names], "*")-1) Where Instr([CSQ Names], "*") > 0;

Query returns Data Type Mismatch in Criteria Expression

 

[Charles2019]

Yes, I messed up my initial post, but according to Charles' second post I screwed up and got it right. Here's the right code depending on what you actually want:

First post:



NewField: Mid([YourField], 1, InStr([YourField], "*")-1)



Second post:



NewField: Mid([YourField], InStr([YourField], "*"))

Plog, Thanks for your help. This works with the exception of the string that do not have the * in the string.

 

[arnelgp]

if this is an Expression in a Query:

ColumnName: IIF(Instr([CSQ Names], "*") > 0, Left([CSQ Names], Instr([CSQ Names], "*")-1), [CSQ Names])

 

[Charles2019]

Awesome, thanks so much for your help.