UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can It Be Done?, Access 2003    
 
   
Bill T
post Dec 7 2017, 11:59 AM
Post#1



Posts: 75
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
Go to the top of the page
 
theDBguy
post Dec 7 2017, 12:01 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,202
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
Go to the top of the page
 
Bill T
post Dec 7 2017, 12:15 PM
Post#3



Posts: 75
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
Go to the top of the page
 
River59
post Dec 7 2017, 12:20 PM
Post#4



Posts: 1,344
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 ...
Go to the top of the page
 
Bill T
post Dec 7 2017, 12:36 PM
Post#5



Posts: 75
Joined: 12-June 10



Many thanks River59

That works really well.

Bill

--------------------
Bill Tweedy
Go to the top of the page
 
RJD
post Dec 7 2017, 12:36 PM
Post#6


UtterAccess VIP
Posts: 7,816
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)
Go to the top of the page
 
GroverParkGeorge
post Dec 7 2017, 01:23 PM
Post#7


UA Admin
Posts: 31,195
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.

--------------------
Go to the top of the page
 
Bill T
post Dec 7 2017, 01:39 PM
Post#8



Posts: 75
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
Go to the top of the page
 
GroverParkGeorge
post Dec 7 2017, 02:00 PM
Post#9


UA Admin
Posts: 31,195
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."

--------------------
Go to the top of the page
 
tina t
post Dec 7 2017, 02:27 PM
Post#10



Posts: 5,195
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

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Bill T
post Dec 7 2017, 04:01 PM
Post#11



Posts: 75
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
Go to the top of the page
 
tina t
post Dec 7 2017, 04:13 PM
Post#12



Posts: 5,195
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

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Bill T
post Dec 7 2017, 06:25 PM
Post#13



Posts: 75
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
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 07:59 PM