Re: How to split a field into parts
From: Brendan Reynolds (anonymous)
Date: 03/08/05
- Next message: Italian Pete: "New line in text box or label"
- Previous message: Graham R Seach: "Re: TextBox"
- In reply to: doyle60_at_aol.com: "Re: How to split a field into parts"
- Next in thread: John Nurick: "Re: How to split a field into parts"
- Reply: John Nurick: "Re: How to split a field into parts"
- Reply: doyle60_at_aol.com: "Re: How to split a field into parts"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Mar 2005 13:05:04 -0000
InStrRev *is* in Access 2000 - it was one of the new string functions that
were added to VBA 6, and Access 2000 was the first version to use VBA 6.
Some of those new functions could not be used directly in a query, though,
and I believe InStrRev was one of those. You'd have to write a custom VBA
function to call InStrRev, and then call the custom function from your
query.
In Access 97, you could do something like this ...
Public Function GetLastChar(ByVal TheString As String, ByVal TheChar As
String) As Long
Dim lngLoop As Long
Dim lngPos As Long
For lngLoop = Len(TheString) To 1 Step -1
If Mid$(TheString, lngLoop, 1) = TheChar Then
lngPos = lngLoop
Exit For
End If
Next lngLoop
GetLastChar = lngPos
End Function
? getlastchar("this is some text", " ")
13
-- Brendan Reynolds (MVP) <doyle60@aol.com> wrote in message news:1110280264.316196.47990@g14g2000cwa.googlegroups.com... > Thanks. I do have Access 2000 at home and the InStrRev is certainly > not a function in it. Your HowManyChars works but I'm afraid I still > need the position of the last Space in the string to split the name in > half properly. > > Here are some sample names: > > George Mitchell > Hank Natty Barnes > Nelly Margeret Stevens > Telsea "Minnie" Mack-Evens > Kent George Matty Gilbert Robertson > > I need to split them like this (I'll hand fix compound sir names): > > George Mitchell > Hank Natty Barnes > Nelly Margeret Stevens > Telsea "Minnie" Mack-Evens > Kent George Matty Gilbert Robertson > > I don't see how to do that without knowing the position of the last > space. > > Basically I need a function that will help me put the last word in one > field and all preceeding words in another. > > Thanks, > > Matt > > > > > > Brendan Reynolds wrote: >> InStrRev was new in Access 2000. The best way to count the number of > spaces >> (or any other character) in a string uses Replace, but Replace was > also new >> in Access 2000. Here's an alternative, won't be as fast, but will > work in >> Access 97 ... >> >> Public Function HowManyChars(ByVal TheString As String, ByVal TheChar > As >> String) As Long >> >> Dim lngLoop As Long >> Dim lngCount As Long >> >> For lngLoop = 1 To Len(TheString) >> If Mid$(TheString, lngLoop, 1) = TheChar Then >> lngCount = lngCount + 1 >> End If >> Next lngLoop >> >> HowManyChars = lngCount >> >> End Function >> >> -- >> Brendan Reynolds (MVP) >> >> <doyle60@aol.com> wrote in message >> news:1110228530.226532.143100@f14g2000cwb.googlegroups.com... >> > Thanks. But InStrRev is not a defined function in my Access 1997. > I'm >> > trying to use it in a query, if that matters. >> > >> > I still need a function that tells me how many spaces are in a > string? >> > But if I get the InStrRev to work, I could just say: if InStr is > equal >> > to InStrRev, then this, and if not, that. Now I'm thinking. > Thanks, >> > >> > Matt >> > >
- Next message: Italian Pete: "New line in text box or label"
- Previous message: Graham R Seach: "Re: TextBox"
- In reply to: doyle60_at_aol.com: "Re: How to split a field into parts"
- Next in thread: John Nurick: "Re: How to split a field into parts"
- Reply: John Nurick: "Re: How to split a field into parts"
- Reply: doyle60_at_aol.com: "Re: How to split a field into parts"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
