Retrieving string before a character

[rhettblaine]

Hello all!

I had a column in my Access table that contained this formula:

WGCategory:Left([Workgroup],Len([Workgroup])-5)

The purpose is to show only a string before a - (dash) from a column that contained the following data in a column called [Workgroup]:

[Workgroup]
xxxxx-1504
xx-xx-1504
xxx-1504
xx-1504
xxxxxxx-1504

etc.

However, this formula no longer works because of an update. It gives me the following error:
[microsoft][odbc sql server driver][sql server]invalid length parameter passed to the LEFT or SUBSTRING function. (#537)

My understanding is that it could be 2 reasons:
1) I'm not connected to the SQL Server that parent table is connected to
2) The formula is giving a negative value

Question: Is there an alternative to the formula above where I am able only to retrieve the information before "-1504"?

Thanks!

 

[apr pillai]

Copy and Paste the following VBA Code into a Standard Module and save the module:

Public Function cutString(ByVal strIn As String) As String
Dim strOut As String, L As Long, J As Long
L = InStrRev(strIn, "-") - 1
strOut = ""
For J = 1 To L
strOut = strOut & Mid(strIn, J, 1)
Next
cutString = strOut
End Function

Change the expression in the Query Column to:

WGCategory:cutString([Workgroup])

If the InstrRev() runs normally and your table link to SQLServer doesn't have any problem then it may work.