remove characters without knowing the length 1

[mmasuda]

Hi,
Is it possible to create a query other than using the trim function to remove some unwanted characters from a string.
I have a delimiter in the middle of the string (#) and want everything removed following the delimiter. The number of characters before and after the delimiter vary.

Thank you,
MM

 

[jebry]

Hi!

Try this:

Left([YourField], InStr([YourField], "#") - 1)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[mmasuda]

Thanks for the quick reply.
I tried that but received an "invalid Procedure call" error. I added the following statement to the criteria option of the query: Left([insturl],InStr([insturl],"#")-1).

The SQL statement looks as follows:
SELECT tblinstitution.insturl
FROM tblinstitution
WHERE (((tblinstitution.insturl)=Left([insturl],InStr([insturl],"#")-1)));

Any help will be greatly appreciated.
Thanks,
MM

 

[PHV]

something like this (SQL code) ?
SELECT insturl, Left([insturl] & '',InStr([insturl] & '#','#')-1) AS TrimmedURL
FROM tblinstitution

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

 

[mmasuda]

This worked wonderfully!
Thank you so much.

MM