Brian-
ParseWord is a custom function from the Allen Browne website. If it's not in your database, your query cannot run!
John
Sent from Samsung Mobile
-------- Original message --------
Subject: Re: [MS_AccessPros] Update query using results from a function
From: Brian P Wood <wonderboymi@gmail.com>
To: MS_Access_Professionals@yahoogroups.com
CC:
Hi John, I'm using Access 2010. When I click Shift+F2 I get a message that
says Identifier under cursor not identified
On Wed, Nov 23, 2011 at 1:30 AM, John Viescas <john@viescas.com> wrote:
> **
>
>
> Brian-
>
> Which version of Access are you using? One way to find code is to go to a
> place
> where you call the code in a VBA module, highlight the procedure name, and
> Press
> Shift+F2. That should take you to where the name is defined.
>
>
> 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:45 PM
>
> To: MS_Access_Professionals@yahoogroups.com
> Subject: 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
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar