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

No I just typed it in and it gave me some results so I assumed it was
working.

is there something different I should try?

On Tue, Nov 22, 2011 at 12:40 PM, John Viescas <john@viescas.com> wrote:

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