Parsing names in a field

[Scoop]

I am using slightly modified versions of the "NameTitle" and
"NameFirst" functions posted in the following thread:

http://groups.google.com/group/microsoft.public.access/browse_thread/thread/6b092d07d7fa528e

I've got them working well (many thanks to Terry Wickenden and those
who have re-posted it) with one exception. The database that I am
trying to clean up has some records where the field that I am parsing
is NULL. It generates a datatype mismatch.

Can someone tell me how to modify either/both of these functions to
check if the field is NULL and skip if it is.

Thanks,

Scott

 

[Brendan Reynolds]

If you're using the functions in an update query, the easiest solution is to
add "WHERE FieldName IS NOT NULL" to the query. Alternatively, you can use
the NZ function. Instead of NameFirst(YourFieldName) use
NameFirst(NZ(YourFieldName, "")). Alternatively again, modify the functions
like so ...

Public Function NameFirst(varPass As Variant) As Variant

If IsNull(varPass) Then
NameFirst = Null
Exit Function
End If
'original function code here

 

[Scoop]

Thanks, Brendan. I'll give it a shot.

One thing to mention...I am using a SELECT query to "stage" the data
from a table in an old database. In this query I am changing field
names/data types to be consistent with a table in a new database.
After staging the data, I have an APPEND query that dumps the data into
the new table.

So, I don't think I can use "WHERE FieldName IS NOT NULL" b/c I need
all the records. Everything actually works, it just bugs me that that
when I use the following to extract the "Title" for a name:

Title: NameTitle([MasterList]![First Name])

....the query shows #Error where the original fields are NULL.

 

[Brendan Reynolds]

You could use ...

Title: NameTitle(NZ([MasterList]![First Name],"")

.... which would display nothing for the Title in the SELECT query, however,
in the APPEND query, you'll get an empty string in the target field instead
of a Null value, which may not be what you want. An alternative would be ...

Title: IIf([MasterList]![First Name] IS NULL, Null,
NameTitle([MasterList]![First Name]))

 

[Scoop]

Brendan,

Sorry for being so late getting back...The iif function did the trick!

Thanks for the help.