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 >