Delete 0 After Dash

[JKJQW]

I need to update a field (field4) so it will get rid of the 0 after the dash. example1 0001-001 example2 222-001-02
The following query works with example1 only. Is the Iif statement the way to go here? I tried it a few different ways without any luck. The desired results are example1 0001-1
example2 222-1-2.
UPDATE [after query1] SET [after query1].FIELD4 = Left([FIELD4],InStr([FIELD4],"-")) & Right([FIELD4],Len([FIELD4])-(InStr([FIELD4],"-")+1))
WHERE ((([after query1].FIELD4) Like "*-0*"));

thanks for any advise

 

[theprez]

Try this. You will have to call this from your query. I used the immediate window to test the function and it seems to work.

Function result(Field4 As String) As String
Dim cnt As Integer
Dim blnAfterDash As Boolean
Dim blnZeroOK As Boolean

blnZeroOK = True
blnAfterDash = False

For cnt = 1 To Len(Field4)

If Mid(Field4, cnt, 1) = "-" Then​

blnAfterDash = True
blnZeroOK = False
result = result & Mid(Field4, cnt, 1)
Else
If blnAfterDash Or blnZeroOK Then
If Mid(Field4, cnt, 1) <> 0 Then
result = result & Mid(Field4, cnt, 1)
blnAfterDash = False
blnZeroOK = True
ElseIf blnZeroOK Then
result = result & Mid(Field4, cnt, 1)
End If
Else
result = result & Left(Field4, cnt)
End If
End If​

Next cnt
Field4 = result
End Function

 

[Jon K]

Alternatively, if you are using Access 2000 or higher, you can use the Replace() function in code.

Create a public function in a Module:-

Public Function DeleteZeroes(Fld As String) As String

    DeleteZeroes = Left([Fld], InStr([Fld], "-")) & _
        Replace(Mid([Fld], InStr([Fld], "-") + 1), "0", "")

End Function

Change the Update Query to:-
UPDATE [after query1] SET [after query1].Field4 = DeleteZeroes([Field4])
WHERE [after query1].FIELD4 Like "*-0*";


Note: On my system, I can't use the Replace() function in queries. I keep getting the error "Undefined function 'Replace' in expression". So I can only use it in VBA.

 

[DALeffler]

Doesn't the Replace function replace all the "0"s (0001-010-010)?

Yet a third solution:

Function fncDelZeros(fld As String) As String

    While InStr(1, fld, "-0") > 0
        fld = Left(fld, InStr(1, fld, "-0")) & _
                 Mid(fld, InStr(1, fld, "-0") + 2)
    Wend
    
fncDelZeros = fld

End Function

Note:

"0001-000-01" returns "0001--1"
"0001-000-00" returns "0001--"
"0000-000-00" returns "0000--"

Change the Update Query to:
UPDATE [after query1] SET [after query1].Field4 = fncDelZeros([Field4]);

 

[Jon K]

Doesn't the Replace function replace all the "0"s (0001-010-010)?

No, it doesn't when used in my function above. It will return 0001-1-1.

 

[DALeffler]

Doesn't the Replace function replace all the "0"s (0001-010-010)?

No, it doesn't when used in my function above. It will return 0001-1-1.

I understand, but shouldn't it return 0001-10-10?

 

[theprez]

If this works, its much simpler than my solution. One thing we need to check to ensure any 0's after valid numbers remain (i.e. 001-010-02 should return 001-10-2

 

[Jon K]

I see what you mean. Replace() can't return 0001-10-10 or 001-10-2. So most likely my function is not what the poster needed.