eliminate characters after the "."

[wizard607]

I have a parts table
1234.prt.1
1234.prt.2
1234.prt.12
I want to run a duplicate query but first I have to eliminate the characters after the last "."
any help would be appreciated

[This message has been edited by wizard607 (edited 09-21-2001).]

 

[Fornatian]

Add this function to a public module:

Public Function TextBeforeLastDot(TheField As String) As String
Dim i As Integer
Dim tmpStr As String
'reverse the string
For i = Len(TheField) To 1 Step -1
tmpStr = tmpStr + Mid(TheField, i, 1)
Next i
'find out where the first(last) occurance is.
'logic dictates that it is x character back from the length of TheField
i = Len(TheField) - InStr(1, tmpStr, ".", 0)
'return all characters before the dot
TextBeforeLastDot = Mid(TheField, 1, i + 1)
'knock the +1 off if you don't want the last dot
End Function

First run a select query using TextBeforeLastDot([TheFieldToUpdate]) as a column header to see if the result is what you want.

If so convert it to an update query and put
TextBeforeLastDot([TheFieldToUpdate]) in the 'update to' row.

HTH

Ian

 

[wizard607]

Thanks alot for your help.