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
Tidak ada komentar:
Posting Komentar