Alternative To The Switch or IIF Functions in Expressions

[gameemaster]

I have an IIF statement that looks like this:



Vender_1: IIf([Transaction text] Like "*Doody*","Doody Calls",IIf([Transaction text] Like "*Home Depot*","Home Depot",IIf([Transaction text] Like "*American Asphalt*","American Asphalt",IIf([Transaction text] Like "*Brightview*","Brightview",IIf([Transaction text] Like "*Lowes*","Lowe's",IIf([Transaction text] Like "*Lowe's*","Lowe's",IIf([Transaction text] Like "*Cintas*","Cintas",IIf([Transaction text] Like "*PPG Arch*","PPG Architectural Finishes",IIf([Transaction text] Like "*bulbs.com*","Bulbs.com",IIf([Transaction text] Like "*Orkin*","Orkin",IIf([Transaction text] Like "*New Pipes*","New Pipes, Inc.",IIf([Transaction text] Like "*Sherwin Will*","Sherwin Williams",IIf([Transaction text] Like "*Sherwin-Will*","Sherwin Williams",IIf([Transaction text] Like "*Staples*","Staples",""))))))))))))))

I got only fit like 14 or 15 expressions into this statement, same with the Switch() function, it tells me that the expression is too complex to run in the query. I only have 4 more expressions I need to add to the IIF statement, but I can't because the expression builder won't let me -- stating that it's too complex.

Does anyone know of a different function other than the IIF or Switch statement that allows for more expressions to be added to the formula -- I only need to add 4 more, thank you.

[JoeM]

Anytime you have a calculation that us that long, I prefer to create my own User Defined Function to do it. It makes it much more manageable and easier to support.
Within the User Defined Function, I would use a CASE statement for all your different options.
See: https://www.techonthenet.com/access/...anced/case.php

[pbaldy]

I would also use a function, though I might also have a table with the associations, rather than have it hard-coded into an expression or function. Makes it more dynamic and user-maintainable.

[CJ_London]

I'm not disagreeing with the option of moving to a UDF function, but the error is usually generated when you have a null value in your data.

Having spaces in your field names doesn't help, particularly when one of the words is 'text' which is a reserved word. I'm not saying it will makes a difference in this case, but it might.

you should also think about the situation where you have additional vendors - you are going to have to modify your query.

depends on how your query is intended to work but in you might have something like

SELECT table1.*, tblVendors.vendorname as Vendor1
FROM table1 LEFT JOIN tblVendors ON table1.TransactionText Like "*" & tblVendors.vendorname & "*"

Note that due to the nature of the join, this query is not viewable in the query builder window, only in the sql window., Also it does not cover multiple spellings such as you have indicated for Sherwin Williams. You might be able to refine the query to cover this - e.g.

.... ON replace(table1.TransactionText,"-"," ") Like "*" & tblVendors.vendorName & "*"

[John_G]

From looking at your query, I'm guessing that you are trying to "standardize" company names that have been entered by users.

If you are importing the data from elsewhere (e.g. Excel), there's no much you can do, but if the data is being entered in another Access form, you should look at having the users select from a combo box list, and store company ID (usually numeric) instead of text. You can always decode it in a query or form when you need to see the name.

[gameemaster]

I earnestly thank you all very much for your replies. I agree the spaces are a problem, I didn't build the database; I'm just trying to make it better. The developer of the DB created only tables and update queries to change the data. I've been able to get rid of 30 update queries and condense it down to a single select query within a select query with calculated fields and functions. John_G you're right, I could suggest to my colleague that we create user forms on the front end to standardize the data, then it wouldn't be a problem -- i dont know the feasibility of that though.

Ajax, also yes, you're correct. There could be unforeseen vendors, if it's done through VBA code (which I think a UDF is through?) then I can add them, but like pbaldy said, i should hard code it to a table, which actually exists, although I don't know how to do that nor create a CASE or UDF. My boss told me to do the CASE statement and that would support more, but like JoeM said, that is done through a UDF and I don't know how to do that? I've done basic coding and simple joins in access, but never a User Defined Function or a CASE statement. I have gotten a RANK query to work before.

Ajax, I really dont understand what you're try to say, I understand you have to write the code in SQL view. But i dont understand what left join (un-match) would do for me. Do I do a CASE statement in the SQL view, or is it done in VBA? If someone could walk me through how to build the CASE statement in a UDF and possible hook it up to the table -- I'd greatly appreciated, I can follow pretty technical directions, especially in access.

Thanks for all the help guys. Really appreciate it. I've been developing my own personal database for several years, so I would enjoy to learn something new.

[CJ_London]

But i dont understand what left join (un-match) would do for me

the left join will display all records in table1, rather that with an ordinary join where table1 records would only be displayed if a suitable match was found in your vendor table. In this way you can identify what table1 records (if any) have not been matched to a vendor record.

I would suggest you try it - create a table called tblVendors. On reflection with two fields

tblAliases
AliasPK autonumber
Alias text
VendorName text

Populated like
AliasPK...Alias..............VendorName
1...........Home Depot...Home Depot
2...........Lowe's...........Lowe's
3...........Lowes............Lowe's
4...........Sherwin Will...Sherwin Williams
5...........Sherwin-Will...Sherwin Williams
etc

then your query would be

SELECT table1.*, tblAliases.vendorname as Vendor1
FROM table1 LEFT JOIN tblAliases ON table1.TransactionText Like "*" & tblAliases.Alias & "*"


substitute table1 with the name of your table with the 'transaction text' field

any records returned without a vendor1 value need to be inspected to determine what identifies the supplier and tblAliases updated accordingly

[gameemaster]

works great. the left join query does the job. thanks so much Ajax

[gameemaster]

Ajax's solution fixed the issue I was having, thanks guys!