VBA Need Advise/Direction (1 Viewer)

[ChuckDee]

Hi Everyone,

First thank you to all for the guidance I was given on my “ Looking for Better Way Using VBA” post. Now that I understand how the VBA is works, I am now looking to use a another table that will be a list of positions that I will I will be searching for just like Pung showed me how to do in VBA.

What I will be doing here is as follows:

• Instead of “If in_Text Like "*Product Manager*" Then ret = "Product Manager"
  I want to have something like:
• If in_Text like “*positions_table.field_1*” then ret = “positions_table.field_2”

positions_table.field_2 contains the actual name of the position and is located on the same row as positions_table.field_1.

The problem I have is that I really don’t understand how I’m doing the search or how I place the result that is in positions_table.field_2.

What I am looing for is directions on how to get to the answer.

As always grateful for any help.

 

[arnelgp]

you can just use Query to return field_2 from positions_table:

select field_2 from positions_table where field_1 like "*" & Forms!yourFormName!in_Text & "*";

 

[Pat Hartman]

LIKE is ONLY used when you have a partial string and at least one wild card. If you are not using it as a search criteria, then you are better off using InStr() to return the value you want.

Iff(Instr(somecolumn, "somestringwithoutwildcards") > 0, "return true", "return false")

 

[ChuckDee]

LIKE is ONLY used when you have a partial string and at least one wild card. If you are not using it as a search criteria, then you are better off using InStr() to return the value you want.

Iff(Instr(somecolumn, "somestringwithoutwildcards") > 0, "return true", "return false")

So Pat, please forgive my ignorance, but can you explain how this would look for every word in that column (col 1) until the word is found & then return the result from column 2. the word has to have the wildcard in front and end cause it is buried in content…


Thanks really appreciate it.

 

[Pat Hartman]

Instr() looks for a string. "pat" in another string. "Patricia", " Patrick", "Pattern", "Patriot","expat", etc. It does not use wildcards but it doesn't matter where in the string the search term exists. Where the IIf() says "return true", that can be any valid expression including another IIf().

Iff(Instr(Column1, "somestringwithoutwildcards") > 0, Column2, "return false")

The Instr() returns the position of the first character of the string. So, for the first 4 examples, the value would be 1. for the last example, the value would be 3. If the value is not found, the value is 0.

LIKE is an SQL term and is only used in SQL.

I can't tell whether you are looking for an SQL solution or a VBA solution. I can tell you that mushing values into a single field is poor practice. If you are doing this with SQL, you should have a table of positions. You should be looking for a value = to the search criteria, not Like it. Then you can return a different column from the position table. Instead of dumbing down your question and showing us non-working code, you might tell us in English, what you are trying to get as a result.

 

[ChuckDee]

Instr() looks for a string. "pat" in another string. "Patricia", " Patrick", "Pattern", "Patriot","expat", etc. It does not use wildcards but it doesn't matter where in the string the search term exists. Where the IIf() says "return true", that can be any valid expression including another IIf().

Iff(Instr(Column1, "somestringwithoutwildcards") > 0, Column2, "return false")

The Instr() returns the position of the first character of the string. So, for the first 4 examples, the value would be 1. for the last example, the value would be 3. If the value is not found, the value is 0.

LIKE is an SQL term and is only used in SQL.

I can't tell whether you are looking for an SQL solution or a VBA solution. I can tell you that mushing values into a single field is poor practice. If you are doing this with SQL, you should have a table of positions. You should be looking for a value = to the search criteria, not Like it. Then you can return a different column from the position table. Instead of dumbing down your question and showing us non-working code, you might tell us in English, what you are trying to get as a result.

Okay thank you for the info and yes I actually did create a table (company_positons) which has to columns (positions & result) SQL solution would be the ideal since eventually the plan is to move all this in to SQL & create a web app. You see I use to do all this but suffered a TBI accident where I broke my neck & spinal injury which has caused some memory issues for me. So please forgive me for all the question. I really do appreciate the help I am getting here on this forum. I am looking for a tutor also. (Just can’t afford much) - Sorry for the long explanation, but suggested I should explain myself - thank you…

 

[Pat Hartman]

Using a table is the best solution. Much more efficient than LIKE and InStr. Use a left join in the query if the Position is optional. If it is required, then an inner join will be fine.