[X]My Assistant

[Bill T]

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

[theDBguy]

Hi Bill,

It might help understand the problem better if you could also post some sample data. Cheers!

[Bill T]

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

[River59]

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.

[Bill T]

Many thanks River59

That works really well.

Bill

[RJD]

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

[GroverParkGeorge]

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.

[Bill T]

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

[GroverParkGeorge]

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."

[tina t]

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.

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

[Bill T]

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

[tina t]

well, if you're using a lookup table as Joe described:

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

[Bill T]

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