Omitting characters from a string

[TonyB]

Hi,



I have a table storing names in a field called "PrimPerson" in the format [Title] [Firstname] [Surname] (so all the results are return like "Mr Joe Bloggs"). However, some of the names don't have a title and so names just appear as "Joe Bloggs". What I want to do is run some VBA code that says "if the name starts with Mr or Ms or Mrs or Miss then run report X, else run report Y".

No functions I've tried seem to work. Any help is greatly appreciated!

Thanks

[orange]

Is this for some sort of academic exercise or demonstration? InN database general, you store atomic fields -fields that can not be decomposed into simpler fields. In your case [Title] [Firstname] [Surname] would be separate fields. You combine fields in queries for display purposes.

[TonyB]

Yes, but the data has been entered into a table from elsewhere, so I can't split it out. After trying all morning I've finally figured it out:

If InStr(1, [PrimPerson], "Mr") Or InStr(1, [PrimPerson], "Ms") Or InStr(1, [PrimPerson], "Miss") Or InStr(1, [PrimPerson], "Mrs") Or InStr(1, [PrimPerson], "Dr") Then
    DoCmd.OpenQuery "Query1"
Else
    DoCmd.OpenQuery "Query2"
End If

[Bob Fitz]

What if the first name Is "Missy"

[TonyB]

Yeah, I spotted that being a potential problem. I've worked around it now by adding a space to each string. Not perfect I know, but it's ok for what I'm doing

If InStr(1, [PrimPerson], "Mr ") Or InStr(1, [PrimPerson], "Ms ") Or InStr(1, [PrimPerson], "Miss ") Or InStr(1, [PrimPerson], "Mrs ") Or InStr(1, [PrimPerson], "Dr ") Then
    DoCmd.OpenQuery "Query1"
Else
    DoCmd.OpenQuery "Query2"
End If