[AccessD] People Alias Names - Solved

Stuart McLachlan stuart at lexacorp.com.pg
Thu May 19 15:56:59 CDT 2011


I've been bitten by a CRLF at the start of a text field  a few times over the years as well.  
It's a b*tch, ain't it :-)

-- 
Stuart

On 19 May 2011 at 10:54, Jurgen Welz wrote:

> 
> When in table view, I tabbed into the FirstNameAlias field and
> replaced Rob with Rob and found that the query now included the last
> name as expected.  I just about broke my head on this one.
> 
> It turns out that the textbox where alias names are entered has the
> property for 'Enter Key Behavior' set to 'New Line in Field'.  In the
> case of the three names that did not display, the data had been
> entered in two rows.  Thus Rob had actually been entered as 'Rob' &
> vbcrlf & 'Rob'.  Apparently this forced the query to display the last
> name field in the 2nd row and the query defalut display height is one
> row only.  Changing the row height in the query view showed the last
> names in the 2nd row.  Trim$ is used for the not in list procedure by
> which names are entered in the tables but allowing the enter key to
> add new rows in the alias sub form messed things up when users entered
> text both before and after an enter key.
> 
> Needless to say I changed the Enter Key Behavior of the  Alias Name
> text box and checked the rest of the data.  All I needed to do is
> change the row height in the alias table view in order to see the
> problem records and I'm satisfied that everything now works as it
> should.
> 
> Ciao 
> Jürgen Welz 
> Edmonton, Alberta 
> jwelz at hotmail.com
> 
> 
> 
> > From: jwelz at hotmail.com
> > To: accessd at databaseadvisors.com
> > Date: Wed, 18 May 2011 14:13:26 -0600
> > Subject: Re: [AccessD] People Alias Names
> > 
> > 
> > Charlotte: Yes there are last names for the records. Reversing the
> > concatenations, the last name shows up correctly. Also, adding the
> > last name field to the query as an additional column displays the
> > last name. There are records in the main table associated with the
> > alias table names since they show up as records on an equal join.
> > The problem arises somewhere from the fact that one concatenated
> > query field concatenates data from fields in different tables. The
> > oddest thing, that also answers your question, is that if I display
> > the last name first in the concatenation, all the names in the
> > search combo display in their entirety. That is, if I select
> > tbl1.LastName & ' ' & tbl2.Alias as one field and tbl2.Alias & ' ' &
> > tbl1LastName as another field in the same query, the output is
> > missing the last name in 3 of the concatenated records where the
> > first name appears first, but it shows in all of the records where
> > the last name appears first. Simply reversing the order of the
> > concatenated fields changes the displayed output. What is also odd
> > is that the space concatenated between the names also fails to
> > appear. That is to say, the name Rick, where it appears, has a
> > length of 4 and Rob as 3. There is an example of Rick where the
> > contatenatation succeeds but there is no other example of Mitch (or
> > the full proper name).
> > 
> > SELECT tblEmployee.EmployeeID, tblEmployeeAlias.FirstNameAlias & ' '
> > & tblEmployee.LastName AS Employee, [LastName] & ' ' &
> > [tblEmployeeAlias].[FirstNameAlias] AS Expr1 FROM tblEmployeeAlias
> > INNER JOIN tblEmployee ON tblEmployeeAlias.EmployeeID =
> > tblEmployee.EmployeeID
> > 
> > yeilds
> > 
> > 12 Brad Card Card Brad
> > 432 Jim Jaybo Jaybo Jim
> > 2344 Rick Mackie Rick
> > 3444 Rob van der Hout Rob
> > 4455 Ron Kay Kay Ron
> > 5445 Mitch Blist Mitch
> > 5987 Bob Kin Kin Bob
> > 
> > I've also reversed the Inner Join order and sides of the comparison
> > operator in the join to no avail. There is no numerical ID or first
> > or last name alphabetical sequence in the success/failure to display
> > the last name side of the concatenation in the 2nd column.
> > 
> > What I get is 31 records returned. 28 records have the full first
> > and last names in the first column but 3 with first name only. In
> > the 3rd column all 31 records have both a last name and first name.
> > These 31 records are unioned with the records from the tblEmployee
> > to fill the employee search combo. I have various options to display
> > regional, active, inactive and other types of employees and to show
> > them by last name or by first name. Most people prefer the first
> > name first approach and that is the one that doesn't consistently
> > work. When I display last name first, I also concatenate a comma
> > with the space to make it obvious and I change the combo back color
> > to indicate that the not in list event will not fire in last name
> > first mode.
> > 
> > Gustav: Distinct did not work.
> > 
> > I can fix this by adding the last name field to the Alias table and
> > storing it for each alias entered. This denormalization would
> > simplify the union query that fills the look up combo but would
> > require me to maintain the last name in the alias table with edits
> > to the main table last name. I lock the textbox displaying the names
> > and require a click on an edit button for each field to be edited so
> > I can control this, though I'd rather not have to code a fix where
> > none should be required.
> > 
> > I plan to extend the alias concept to other single field 'name'
> > tables to accomodate things like company names (PCL = P.C.L. = P C L
> > = P. C. L.) and equipment component names (Side Bracket = Outrigger
> > = Out rigger = Hanger) but am perplexed by the odd inconsistent
> > behavior of the query and am leery of future complications.
> > 
> > It's as though I evaluated the following lines in the immediate
> > window and all evaluated to true except the 3rd:
> > 
> > ?1=1
> > ?2=2
> > ?3=3
> > ?4=4
> > 
> > One thing I hadn't mentioned is that I am using an MSAccess ODBC
> > connection to tables in SQLServer 2005.
> > 
> > At this point, I'm tempted to run a make table query to bring the
> > data back to native access and populate all the data. I'll also try
> > adding some additional aliases to the misbehaving records in situ to
> > determine whether they continue to fail.
> > 
> > Ciao 
> > Jürgen Welz 
> > Edmonton, Alberta 
> > jwelz at hotmail.com
> > 
> > 
> > 
> > > Date: Wed, 18 May 2011 09:16:18 -0700
> > > From: charlotte.foust at gmail.com
> > > To: accessd at databaseadvisors.com
> > > Subject: Re: [AccessD] People Alias Names
> > > 
> > > Do you actually have any names associated with those aliases? If
> > > not, that could be the problem.
> > > 
> > > Charlotte Foust
> > > 
> > > On Wed, May 18, 2011 at 12:39 AM, Gustav Brock <Gustav at cactus.dk>
> > > wrote:
> > > > Hi Jürgen
> > > >
> > > > Weird.
> > > > Try adding DISTINCT:
> > > >
> > > > SELECT DISTINCT tblEmployee.EmployeeID, ..
> > > >
> > > > /gustav
> > > >
> > > >
> > > >>>> jwelz at hotmail.com 17-05-2011 21:56 >>>
> > > >
> > > > I've devised a method to allow users to select a person's record
> > > > by an alias first name in a lookup combo and there appear to be
> > > > some issues. For example, I have a fellow known as Willie whose
> > > > payroll name is Guillermo and I need my users to be able to look
> > > > up the user in a search combo. Choosing either Willie xxx or
> > > > Guillermo xxx navigates the form to display the Guillermo
> > > > record. Aliases include common misspellings such as Jurgen and
> > > > Juergen for Jürgen. Alias names appear in a continuous sub form
> > > > on the main form record.
> > > >
> > > > Assume a master record in tblEmployee with an autonumber primary
> > > > key, EmployeeID and text FirstName and LastName fields.
> > > >
> > > > Assume an alias table that contains a foreign key pointing to
> > > > the Employee record and a text FirstNameAlias field.
> > > >
> > > > The alias names are unioned to the employee names in the lookup
> > > > combo. The combo displays full names being the FirstName & ' ' &
> > > > LastName concatenation and there are options to load LastName &
> > > > ' ' & FirstName and to include only active, only inactive or
> > > > both records as well as regional filters.
> > > >
> > > > The query that I wish to union appears:
> > > >
> > > > SELECT tblEmployee.EmployeeID, tblEmployeeAlias.AliasFirstName &
> > > > ' ' & tblEmployee.LastName AS Employee FROM tblEmployeeAlias
> > > > INNER JOIN tblEmployee ON tblEmployeeAlias.EmployeeID =
> > > > tblEmployee.EmployeeID
> > > >
> > > > I have 31 alias names in the table. 28 of them work. 3 don't in
> > > > that the concatenation of the LastName doesn't happen.
> > > >
> > > > If I add a column to the select statement: [LastName] & ' ' &
> > > > [tblEmployeeAlias].[FirstName] AS Expr1, the LastNames appear.
> > > > If the select statement includes a column for LastName alone, it
> > > > appears in the query so there is no problem with missing or null
> > > > data and the join functions. We know it functions as the 3 alias
> > > > names appear and a join failure would exclude those records.
> > > >
> > > > AliasFirstNames that don't show a concatenated last name are
> > > > Rob, Rick and Mitch. The last name will not appear in the
> > > > concatenation of the Alias table first name with primary record
> > > > last name in the 3 records. I don't commonly do display
> > > > concatenations of fields from different tables but am baffled
> > > > why some work and some don't. There are no names with
> > > > apostrophes. Common names like Mke and Ron work as do unusual
> > > > name like mine which is commonly misspelled. It seems to make no
> > > > difference if the first or last name is unique. If I reverse the
> > > > order of the names in the concatenation, the Last Names show.
> > > >
> > > > Ciao
> > > > Jürgen Welz
> > > > Edmonton, Alberta
> > > > jwelz at hotmail.com 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 







More information about the AccessD mailing list