ACCESS FEED: Re: [MS_AccessPros] Update query using results from a function

Selasa, 22 November 2011

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]
>
>
>