Field value that contains Alpha Characters

[amerifax]

I am looking for a phrase based on if a field (NAME) has 4 characters or more. If the field starts with 4 characters or more. I want to take everything up to the first space or the end of the field, whichever comes first, and put that into another field called (SECNAME).

Bob

 

[Galaxiom]

It could also be done as a nested IIF but would be quite clumsy because the InStr would need to be repeated three times. I would use a function. If you want to test for the four characters being alpha you would definitely want to use a function.

Here is a function without the alpha test.

If you need the alpha test then use the Mid() function in a loop to step through the first four characters and test their ASCII value for being in the range of 65 to 90 or 97 to 122.

Use the Asc() function to get their character code.

Public Function ParseName(WholeName As String) As String
 
Dim SpaceLocation as Integer
 
   SpaceLocation = Instr(WholeName," ")
   If SpaceLocation = 0 Then
      ParseName = WholeName
   ElseIf SpaceLocation > 4 Then
      ParseName = Left(WholeName, SpaceLocation)
   End If
 
End Function

BTW
Avoid NAME as a field name as it is a reserved word.

 

[amerifax]

Green as I am! How does a function differ from a query?

 

[Galaxiom]

A function is a code block that can be called from other places. It is stored in a Standard Module as a Public function if you want to use it in queries.

It works exactly like any other function in VBA.

In your query designer, the Update To cell of the Field SECNAME would be:
ParseName(fieldname)

This would pass the value in the field called "fieldname" to the function and return the ParseName value from the function.

 

[amerifax]

>>Galaxiom - It works exactly like any other function in VBA<<

Thanks. A good piece of info. Probably as common to use as the day after Saturday would be Sunday but new to me.

Bob

 

[amerifax]

>>Galaxiom<<

Been a few years since you reached out to help. I save my help topics as you can see. Wanted to thank you for the past help. With out the help from people like yourself and the use of a good forum I mostly likely would be on page 10 of the manual.

Thanks again.

Bob