Brian-
I assume ParseWord is another function that returns the "nth" word in the
string. Your functions should be:
Public Function GetFirstName(strName As String) As String
GetFirstName = ParseWord(strName, 1)
End Function
Public Function GetLastName(strName As String) As String
GetLastName = ParseWord(strName, 2)
End Function
Then your query should look like:
UPDATE dbo.Contact
Set LastName = GetLastName([ContactName]),
FirstName = GetFirstName([ContactName]);
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Brian P Wood
Sent: Tuesday, November 22, 2011 5:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Update query using results from a function
Hello. Trying to do an Access Update Query that will update a table
with the results of a function. I am new to doing functions, so I am
not sure at which point I have a problem here.
This is the problem. The db software my company runs keeps Contact
Information on customers in only one field, it doesn't split them into
FirstName and LastName. It does have fields for FirstName and LastName
but they always remain empty, so for the purposes of a report I need
to do I am trying to split up the ContactName field.
I have written two functions.
Public Function FirstName()
ParseWord([dbo].[Contact]![ContactName], 1)
End Function
Public Function LastName()
ParseWord([dbo].[Contact]![ContactName], 2)
End Function
My thinking is that I need a First Name and a Last Name to call them
each individually.
In the query itself I call the function by putting [FirstName] or
[LastName] into the update to column. When I change to view mode I can
see that it does indeed split them apart, but when I run the query it
only seems to work for some records and ignores others.
Not sure what I am doing wrong.
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar