Matching 3 sequential letters in a string to the first 3 letters of a field entry

[Sam Summers]

Hi Guys,

I don't know where to start with this one?
So I am hoping there is some clever person who can help?

A user selects a string - e.g. HE-168-DIA-DR-003 from a combobox.

This number is generated from a combination of things depending on original input.

The "DIA" portion is the first 3 letters of a name that is stored in a table.

What I want to do is to set another combobox (ClientName) with the full name as in the table (ReportTbl) that the "DIA" letters belongs to?

Racking my brains on how to achieve this or at least a workaround?

Many thanks in advance! Plus i'll be impressed!

 

[Isskint]

Hi Sam

Set the rowsource for the combobox to something like;

SELECT [ReportTbl].[Field1], [ReportTbl].[Field2] etc...
FROM ReportTbl
WHERE (((Left([ReportTbl].[MatchingField],3))=Mid([Forms]![FormName]![SelectCombo],8,3)));

Forms!FormName!SelectCombo is the form and combobox that the user selects the 3 character string from.
[ReportTbl].[MatchingField] is the field that contains the 3 character code

 

[Sam Summers]

Hi, Thank you very much for replying.
I just wrote a long reply but it got lost when this site asked me to log in again!!!!! So I will try again to explain a bit clearer what I am trying to achieve.

I tried a few things but I think it will help if I explain better?

A textbox on the form "Main" displays the number - HE-168-DIA-DR-003 which is automatically generated.

From this I want the combobox "ClientName" to display the full clients name from the "ReportTbl" Table "ClientName" field that corresponds with the 3 letters in the Report number.

i.e. DIA is the client Diamond and TRA (from HE-182-TRA-DR-022) is the client Transocean.

I hope this helps and i'm sorry if its a bit confusing as I am trying to work out how to achieve this?

 

[Isskint]

Hi Sam

3 questions for you.

ReportTbl has a field called ClientName, is there a separate field that stores the 3 character code ~ DIA, TRA ~ or is that just the first 3 letters of ClientName?

Little confusion over the control type you are using on the Main form for the identity/serial number. In the OP you said it was combobox but in your reply you say textbox. Which is it? If it is a combobox can you please include the rowsource SQL?

If there can only be 1 ClientName from the number HE-168-DIA-DR-003, why do you want to use a combobox for the ClientName? A textbox may suit.

Don't worry about confusion :banghead:, have a look through the posts on here and you will see very few posters acuratley state the precise problem and associated details first time

 

[Sam Summers]

Thank you again,

You are right with your first question - its just the first 3 letters of ClientName.

Sorry about the second - it is a textbox (ReportNo)

If you clicked on the Combobox (ClientName) you would see a list of all the clients but I guess it probably could be a textbox as long as the details on the rest of the form referred to that report number each time new details are entered on selecting a button to save the entered details?

I think it will work somehow? Its just figuring it out?