Parsing names in a field

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

http://groups.google.com/group/micro...092d07d7fa528e
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6781-parsing-names-in-a-field.html

I've got them working well (many thanks to Terry Wickenden and those
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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

--
Brendan Reynolds
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781

"Scoop" wrote in message
news:1131217656.982848.229550@f14g2000cwb.googlegr oups.com...
>I am using slightly modified versions of the "NameTitle" and
> "NameFirst" functions posted in the following thread:
>
> http://groups.google.com/group/micro...092d07d7fa528e
>
> 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
>

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])
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781

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

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 ...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781

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

--
Brendan Reynolds


"Scoop" wrote in message
news:1131224355.347483.204020@g49g2000cwa.googlegr oups.com...
> Thanks, Brendan. I'll give it a shot.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6781
>
> 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.
>