I have an entire process in Access with many queries that was written before BigInt was supported. There is a table that I had to import because it contained a BigInt field. Now that I'm on O365, I can work with BigInt, but I don't want to alter all my tables and queries to support BigInt. Instead, I will create new queries that can compare the text version of the BigInt field that was imported with the actual BigInt in the table now that I can link to it so that I can update the linked table using queries. The field has up to 17 digits.
I've tried Val(textkey), but that produces scientific notation. When formated, only shows the left 15 digits and trailing 00.
Cdbl() and Csng() do the same. CLNG() gives me #Num! errors. Cdec() gives me an Arguments error).
Use Decimal or convert to BigInt:
LongTextNumber = "1234567890123456"
? VBA.CDbl(LongTextNumber)
1.23456789012346E+15
? VBA.CLngLng(LongTextNumber)
1234567890123456
? VBA.CDec(LongTextNumber)
1234567890123456
To convert to Decimal in a query, use this wrapper:
Public Function CDec2(ByVal Value As Variant) As Variant
CDec2 = CDec(Value)
End Function