Bin2Dec Function in Excel also available in Access ?

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top