Code doesn't work with linked table.

[roc100]

Hi,
I have this great function that works great if I create the table from scratch in the database. The problem is that I need it to work on a table that is linked into my database. For some reason the same function will not work if I try to use it in a query with a table linked to the database. I am new to VBA. Is there a way to fix this? Thanks in advance for your help.

Here is the code:
Function getLinkValue(s) ' takes string and returns substring before last space character in stringmaxi = Len(s) ' length of input string, controls looping through charactersLastSpace = maxi ' position of last space character in stringFor i = 1 To maxi ' loops through every character to find position of last space If (Mid(s, i, 1) = " ") Then LastSpace = i NextIf LastSpace < maxi Then LastSpace = LastSpace - 1 ' if Space was found backtracks LastSpace to last character in string getLinkValue = Mid(s, 1, LastSpace) End Function

 

[jdraw]

When you place vba code in your post, you should surround it with [c o d e] and [/c o d e] tags. (remove the spaces)

Function getLinkValue(s as String) ' takes string and returns substring before last space character in string
maxi = Len(s) ' length of input string, controls looping through characters
LastSpace = maxi ' position of last space character in string
For i = 1 To maxi ' loops through every character to find position of last space
 If (Mid(s, i, 1) = " ") Then 
   LastSpace = i 
 End if
Next
 If LastSpace < maxi Then
   LastSpace = LastSpace - 1 ' if Space was found backtracks LastSpace to last character in string 
  getLinkValue = Mid(s, 1, LastSpace) 
 End if
End Function

I have tried to reformat your function, which initially read like a paragraph.
You may wish to check and see if the function works, and if so, works as you want it to work

 

[roc100]

Thanks I will be sure to do that in future posts. I tried the function and it does work in
the table I created within the database, but not in the table that is linked from another source. Any suggestions?

 

[spikepl]

You may want to reveal to the readers what "doesn't work" is. Such a phrase means nothing to people who are not seeing your screen.

What are the symptoms of "doesn't work"? What happens (or not)? WHat did you exepct to happen? Do you get error messages and in such case which?

 

[roc100]

The code should return all values prior to the last space in a string. For example: "Street 123" returns "Street". When I use this expression in a query utilizing a table that is linked to the database, it returns "Street 123". When I use this expression in a query with a table that I have created within the database, it returns "Street" like it should. I hope this helps to clarify my situation. Thank you for your help.

 

[spikepl]

So probably your data suck. If you have "Street 123 " in that table then the function does its job.

 

[roc100]

The original data is "street 123". The function should return "street". Thanks again.

 

[spikepl]

If the function does what it is supposed to do with one table but not another, then the difference is data. It then means that the space between Street and 123 in your foreign table is not equal to " ".

As a test, retype the field in the foreing table, and now your function will work for that record.

You will need to find out what is hidden in that value. The function ASC will tell you the ascii value of that character. In your function you can then use Chr(theAciiValueYouFound) in stead of " " to locate the "space", if all the data come like this.

 

[roc100]

Thanks very much, this is very helpful. Can you please show me how to use "Chr(theAciiValueYouFound)" in my function?

 

[roc100]

Hi,
I am able to use the ASC function to get the ascii value of the first character in my string. How do I use it to get the value of a character that is in the middle of the string? For example; I am trying to find the ascii value of the space in the middle of this string "ABC 123". Thanks again,

Erica

 

[jdraw]

Please explain why you want the ascii value of a space?

If you want to see the value of individual bytes in your variable, you have to set it up so you look at individual bytes/chars.

If you suspect there is a leading or trailing space in your data, then do a Trim(yourVariableName)

If your value variable contains data and an embedded space, you can find the value before the space as follows"

Suppose you have a string sString
and it is sString = "Street 123"
and a variable Dim MyValue as string

If InStr(sString," ") > 0 Then
'there is a space within the data
'get the first part of the string
MyValue = Left(Trim(sString),Instr(sString," ") -1)
'MyValue has the data you're looking for
else
'no internal space exists
end if

ALSO: Here is an older function from Dev Ashish for looking through strings and selecting certain values.
You could have it print out the number of the character (from the left) in the variable and its value.

Function fExtractStr(ByVal strInString As String) As String
[COLOR="Green"]' From Dev Ashish
'(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?

'(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
'Lower or Upper case characters.

'************ Code Start ****[/COLOR]******

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        [COLOR="Green"]'The next statement will extract BOTH Lower and Upper case chars[/COLOR]
        If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
            (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
            [COLOR="Green"]'to extract just lower case, use the limit 97 - 122
            'to extract just upper case, use the limit 65 - 90[/COLOR]
            strOut = strOut & strTmp
        End If
    Next i
    fExtractStr = strOut
End Function

 

[DrallocD]

If you are looking for the last space, why not start from the end?

Does this work?

Function getLinkValue(s As String) As String ' takes string and returns substring before last space character in string
    Dim nPos As Long
    s = Trim(s)
    'No spaces so get out immediately
    If InStr(s, " ") = 0 Then
        getLinkValue = s
        Exit Function
    End If
    'Look for spaces from the end
    For nPos = Len(s) To 1 Step -1
        If Mid(s, nPos, 1) = " " Then
            Exit For
        End If
    Next nPos
    getLinkValue = Left(s, nPos - 1)
End Function

 

[roc100]

I can't thank you enough DrallocD! For some reason the code I was using would not work with the table that is linked to my database. The code you gave me worked! This has made my life so much easier. Many thanks to everyone for your help.