Upper Case Wildcard?

[moorsey]

Just been searching around, found a few posts of uppercase questions, none that quite fit with mine, anyway,

Basically, the data I am working on produces names as such "JoeBloggs" "FredPerry"

Anyway, Im going to use a query to seperate these out into first name and last name, but, I can't seem to find a way to specify a capital letter as a wild card. So far:

CapPos: InStr(2,[calllogs]![CalledNumber],"A")

Then I am going to use the mid function to seperate the two, depending on the position of the first capital letter.

But I just need to replace "A" with something that will find any capital letter.

Cheers

 

[TanisAgain]

Ther is something on this site that may help. Try here.

http://www.access-programmers.co.uk/forums/showthread.php?t=94206&highlight="find+capital+letter"

 

[Sergeant]

Ther is something on this site that may help. Try here.

http://www.access-programmers.co.uk/forums/showthread.php?t=94206&highlight="find+capital+letter"

Here is what I came up with:

Public Function InsertSpace(txt As String) As String

    For i = 2 To Len(txt)
        If Asc(Mid(txt, i, 1)) < 91 And Asc(Mid(txt, i, 1)) > 64 Then
            InsertSpace = Left(txt, i - 1) & " " & Mid(txt, i)
            Exit For
        End If
    Next i
    
End Function

I'm going to check out the link you posted to compare methods.

 

[moorsey]

Great, just what im after, so to define a "capital letter" is Asci values 64-91, trying to keep just with queries, how can I define those Asci values in my InStr statement

thanks for the help guys,

 

[Sergeant]

In the database window, go to 'Modules' and select 'New'. VBE opens...paste a function in the white space and click save...call it Module1.

Here is the SQL for a query that uses the function I presented in my last post:

SELECT InsertSpace([pname]) AS SplitName
FROM ppl;

Presto...you are calling the function for each row returned.

 

[moorsey]

cant get that to work at all, what does pname refer to? whatever I put in that box, it just comes up with a message box asking for input for "pname".....

So I have to link to a module? There is no way of doing it pure query using functions e.t.c.

Thanks again,

 

[Sergeant]

Use your table's field name in place of pname.

 

[moorsey]

thanks, got that working, only trouble is, if it comes accross a blank field, it puts an error as the out put, messing up the data, I looked at the module to see if I could change it to skip trying to put a space in if there is nothing in the field, but couldn't quite figure it out

 

[Sergeant]

Right, I wrote that function quickly over my morning cuppa. It has no error control at all.

This mod to the function will ignore blank entries...

Public Function InsertSpace(txt As String) As String
  [COLOR="Red"]If txt & "" <> "" Then[/COLOR]
    For i = 2 To Len(txt)
        If Asc(Mid(txt, i, 1)) < 91 And Asc(Mid(txt, i, 1)) > 64 Then
            InsertSpace = Left(txt, i - 1) & " " & Mid(txt, i)
            Exit For
        End If
    Next i
  [COLOR="red"]Else
    InsertSpace = ""
  End If[/COLOR]    
End Function

 

[moorsey]

hmm, that gives the same results, tried playing around with the code you posted but can't seem to make it work, still gives errors?

Thanks for this, im getting through this DB bit by bit!

 

[moorsey]

strange, it works now, must not have saved properly last time I made changes, thanks for all your help!