Whilst I am not an Access expert by any means, I don't believe there is an equivalent Access function (it's only available in Excel via the Analysis Toolpak add-in anyway). You could write a custom function in VBA to do the same thing though. Could you elaborate on how you want to use it and under what circumstances? Thanks.
I was trying to get a priority list for our customers. Each client is listed and then has a YES/No for each field. Starting from left to right this determines the priority of the client - so for instance
Client Name, Preferential Client, Large Revenue Provider, Potential for Growth
has the values
Client A, Yes, No, No
Client B No, Yes, No
Client c No,Yes,Yes
Someone suggested I could use Binary to set the priority and although it's new to me I thought about giving it a try e.g. changing Yes = 1, No = 0.
I was going to check using the equivalent to BIN2DEC but as you mentioned I don't think I have this option in Access. I did get a book on Access which has a way of using Excel Functions in Access
It suggested something like
Public Function GAMMALN(number As Integer) As Double
Dim xl As Object
Set xl = CreateObject("Excel.Application")
GAMMALN = xl.WorksheetFunction.GAMMALN(number)
Set xl = Nothing
could be used but I can't seem to get this to work for BIN2DEC
Anyway if you have any thoughts please let me know but please don't go to much trouble as I realised by simply setting each field after client name to Ascending I can get exactly what I need !
Aren't you over-complicating this by using BIN2DEC? If you assign a value of 1 for a yes and 0 for no, then concatenate the 3 digits and convert into a number between 111 and 000, and sorting this value in descending order you get the highest priority clients at the top of the list. But as you already identified, you can get this by sorting on those three fields anyway.
I think in your specific situation (as you've discovered and Andrew suggested) the sort based on meaningful info (ie Yes/No) is best - it will make more sense to you if you (or a co-worker) comes back to the database after a break. However, in case you wanted to see how you might structure a UDF (which will work in access too) then:
Function BinToDec(str As String) As Long
Dim i As Long, lTemp As Long, j As Long
i = Len(str)
If i > 0 Then
lTemp = 0
For j = 1 To i
If CInt(Mid(str, j, 1)) Then lTemp = lTemp + 2 ^ (i - j)
BinToDec = lTemp
Note that ths function performs no error checking (so it will actually 'convert' a non-binary string such as 7073 (treating it as 1011)).