Brian-
AFAIK, ParseWord is not a builtin Access function. Did you dowload code from
somewhere?
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 6:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Update query using results from a function
John-
It's not a function, I used the information I found about it from
everythingaccess.com
It suggested it was more reliable than split which is what I thought I
would need to use initially
On Tue, Nov 22, 2011 at 12:27 PM, John Viescas <john@viescas.com> wrote:
> **
>
>
> 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]
>
>
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar