Bin2Dec Function in Excel also available in Access ?

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Nick

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.
 

TartanSpecial

New Member
Joined
May 29, 2005
Messages
42
Thanks for your help as always Richard

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

etc

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
End Function

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 !

Thanks again for all your help

Nick
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Nick

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.

HTH, Andrew
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Nick

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:

Code:
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)
    Next
End If
BinToDec = lTemp
End Function

Note that ths function performs no error checking (so it will actually 'convert' a non-binary string such as 7073 (treating it as 1011)).
 

Watch MrExcel Video

Forum statistics

Threads
1,112,883
Messages
5,543,019
Members
410,583
Latest member
gazz57
Top