Help with ElseIf function please

[kiwijules0505]

Hi All,

I've got the Function below set-up to assign standardised names to cutomers whose names may have multiple different formats, e.g. Fonterra Ltd, Fonterra Limited, etc. The funtion is called from an Access Query.

Option Compare Database

Function Customertype(CUSTOMERNAME As Variant) As Variant

Dim x
If [CUSTOMERNAME] Like "*FONTERRA*" Then
    x = "Fonterra"
ElseIf [CUSTOMERNAME] Like "*Anchor*" Then
    x = "Fonterra"
ElseIf [CUSTOMERNAME] Like "*Alliance*" Then
    x = "Alliance"
ElseIf [CUSTOMERNAME] Like "*Dongwha*" Then
    x = "Dongwha"

End If

Customertype = x

End Function

While this solution is working fine, I would like to rather have this look-up data reside in a table, which will be easier for the end user to update than this function.

The table will have two fields:
SEARCHSTRING
STANDARDISEDNAME

I think that the solution may look something like this, but not sure at all:

For i =  1 To lastRow
     If DATA.CUSTOMERNAME Like  LOOKUPTABLE.SEARCHSTRING Then
     x =  LOOKUPTABLE. STANDARDISEDNAME
     End If
Next i

Can anyone point this newbie in the right direction please?

Thanks

 

[ajetrumpet]

Hi All,

I've got the Function below set-up to assign standardised names to cutomers whose names may have multiple different formats, e.g. Fonterra Ltd, Fonterra Limited, etc. The funtion is called from an Access Query.

I understand what you've said, but I'd be inclined to ask why you want to change it. What are you doing with this function anyway? Is this an UPDATE query? You said something about updating. I just think your description is vague. What exacltly are you wanting help with?

If you are doing any kind of updating, it really doesn't matter what function you call, because they user does not have to do that work; the program does. So, why change it? Why would that make things easier for the user?

 

[kiwijules0505]

Hi Adam,

Thanks for the reply.

Currently the list of customers stand at 25, but this will change over time, customers being added or deleted.

I don't want the end-user having to edit this function when changes need to be made - I would rather them just have to enter the details into the table I mentioned and that the function refer to the table.

More info on what this funtion does: This function populates a field in a table which assigns a standardised name for some of our customers who have multiple accounts with us.

For example: One of our customers have 20 accounts with us, such as Fonterra Foods, Fonterra Brands, Fonterra Diary, etc.

I wanted a field with with I can easily identify these accounts as essentially being the same "parent" account. The above function assigns "Fonterra" as the parent account name.

I hope this answers your question.

Regards
JUles

 

[CraigDolphin]

Jules,

FYI, check out this link http://www.devdos.com/vb/lesson4.shtml

It is a tutorial that explains how to use and manipulate recordsets (queries, tables etc) in VBA.

This is the approach that I think you would need to take to do what you want to do, in the way that you have suggested.

If you went that route your code would probably end up looking more like (air code)

dim db as DAO.Database
dim MyRS as DAO.Recordset
dim x as string

set db = CurrentDb()
Set MyRS = db.OpenRecordset("YourTableName", dbdbOpenDynaset)
If Not MyRS.EOF then MyRS.MoveFirst
Do while MyRS.EOF = False
     If CUSTOMERNAME Like  MyRS!SEARCHSTRING Then x =  MyRS!STANDARDISEDNAME
     MyRS.MoveNext
Loop
Customertype = x

However, I think there's another simpler way to do what you need using a simple domain aggregate function. This is likely to be much faster.

Function Customertype(CUSTOMERNAME As Variant) As Variant
CustomerType = Dlookup("STANDARDISEDNAME","YourTableName","[SEARCHSTRING] Like ('" & CUSTOMERNAME & "')")
End Function

You could even use the expression directly without creating a function to house it by changing the CustomerName variable to a control reference on a form or a field reference in a query.

I offered the former approach to maybe help you learn about recordsets for future use.

 

[KeithG]

Sounds like you have a normalization error to me. You should only be storing the Company name once and using the the records PK as an FK

 

[ajetrumpet]

If you want me to continue working here, my questions and follow-ups are below:

I don't want the end-user having to edit this function when changes need to be made - I would rather them just have to enter the details into the table I mentioned and that the function refer to the table.

OK, I understand that you don't want the function to have anything to do with record changes. Correct? (if you would want this, the function would have to change everytime)

More info on what this funtion does: This function populates a field in a table which assigns a standardised name for some of our customers who have multiple accounts with us.

I understand that it does this, and that is fine. It is a good setup to manipulate the string being entered to what you want it to read.

I wanted a field in which I can easily identify these accounts as essentially being the same "parent" account.

Well, if you want a way to track this, I would suggest creating two different fields in your "customers" table, one that says "Customer", which will refer to the actual account, and one that says "Parent Company / Account", which will refer to the parent "entity". If you do this, you can use the two fields to easily search for the "parent" name when you have an "account" that you are wanting information on. Does that makes sense?

In short (after my long a** explanation, I think the function is useful for customer entry, but not for manipulation of the data once you have the customer information already entered). If you're going to want to delete customer accounts at some point, you may want to write another function that extracts the portion of the "account" name that corresponds to the "parent" name, but that is completely up to you.

 

[Moniker]

Keith is most on it here, IMO. You don't want to store "Fonterra Foods", "Fonterra Dairy", etc. and then have to search for things to make a "parent" company. You create the "parent" company once, give it an ID, and then refer to all the related ("child") companies through the parent. This is basically 1NF. If you're not familiar with normalization, get up to speed on it. There are a ton of posts on the subject to be found, both in these forums and on the net in general.

In short, though, you'd set up some basic tables like this:

[b]t_Companies[/b]

[u]Comp_ID[/u]   [u]Comp_Name[/u]
1         Fonterra, Inc.
2         Not Fonterra, Inc.
.         .
.         .
.         .
(etc.)

[b]t_Company_Details[/b]

[U]Comp_ID[/U]   [u]Sub_ID[/u]   [u]Sub_Name[/u] ... (etc.)
1         1        Fonterra Foods
1         2        Fonterra Dairy
1         3        Fonterra Fondue
2         1        Not Fonterra Foods
2         2        Not Fonterra Dairy
.         .        .
.         .        .
.         .        .
(etc.)

Comp_ID in the table t_Companies is your primary key. Comp_ID is linked to t_Company_Detail and then Sub_ID is added to make a unique key in your DB. Therefore, everything that has a Comp_ID = 1 belongs to Fonterra, Inc., regardless of if there are 5 or 500 entries. To get to "Fonterra Fondue", a very simple query would do this:

SELECT * FROM t_Company_Detail WHERE Comp_ID = 1 AND Sub_ID = 3;

To get all the entries for Not Fonterra, Inc., you'd do this:

SELECT * FROM t_Company_Detail WHERE Comp_ID = 2;

And so on.

This is normalization (only 1NF, but that's still normalized, as it were), and this is the maintainable version of what you're trying to do. There's no code to write -- it's a query. You can control that query with drop-downs, listboxes, etc. through a form that your end-user enters data on. The end-user never sees the code, and you never have to update it for him/her.

Hopefully, that's enough to get you going.

 

[ajetrumpet]

Thanks Moniker.