Brian-
It's not random at all! It's "skipping" entries like:
Dr Cipriani/Robin
Sr.Carmelina
V B Benham
Dr. R Berg
Hilda Wolfe/Peter M
Your ParseWord function is clearly "confused" when it "sees" more than one
"word" or the "words" are separated by something other than a blank.
What's the code in your ParseWord function?
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/> http://www.viescas.com/
(Paris, France)
P.S. In future, upload sample files like this to the Files / 2_AssistanceNeeded
folder on the group website and post messages only to the group.
From: Brian P Wood [mailto:wonderboymi@gmail.com]
Sent: Tuesday, November 22, 2011 6:12 PM
To: john@viescas.com
Subject: Re: [MS_AccessPros] Update query using results from a function
hi John
For some reason it doesn't want to work, it seems to skip some ContactNames
randomly, I'm attaching a file showing the results I get.
On Tue, Nov 22, 2011 at 11:33 AM, John Viescas <john@viescas.com> wrote:
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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Brian P Wood
Sent: Tuesday, November 22, 2011 5:22 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar