Solved Looking for Better Way Using VBA

[ChuckDee]

Morning,

Looking for a little advise and help. I have created a Access Db that contains 3 tables and one query.

The 3 tables are:

• Job Types
• Sent items
• Remote Jobs

The fields I am concerned with are

• Sent.Subject
  I am searching in the content of the field to see if it has a keywork or list of keywords that I am looking for.
• Position – I am creating this field in the query that I am creating and in this field I am using the following to find a keyword then print the keyword found.
• Example:

Position: IIf([Sent.Subject] Like "*Scrum Master*","Scrum Master",IIf([Sent.Subject] Like "*Project Manager*","Project Manager",IIf([Sent.Subject] Like "*Program Manager*","Program Manager",IIf([Sent.Subject] Like "*Product Owner*","Product Owner",IIf([Sent.Subject] Like "*Product Manager*","Product Manager",IIf([Sent.Subject] Like "*Agile Coach*","Agile Coach",IIf([Sent.Subject] Like "*Change Management*","Change Management",IIf([Sent.Subject] Like "*Test Lead*","Test Lead",IIf([Sent.Subject] Like "*Delivery Manager*","Delivery Manager",IIf([Sent.Subject] Like "*Delivery Lead*","Delivery Lead",IIf([Sent.Subject] Like "*Business Analyst*","Business Analyst",IIf([Sent.Subject] Like "*Discovery*"," Business Analyst ",IIf([Sent.Subject] Like "*Product Management*","Product Manager",IIf([Sent.Subject] Like "* Program/Portfolio Manager*","Program Manager"))))))))))))))

As you can see the above is very messy and I been told I can achieve a better solution in VBA.

Here is the criteria I am trying to solve for:

Scrum Master	Scrum Master
Project Manager, Digital Project Manager, Project Mgr.	Project Manager
Program Manager, Program/Portfolio Manager	Program Manager
Product Owner	Product Owner
Product Manager, Product Management,	Product Manager
Agile Coach	Agile Coach
Change Management	Change Management
Test Lead	Test Lead
Delivery Manager	Delivery Manager
Business Analyst, Analyst, Discovery,	Business Analyst
Delivery, Delivery Lead, Delivery Manager	Delivery Lead/Mgr.

Any help and or advice is gratefully accepted.

Thanks

 

[theDBguy]

I would create a table that you can use to look up the value you want from the keyword criteria.

 

[plog]

You're right, that messy monstrosity becomes so much easier for a human to read and update in VBA. Here's how you get there:

Public Function get_Position(in_Text As String) As String
  ' takes text (in_Text) and extracts desired Position from it

Dim ret
ret = "ERROR"
  ' return value, default is an error if nothing is found
 
If in_Text Like "*Scrum*" Then ret = "Scrum Master"
If in_Text Like "*Product Manager*" Then ret = "Product Manager"
If in_Text Like "*Project Manager*" Then ret = "Project Manager"

' ... more cases here



get_Position = ret
' returns the position value found in the text

End Function

You need to move all those Iff statements in your current code into the VBA function above like I did the first three. Then in the query you would put this to make it work:

Position: get_Position([Sent.Subject])

 

[XPS35]

What is messy here, is your table design. Placing multiple values in a field is a guarantee for problems.
I also wonder what should happen if there are two or more values in the field that are interesting. Now you select the one you happen to come across first.

 

[Pat Hartman]

Now you select the one you happen to come across first.

Actually, the code snippet returns the value for the string found LAST.

 

[ChuckDee]

You're right, that messy monstrosity becomes so much easier for a human to read and update in VBA. Here's how you get there:

Public Function get_Position(in_Text As String) As String
  ' takes text (in_Text) and extracts desired Position from it

Dim ret
ret = "ERROR"
  ' return value, default is an error if nothing is found

If in_Text Like "*Scrum*" Then ret = "Scrum Master"
If in_Text Like "*Product Manager*" Then ret = "Product Manager"
If in_Text Like "*Project Manager*" Then ret = "Project Manager"

' ... more cases here



get_Position = ret
' returns the position value found in the text

End Function

You need to move all those Iff statements in your current code into the VBA function above like I did the first three. Then in the query you would put this to make it work:

Position: get_Position([Sent.Subject])

You're right, that messy monstrosity becomes so much easier for a human to read and update in VBA. Here's how you get there:

Public Function get_Position(in_Text As String) As String
  ' takes text (in_Text) and extracts desired Position from it

Dim ret
ret = "ERROR"
  ' return value, default is an error if nothing is found

If in_Text Like "*Scrum*" Then ret = "Scrum Master"
If in_Text Like "*Product Manager*" Then ret = "Product Manager"
If in_Text Like "*Project Manager*" Then ret = "Project Manager"

