Excel formula QUOTIENT,INDIRECT in access

[kentck86]

Hi Guys,



I have this long conversion works in excel, but as i have a huge dynamic database in access. Please advises how can this works the same in microsoft access for this case would be much appreciated.Sample.zip

F2=RIGHT(DEC2BIN(MOD(QUOTIENT(C2,256^4),256),8),4) &DEC2BIN(MOD(QUOTIENT(C2,256^3),256),8)&DEC2BIN(MO D(QUOTIENT(C2,256^2),256),8)&DEC2BIN(MOD(QUOTIENT( C2,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^0),2 56),8)

B2=SUMPRODUCT(--MID(E2,LEN(E2)+1-ROW(INDIRECT("1:"&LEN(E2))),1),(2^(ROW(INDIRECT("1 :"&LEN(E2)))-1)))
A2=SUMPRODUCT(--MID(D2,LEN(D2)+1-ROW(INDIRECT("1:"&LEN(D2))),1),(2^(ROW(INDIRECT("1 :"&LEN(D2)))-1)))



Thank you

[June7]

Access does not have intrinsic function to convert decimal to binary. Means writing VBA to create custom function. https://www.devhut.net/2010/06/22/vb...al-and-binary/

Int() will do what Quotient() does.

SumProduct() would be a Sum(this * this) in an aggregate query or in textbox on form or report.

Instead of Indirect() refer to field that has value.

Mid, Len, Left, Right are available in Access but Right and Left don't allow for a range for start and stop position - use Mid.

Mod syntax is different.

Here is an example using your data in Access query:

SELECT nrCellIdentity, Right(Dec2Bin(Int([nrCellIdentity]/256^4) Mod 256,8),4) AS Bin FROM Sample;

Since your formula for column F will be quite long, should probably build another VBA custom function for this calc.