Query to parse out specific characters in an address if they meet a certain criteria

[VGG]

I have a field called [Situs] which contains addresses, that look like this:

2482 S MAIN ST NW
54 GREEN TREE LN
978 B MARQUART CIR
978 C MARQUART CIR

I am able to use this module to separate the numbers before the first space into their own field, and everything after the first space into its own field.

I NEED TO MODIFY THIS CODE OR GET A QUERY TO PULL OUT JUST THE FIRST STAND ALONE ALPHA CHARACTER IF IT IS SPECIFICLY AN " A ", " B ", " C " or " D ". I DONT WANT TO NARROW THE FINAL TABLE TO JUST THOSE RECORDS, I JUST NEED TO HARVEST THOSE OUT AND PLACE THEM IN TO A FIELD IF THEY MEET THE CRITERIA. PLEASE HELP!!!

The code I am currently using looks like this:

Option Compare Database
Global GClinet_ID As Integer
Function ParseFirstComp(pValue) As String
   Dim LPosition As Integer
   
   'Find postion of space
   LPosition = InStr(pValue, " ")
   
   'Return the portion of the string before the space
   If LPosition > 0 Then
      ParseFirstComp = Left(pValue, LPosition - 1)
   Else
      ParseFirstComp = ""
   End If
   
End Function

Function ParseSecondComp(pValue) As String
   Dim LPosition As Integer
   
   'Find postion of space
   LPosition = InStr(pValue, " ")
   
   'Return the portion of the string after the space
   If LPosition > 0 Then
      ParseSecondComp = Left(pValue, LPosition + 1)
            
   Else
      ParseSecondComp = ""
   End If
   
End Function

MY CURRENT WHEN USING THIS IN MY QUERY ARE:

ParseFirstComp([Situs]) - ReturnS everything before the first space, ie: 4587

ParseSecondComp([Situs]) - Returns EVERYTHING after the first space, ie: B MARQUART CIR, or S MAIN ST SW

I NEED JUST THE ALPHA IF IT IS SPECIFICLY "A", "B", "C" or "D"



Know what I mean?

Please assist, I'm getting very desperate here!

Thank you very much!

[RuralGuy]

Have you looked at the Split() function?

[Dunro]

You might want to modify the query, and have another field based on a switch...

I'm sure someone could rewrite this better. (I prefer switch() to iif() as it seems to run faster for me, but your mileage may vary...)

eg.

NewField: switch(instr([Situs]," A ") <> 0,"A",instr([Situs]," B ") <> 0,"B",instr([Situs]," C ") <> 0,"C",instr([Situs]," D ") <> 0,"D",1=1,"")

or in SQL: switch(instr([Situs]," A ") <> 0,"A",instr([Situs]," B ") <> 0,"B",instr([Situs]," C ") <> 0,"C",instr([Situs]," D ") <> 0,"D",1=1,"") as NewField