' ... more cases here



get_Position = ret
' returns the position value found in the text

End Function

You need to move all those Iff statements in your current code into the VBA function above like I did the first three. Then in the query you would put this to make it work:

Position: get_Position([Sent.Subject])

Hey Thanks Plog,
I think that this is what I was looking for. Now being new to access and VBA, I have to ask - so this code will work for every record (Row) in the table - right?

 

[ChuckDee]

I would create a table that you can use to look up the value you want from the keyword criteria.

The JobsType table is wat I had created as a lookup table

 

[theDBguy]

The JobsType table is wat I had created as a lookup table

Here's an example of the table structure I was thinking about.

Keyword	Position
Scrum Master	Scrum Master
Project Manager	Project Manager
Digital Project Manager	Project Manager
Project Mgr.	Project Manager
Program Manager	Program Manager
Program/Portfolio Manager	Program Manager
etc.

You can then JOIN this table to your other table to return the Position column. Hope that makes sense...

 

[plog]

I think that this is what I was looking for. Now being new to access and VBA, I have to ask - so this code will work for every record (Row) in the table - right?

Define 'work'? I mean it's going to search whatever text you send it and send back whatever match it makes last.

It's not a lot of work to test it--paste my above code into a module, paste the below SQL into a query replace YourTableNameHere appropriately and then run this query:

SELECT [Sent.Subject], get_Position([Sent.Subject]) AS Position
FROM YourTableNameHere

You will be able to see what data you passed it in one column and what it returned in the other.

 

[ChuckDee]

Define 'work'? I mean it's going to search whatever text you send it and send back whatever match it makes last.

It's not a lot of work to test it--paste my above code into a module, paste the below SQL into a query replace YourTableNameHere appropriately and then run this query:

SELECT [Sent.Subject], get_Position([Sent.Subject]) AS Position
FROM YourTableNameHere

You will be able to see what data you passed it in one column and what it returned in the other.

 

[Galaxiom]

Only two answers make sense.

XPS35 correctly points to the central problem is storing multiple values in one field. They should be in a related table.

Overlooking that fact, theDBguy suggests how the problem could be approached using the database engine to do the work.

Techniques using hard coded search terms whether in queries or in VBA are utterly ridiculous. Every time new data is included the code or query has to be changed. Any such requirements mean the solution is inept. Changes to the capabilities of a database should be made by adding or changing records in tables, not editing code or queries.

 

[ChuckDee]

Hi,
First off thank you very much for helping such a novice.
Here is what I've done
Created a Module:

Next I have entered the SQL code as instructed:

When I try to Run it I get:

Funny thing is that I can open any query that does not have SQL associated to it. - any ideas?

 

[ChuckDee]

Only two answers make sense.

XPS35 correctly points to the central problem is storing multiple values in one field. They should be in a related table.

Overlooking that fact, theDBguy suggests how the problem could be approached using the database engine to do the work.

Techniques using hard coded search terms whether in queries or in VBA are utterly ridiculous. Every time new data is included the code or query has to be changed. Any such requirements mean the solution is inept. Changes to the capabilities of a database should be made by adding or changing records in tables, not editing code or queries.

Thanks Galaxiom,
I understand what you are saying I do have a table that contains all the jobs - so I do believe that is what you are talking about

 

[plog]

Go to your first post in this thread. You posted part of a query that you said worked but was tedious. What is the full SQL of that code?

You need to replace the FROM in my code with the FROM in that code.

 

[ChuckDee]

Plog,
Thank you for all the help and for taking a newby by the hand. I really appreciate it. My next step is going to be to have it reference the table called JobTypes using maybe a DLookup. Again, thanks for the help Very much appreciate it.

 

[Pat Hartman]

My next step is going to be to have it reference the table called JobTypes using maybe a DLookup

Bad idea. Use a left join to JobTypes instead. Domain functions do not belong in queries or in code loops if there is any other option and there almost always is.

 

[ChuckDee]

Bad idea. Use a left join to JobTypes instead. Domain functions do not belong in queries or in code loops if there is any other option and there almost always is.

Thanks Pat I'll check into that.

 

[Galaxiom]

I understand what you are saying I do have a table that contains all the jobs - so I do believe that is what you are talking about

I don't think so.
The technique theDBguy and I are talking about uses a join between the data and the table with the lookups.

The following returns all matches.

SELECT Sent.ID, Sent.Subject, JobType.Position
FROM Sent
INNER JOIN  JobType ON Sent.Subject LIKE "*" & JobType.Keyword & "*"

To cover multiple matches if you only want one, include a field for priority in the JobType table and return only the highest priority.