Problem with complex query

[Never Hide]

Hello guys,

I have a problem with a query I wanna write. I have a field in a table(text field)
which is like this

Field
====
ABC-1
ABC-15
ABC-6

note: it's just 1 column.
What'd I'd like to do is check the number after the "-" and if it's 1digit change it to the same but with a Zeoro (0) in front of him and change the "-" to " - ". So the example will change to

Field
====
ABC - 01
ABC - 15
ABC - 06


If've created a select query first to test and this what I used for a test field is this

test: IIf(Len(Right([tblTest];InStr([tblTest];"-")-1))=1;Left([tblTest];InStr([tblTest];"-")-1)+" - "+replace([tblTest];Right([tblTest];InStr([tblTest];"-")-1);"0"+Right([tblTest];1));[tblTest])

P.S. the "ABC"s i used are just random,the length of the "ABC" isn't always the same for every row in the table

 

[recyan]

Just check if below gives some guidelines :

SELECT 
	myTable.tblTest, 
	InStr(1,Trim([tblTest]),"-")+1 AS [The-EndPosition], 
	Len(Trim([tblTest])) AS TheLengthOfString, 
	[TheLengthOfString]-[The-EndPosition] AS TheDifference, 
	Left([tblTest],[The-EndPosition]-2) & " - " AS TheLeftPartOftblTest, 
	Right([tblTest],1) AS TheRightPartOftblTest, 
	IIf([TheDifference]=0,[TheLeftPartOftblTest] & "0" & [TheRightPartOftblTest],[TheLeftPartOftblTest]+Mid([tblTest],[The-EndPosition])) AS test
FROM 
	myTable;

Thanks

 

[Never Hide]

Thank you very much recyan
it doesn't give just "some guidelines" it gives me the result I was looking for

P.S. I wanted to write "fldTest" don't know why I wrote "tblTest" hehe

 

[recyan]

Thank you very much recyan
it doesn't give just "some guidelines" it gives me the result I was looking for

Glad you found it helpful. Whenever you have time, just check out if I have over-complicated things in the solution.

P.S. I wanted to write "fldTest" don't know why I wrote "tblTest" hehe

That one really had me confused. I thought it was table name & then realized you had used it as field name.

Thanks