blank spaces after data in field is causing problems

[joe789]

Hi Folks,

I am creating an IF CASE statement in SQL that basically takes the 2 last characters in a field and if they happen to be 'HX' then a special rate is used to calculate the cost whereas if their is no 'HX' as the last 2 characters of the field then a different rate is applied.

(Right([BOARD SPEC DATA],2)) has always worked flawlessly in SQL to render similar solutions. However, unfortunetly the weekly extract that comes into us permits for massive amounts of blank but real spaces after the data in [BOARD SPEC DATA] field so what ends up happening is that (Right([BOARD SPEC DATA],2)) yields a (SPACE SPACE) value even thou many of these values should be 'HX' as they are the last 2 real non-space values at the end of the field.

If there any easy way to fix this keeping in mind that this is how we get the extract everyweek so a search/replace on spaces won't work ... is there any way I can use a Right command but ignore spaces so that it simply renders the 2 real characters at the end of the field as oppossed to blank spaces?

Any help is greatly appreciated.

Thank you very much,


Joe

 

[RuralGuy]

Use Trim([YourField]) to eliminate leading and trailing spaces.

 

[pbaldy]

Try Right(Trim(...),2)

 

[Matt Greatorex]

If the blank spaces are always just at the end of the [BOARD SPEC DATA] field, you could use

Dim li_Position as Integer

li_Position = [I]Instr([BOARD SPEC DATA]," ")

to find out the position of the first one.
(In "TEST123 " it would find 8)

You could then use

Dim str_String as string

str_String = Left([BOARD SPEC DATA],li_Position - 1)

to return the 'useful' part of the field (i.e. the left seven characters - "TEST123")

Then use

(Right(str_String, 2))

to return the bit you actually want.

I'm sure someone more experienced than I will point a more 'correct' method, but that should do the trick.