Solved Left or Mid?

[prasadgov]

Hi,

I have a field in Access table with values such as
1. abc
2. efg
.......
10. ijk


It has two spaces between the numbers and the string, which is uniform. I want to eliminate the numbering and just retain characters. How to identify the space between the single digit numbers and the double digit, in order to run a update query?
Or should i use InStr() function to identify the position of the first occurrence of a string?

 

[theDBguy]

Yes, I would use a combination of both. For example:

Trim(Mid([FieldName],InStr([FieldName], " ")))

 

[prasadgov]

It did this

Yes, I would use a combination of both. For example:

Trim(Mid([FieldName],InStr([FieldName], " ")))

It eliminated numbering and spaces only for the double digit values (after 10 but not before)

QuestionText	Expr1
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
10. Maintaining appropriate control over the proceeding	Maintaining appropriate control over the proceeding
10. Maintaining appropriate control over the proceeding	Maintaining appropriate control over the proceeding

 

[theDBguy]

It did this

It eliminated numbering and spaces only for the double digit values (after 10 but not before)

QuestionText	Expr1
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
9. Moving the proceeding in an appropriately expeditious manner	9. Moving the proceeding in an appropriately expeditious manner
10. Maintaining appropriate control over the proceeding	Maintaining appropriate control over the proceeding
10. Maintaining appropriate control over the proceeding	Maintaining appropriate control over the proceeding

That probably means there is a space before the single digit numbers. If so, try it this way.

Trim(Mid([FieldName],InStr([FieldName], ". " + 2)))

 

[prasadgov]

Resolved it
Mid([QuestionText],5) did the trick

 

[Minty]

That will only work if your numbers don't rise above 99.

If there are always two spaces after the period the safer way is to search for that using Instr()

 

[prasadgov]

That will only work if your numbers don't rise above 99.

If there are always two spaces after the period the safer way is to search for that using Instr()

Yes and my values are 1-31

 

[moke123]

You could also use a double space in your Inst() criteria

Trim(Mid(yourstring, InStr(1, yourstring, "  ") + 1))

one way to check what your dealing with when working with strings is to check ascii values
This will expose non printable characters

Sub CheckString(strIN As String)

    Dim i As Integer
    For i = 1 To Len(strIN)
        Debug.Print Asc(Mid(strIN, i, 1)); Mid(strIN, i, 1)
    Next

End Sub

Output:

 32
 57 9
 46 .
 32
 32
 77 M
 111 o
 118 v
 105 i
 110 n
 103 g
 32
 116 t
 104 h
 101 e
 32

 

[prasadgov]

You could also use a double space in your Inst() criteria

Trim(Mid(yourstring, InStr(1, yourstring, "  ") + 1))

This worked neatly.