String parseing 1

[ThunderGeek]

I am writing an Access 2003 query and having trouble parsing a string.
Example of the string - This is my string, it has two commas, and a number 1234. -
I need to remove the string section from the left side, up to but not including the comma.

Here is what I am using in my query, string is the "string" pulled from a table:

String: string
Count: Len([string])
Comma: InStr([string],",")
RemoveToComma: Left([string],[comma])

The strings are random length.
Any help is appreciated.

 

[Golom]

When you say "... remove the string ...", do you mean

delete the characters up to the first comma and leave the remainder (i.e. after the comma) as the result?

OR

pick up the characters up to the first comma and report that as the result?


The first is obtained as

Left([String], Instr([String],",") - 1)

and the second is

Mid ([String], Instr([String],",") + 1)

Note: in SQL you cannot use a field alias ([Comma] in your example) as a value elsewhere in the SQL statement (except for Union queries).

 

[ThunderGeek]

I think I wrote the question wrong. If the string is -
"This is my string, it has two commas, and a number 1234."

I need the result to include this fragment from the left side of the string - "This is my string, it has two commas," The last comma - ", and a number 1234" will always exist as read from the right hand side of the string.

I think the parse needs to come from the right hand side of the string. Does that make sense?
Thanks TG

 

[PHV]

Have a look to the InStrRev function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ThunderGeek]

InstrRev function - that works!
Here is the solution-

(string is a field from my table)

My query fields are:
String: string
Count: Len([string]
Comma: InStr([string],",")
ToComma: InStrRev([string],",",-1)
remove to comma: Left([string],[tocomma])

"Remove to comma" gives me the result I need.
Thanks for the help,
sorry if if messed up the post.
TG