My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 79 Joined: 12-June 10 ![]() | I am trying to populate one field with a word based on the the first character in another field using a lookup table. I can get the first character by using =Left([CompanyName], 1). i.e. if the character trimmed is an "F" then I need to populate the other field with the value "Family". The lookup table has 8 rows so would I need to use the Case function? The trimmed letter is not always the first letter of the name to be stored by the way. Thanks Bill -------------------- Bill Tweedy |
![]() Post#2 | |
![]() Access Wiki and Forums Moderator Posts: 71,915 Joined: 19-June 07 From: SunnySandyEggo ![]() | Hi Bill, It might help understand the problem better if you could also post some sample data. Cheers! -------------------- Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas Microsoft Access MVP | Access Website | Access Blog | Email |
![]() Post#3 | |
Posts: 79 Joined: 12-June 10 ![]() | Does this help? If one field has data such as "A2312" then I need to populate the other field with the word "Ancestry" or if the data is "E3212" then I need to populate the other field with the word "Genealogy" and so on. Thanks Bill -------------------- Bill Tweedy |
![]() Post#4 | |
Posts: 1,356 Joined: 7-April 10 From: Detroit, MI ![]() | On the After Update event for the field that contains the input (your show CompanyName), I would use a Case Select to populate the other field. CODE Private Sub CompanyName_AfterUpdate() Select Case Left([CompanyName], 1) Case "A2312" Me.MyControlName = "Ancestry" Case "E3212" Me.MyControlName = "Geneology" End Select End Sub Set as many cases as you need and replace with your control names. No lookup table needed. -------------------- Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ... |
![]() Post#5 | |
Posts: 79 Joined: 12-June 10 ![]() | Many thanks River59 That works really well. Bill -------------------- Bill Tweedy |
![]() Post#6 | |
![]() UtterAccess VIP Posts: 8,222 Joined: 25-October 10 From: Gulf South USA ![]() | Hi: PMFJI, but you could also use a table approach, with the table housing the first letter and the resulting description. Then, in the query, link from the first letter of the companyname to the table first letter field (you can do this by stacked queries or by a special join, not available in Design View, but in SQL). This way you will never have to store the result (this could produce an error if the company name was entered wrong and then re-entered), and maintenance is much easier if you ever have to add a name (no code corrections). Just a thought ... HTH Joe -------------------- "Each problem that I solved became a rule, which served afterwards to solve other problems." "You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing." Rene Descartes 1596-1650 (Mathematician and Philosopher) |
![]() Post#7 | |
![]() UA Admin Posts: 32,357 Joined: 20-June 02 From: Newcastle, WA ![]() | Joe is on the right track here. What you are doing is the equivalent of a calculated field, which is almost never a good plan. Use a lookup table as he suggests. -------------------- My Name Is George and I believe in Karma How to Ask a Good Question Beginning SQL Server Visit My Blog on Facebook |
![]() Post#8 | |
Posts: 79 Joined: 12-June 10 ![]() | Thanks for the suggestion. At present I have it working well and the idea mentioned is a bit beyond my skills with SQL involved. Bill -------------------- Bill Tweedy |
![]() Post#9 | |
![]() UA Admin Posts: 32,357 Joined: 20-June 02 From: Newcastle, WA ![]() | It'll work until you add new options. At that point, you'll have to modify the code in the VBA. If you use a table-driven solution, you'll only have to add a record to a table.... It's like the old tv commercial, "pay me now or pay me later." -------------------- My Name Is George and I believe in Karma How to Ask a Good Question Beginning SQL Server Visit My Blog on Facebook |
![]() Post#10 | |
Posts: 5,338 Joined: 11-November 10 From: SoCal, USA ![]() | i'm with George and Joe - a table solution is by far the easiest to maintain. i do understand your hesitancy about implementing a SQL solution; SQL is probably my weakest skill in Access. but the two options that Joe suggested are actually easy enough to create. QUOTE Then, in the query, link from the first letter of the companyname to the table first letter field (you can do this by stacked queries or by a special join, not available in Design View, but in SQL) the "stacked queries" approach just means creating a query with a calculated field that isolates the first letter of the field value, as FirstLetter: Left(CompanyName,1) then use that query in a second query, including the lookup table, and linking on the calculated field FirstLetter and the letter field in the lookup table. you can do both queries in the query Design grid, without having to edit the SQL statement manually. the "special join" approach is actually cooler, and pretty easy to do once you know what to look for. you can build the query in the Design grid, using the data table and the lookup table, and linking on the CompanyName field and the letter field in the lookup table. the query won't work. but then you open the SQL pane and replace the CompanyName field in the JOIN clause, with the Left(CompanyName,1) expression. if you post the SQL statement, we can help you make the replacement. i urge you to give the table-based setup a try; it's a good solution for your database, and good tricks to add to your skill set - very little pain involved! ;) hth tina -------------------- "the wheel never stops turning" |
![]() Post#11 | |
Posts: 79 Joined: 12-June 10 ![]() | Well I've had a play and created the First Letter query then I created another query with the look up table data. This second query produces two columns, the first with the single letter and the second with the company full name. [SQL below]. Having made this query how do I implement it on my form. CODE SELECT qryFirstLetter.FirstLetter, tblCompanyName.FullNameCompany FROM qryFirstLetter INNER JOIN tblCompanyName ON qryFirstLetter.FirstLetter=tblCompanyName.First; Thanks Bill -------------------- Bill Tweedy |
![]() Post#12 | |
Posts: 5,338 Joined: 11-November 10 From: SoCal, USA ![]() | well, if you're using a lookup table as Joe described: QUOTE Hi: PMFJI, but you could also use a table approach, with the table housing the first letter and the resulting description. then you wouldn't have a field in your main table for the description. so you wouldn't want to populate a bound control on a form with the description. but you can display the description in an unbound textbox control on a form, by using the DLookup() function, something like =DLookup("MyDescriptionFieldname", "MyLookupTableName", "MyLetterFieldname = '" & Left(CompanyName,1) & "'" the above expression would go in the ControlSource property of the unbound textbox control. the queries, that we discussed in the previous few posts, would be used for Recordsource in a report, or in a "view-only" form - they're not intended to be used in a form where records are added/edited/deleted. hth tina -------------------- "the wheel never stops turning" |
![]() Post#13 | |
Posts: 79 Joined: 12-June 10 ![]() | QUOTE the queries, that we discussed in the previous few posts, would be used for Recordsource in a report, or in a "view-only" form - they're not intended to be used in a form where records are added/edited/deleted. Oh that makes a difference as the purpose of my post was to use it for adding records to a form. Thanks Bill -------------------- Bill Tweedy |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 19th April 2018 - 07:55 PM |