# Thread: Problem forming Query coding

1. Registered User
Join Date
Dec 2016
Posts
2

## Unanswered: Problem forming Query coding

Hi Guys & Gals
I am trying to make Access product what I previously created in Excel
Iam hopeless at Coding so Iam trying to do it in a Query
I already know its probably hopless, but here goes

The Excel sheet used two formula to remove a trailing - [dash] from a series
Kinda clever as not all the series have a trailing dash.. so CANT USE TRIM
This was the code =LEFT(B2,LEN(B2)-(RIGHT(B2)="-"))
I would like to duplicate the result in a Query if at all possible

A series of the numbers looks like this

07500-032-23-
07500-032-24-
07500-032-25-
07500-032-26-
07500-032-27-A
07500-032-27-B
07500-032-28-A
07500-032-28-B

output need is

07500-032-23
07500-032-24
07500-032-25
07500-032-26
07500-032-27-A
07500-032-27-B
07500-032-28-A
07500-032-28-B

Any one got any solutions ?
They will be most appreciated

Cheers

Paul

2. Moderator
Join Date
Mar 2009
Posts
5,442
Let's call the table [MyTable] and the column you want to use in the query [Field1]
The query that will yield the results you're looking for goes like this:
Code:
```SELECT IIf(Right([Field1],1)= '-', Left([Field1], Len([Field1])-1), [Field1]) AS MyField1
FROM MyTable;```
If [Field1] can contain NULL values, use:
Code:
```SELECT IIf(Right([Field1],1)= '-', Left([Field1], Len([Field1])-1), [Field1]) AS MyField1
FROM MyTable
WHERE [Field1] IS NOT NULL;```

3. Registered User
Join Date
Dec 2016
Posts
2

## Thanks

Thanks with a bit of testing I managed to work it out using your code
I ended up drawing on the field called 'sub'
creating a new field called cleaned
I don't have to handle null's but nice to have the code..

Added a line to my Query

Cleaned: IIf(Right([sub],1)='-',Left([sub],Len([sub])-1),[sub])

Works a treat