Trim-Function in MS Access 2000

[tone1988]

Hi there!
I've got an Excel import in my Access database. Every string has got the prefix =" and the suffix ". is there any short code method to remove these pre- and suffixes by a query?

kind regards
tone

 

[Golom]

Mid(Left(TheString,Len(TheString)-1),2)

 

[tone1988]

thanks for your fast reply!
but is there no chance to handle with the problem by a simple query such as "SELECT TRIM(LEADING '="' FROM 'field'"?
i didn't want to use any vba-code up to now.

 

[Golom]

Hmmmm ...

TRIM [red]IS[/red] VBA code and the syntax you suggest is not valid code.

Using what I suggested in a query would simply be

SELECT Mid(Left([Field],Len([Field])-1),2) As [TrimmedField], ...

 

[tone1988]

ok, it works, but now i have to update the existing value of the field with the new value.

 

[Golom]

OK

UPDATE TheTable
SET [Field] = Mid(Left([Field],Len([Field])-1),2)
Where Left([Field],1) = "=" AND Right([Field],1) = "."

The "Where" clause just ensures that you don't change anything that doesn't have the "[red]=[/red]SomeText[red].[/red]" format.