Excel .match equivalent in Access

[Guest]

Is there a function equivalent to .match that can search through an array in
Access?

 

[Guest]

The closest thing in Access is the Choose function. It does differ in that
it does not have the match options to return the smallest or largest close
matches. It will only return the value found in the index or Null if no
match is found.

 

[Guest]

The Choose function only works if you provide the data in-line

Sub myTest()
Dim a
a = Array(1, 2, 3, 4)
MsgBox Choose(2, a)
End Sub

This function returns error 94 invalid use of Null.


Sub myTest()
MsgBox Choose(2, 1, 2, 3, 4)
End Sub

This function returns 2.

I couldn't see a function that would seek out a value from an array. My
followup question is:

Is there someone that has written a function that would efficiently search
an array? The array that I want to search will be approximately 5000
elements.

Thanks in advance for your help.

Guy

 

[Guest]

I did not say it was the same thing, I said it was the closest thing.
Mabye we need to step back one level and consider the array. 5000 entries in
an array seems to be a lot. What will this array be based on?

 

[Guest]

I have a customer table that has 4 different roles, Ship-T0, Sold-To, Bill-To
and Payer. Each customer can be used more than once. Example, Customer ABC
can be a primary sold-to customer but can also be a bill-TO customer for
several other sold-to customers.

What I need to do is extract customer information with out duplicating the
data. The only way that I can see that happening is to create an array that
would keep track of the extracted customers. The outputs is of this extract
are text files which are not exactly the best for searching. Changing or
adding a field in the customer table is not an option as all my data is
linked from multiple sources. Creating side table might be an option but i
never want to keep the data.

 

[Guest]

I think the idea of creating a table to use for this purpose is better than
creating an array. With this much info in an array, it may be a memory
problem. As to not wanting to keep the data, delete the data in the table
when you are done with it.

So, you would probably use an append query to load the data into the table.

 

[John Spencer]

The poster might be able to use a union query to get what he wants. It is
hard to tell for sure since we have to guess at the data structure and the
information the poster wants.

SELECT ShipTo as CustInfo
FROM Customers
UNION
SELECT SoldTo
FROM Customers
UNION
SELECT BillTo
FROM Customers
UNION
SELECT Payer
FROM Customers