How to test for Large Number (1 Viewer)

[tvanstiphout]

The Large Number data type is not very well supported in VBA. FWIW, I am using 32-bit Access 365, with support for large numbers checked.
Assuming I have a string value and I want to test if it is a valid Large Number, how would I do it?
Large numbers pass IsNumeric - good, but the below technique where I test for Long Integer cannot be used. What would be a good alternative?

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
On Error Resume Next
Dim blnReturn As Boolean
blnReturn = (CLng(varValue) = varValue)
blnReturn = (Err.Number = 0)
IsLongIntegerValue = blnReturn
End Function

 

[MajP]

From copilot

' ========= Module: BigIntValidate.bas =========
Option Explicit

' Public entry point:
' Returns True if s represents a valid signed 64-bit integer (Access Large Number / BigInt)
Public Function IsValidLargeNumber(ByVal s As String) As Boolean
    Dim sign As Integer, digits As String

    ' Normalize whitespace
    s = Trim$(s)
    If Len(s) = 0 Then
        IsValidLargeNumber = False
        Exit Function
    End If

    ' Extract sign and digits
    sign = GetSign(s)
    digits = StripSign(s)

    ' Must be all digits and not empty
    If Len(digits) = 0 Or Not IsAllDigits(digits) Then
        IsValidLargeNumber = False
        Exit Function
    End If

    ' Remove leading zeros for magnitude comparison
    digits = StripLeadingZeros(digits)

    ' Zero is always valid
    If digits = "0" Then
        IsValidLargeNumber = True
        Exit Function
    End If

    ' Compare magnitude against max/min 64-bit bounds
    If sign >= 0 Then
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775807")
    Else
        ' For negative, compare to absolute of min bound (note: min has larger magnitude)
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775808")
    End If
End Function

' -------- Helpers --------

Private Function GetSign(ByVal s As String) As Integer
    ' Returns +1 for positive/+, -1 for negative/-, 0 for invalid sign char
    If Left$(s, 1) = "-" Then
        GetSign = -1
    ElseIf Left$(s, 1) = "+" Then
        GetSign = 1
    Else
        GetSign = 1 ' default positive
    End If
End Function

Private Function StripSign(ByVal s As String) As String
    If Left$(s, 1) = "-" Or Left$(s, 1) = "+" Then
        StripSign = Mid$(s, 2)
    Else
        StripSign = s
    End If
End Function

Private Function StripLeadingZeros(ByVal s As String) As String
    Dim i As Long
    i = 1
    Do While i < Len(s) And Mid$(s, i, 1) = "0"
        i = i + 1
    Loop
    If i > Len(s) Then
        StripLeadingZeros = "0"
    Else
        StripLeadingZeros = Mid$(s, i)
    End If
End Function

Private Function IsAllDigits(ByVal s As String) As Boolean
    Dim i As Long, ch As String
    If Len(s) = 0 Then
        IsAllDigits = False
        Exit Function
    End If
    For i = 1 To Len(s)
        ch = Mid$(s, i, 1)
        If ch < "0" Or ch > "9" Then
            IsAllDigits = False
            Exit Function
        End If
    Next i
    IsAllDigits = True
End Function

' Returns True if numeric string a <= numeric string b (no sign, no leading zeros required)
Private Function MagnitudeLE(ByVal a As String, ByVal b As String) As Boolean
    ' First by length, then lexicographic
    If Len(a) < Len(b) Then
        MagnitudeLE = True
    ElseIf Len(a) > Len(b) Then
        MagnitudeLE = False
    Else
        MagnitudeLE = (a <= b)
    End If
End Function
``

 

[DaveMD]

I've dealt with this type of thing before with very large numbers. There is an easy but not well documented solution. Change your CLng() cast to Fix(). Fix can handle working with Doubles (8 byte numbers) while CLng is limited to 4 byte numbers which is why it errors out on large numbers.

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
    Dim blnReturn As Boolean
    blnReturn = (Fix(varValue) = varValue)
    IsLongIntegerValue = blnReturn
End Function

 

[tvanstiphout]

Ha! I thought of doing something like your MagnitudeLE function, because "123" is less than "456", as a string comparison.
Thank you very much.

 

[tvanstiphout]

I've dealt with this type of thing before with very large numbers. There is an easy but not well documented solution. Change your CLng() cast to Fix(). Fix can handle working with Doubles (8 byte numbers) while CLng is limited to 4 byte numbers which is why it errors out on large numbers.

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
    Dim blnReturn As Boolean
    blnReturn = (Fix(varValue) = varValue)
    IsLongIntegerValue = blnReturn
End Function

I thought about the similar CDec, which does indeed work at least for some large numbers, but I like the exact solution by MajP even better.

 

[NauticalGent]

This thread intrigued me, I had no idea what the actual problem was, so of course the solutions didn't clear it up for me either. I turned to ChatGPT and thought it might be prudent to share the conversation here:

Understanding BigInt Validation in Access VBA​

Why Fix() Is Interesting — and Why It’s Not Enough​

This discussion came out of an Access forum question about determining whether a value represents a Large Number (BigInt). Along the way, we touched on Fix(), classic VBA idioms, and a robust string-based BigInt validator.
This write-up captures the full reasoning.

---

1. The Original Curiosity: Fix()​

The original code that caught my attention was:

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
    Dim blnReturn As Boolean
    blnReturn = (Fix(varValue) = varValue)
    IsLongIntegerValue = blnReturn
End Function

At first glance, this looks clever — and it is, within a narrow scope.

What Fix() Does​

• Removes the fractional part of a number
• Does not round
• Always truncates toward zero

Examples:

Input	Fix()
5.9	5
5.0	5
-5.9	-5

So this comparison:

Fix(x) = x

Is simply asking:

“Does this number have a fractional component?”

In other words:

• ✔ Detects whole numbers
• Does not detect data types
• Does not detect Long vs BigInt
• Does not enforce numeric limits

---

2. Why Fix() Cannot Detect Large Number (BigInt)​

In Microsoft Access:

Concept	Reality
Long	32-bit signed integer
Large Number	64-bit signed integer
VBA native BigInt type	None
DAO field type	dbLongLong

Because VBA has no native 64-bit integer, any attempt to detect BigInt by value is unreliable.
Example:

Fix(5000000000#) = 5000000000#   ' True

✔ Whole number
Not a Long
Not proof of BigInt
Stored as Double → precision risk
So while Fix() is mathematically elegant, it cannot identify a Large Number datatype.

---

3. The Only Authoritative Way (When You Have Metadata)​

If you have access to the table or query schema, the only reliable check is at the DAO level:

If fld.Type = dbLongLong Then
    ' This is a Large Number field
End If

This checks the actual storage type, not an inferred value.

---

4. When You Don’t Have Metadata: String-Based Validation​

Another forum user provided this solution, which is correct and robust.
It validates whether a string represents a valid signed 64-bit integer — without ever converting it.

Why This Works​

• No overflow
• No rounding
• No implicit coercion
• No precision loss
• Mirrors CPU-level integer limits

This is exactly how BigInt validation must be done in VBA.

---

5. The BigInt Validation Module​

' ========= Module: BigIntValidate.bas =========
Option Explicit

' Returns True if s represents a valid signed 64-bit integer
Public Function IsValidLargeNumber(ByVal s As String) As Boolean
    Dim sign As Integer, digits As String

    s = Trim$(s)
    If Len(s) = 0 Then Exit Function

    sign = GetSign(s)
    digits = StripSign(s)

    If Len(digits) = 0 Or Not IsAllDigits(digits) Then Exit Function

    digits = StripLeadingZeros(digits)

    If digits = "0" Then
        IsValidLargeNumber = True
        Exit Function
    End If

    If sign >= 0 Then
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775807")
    Else
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775808")
    End If
End Function

Supporting Helpers (unchanged)​

Private Function GetSign(ByVal s As String) As Integer
    If Left$(s, 1) = "-" Then
        GetSign = -1
    Else
        GetSign = 1
    End If
End Function

Private Function StripSign(ByVal s As String) As String
    If Left$(s, 1) = "-" Or Left$(s, 1) = "+" Then
        StripSign = Mid$(s, 2)
    Else
        StripSign = s
    End If
End Function

Private Function StripLeadingZeros(ByVal s As String) As String
    Dim i As Long: i = 1
    Do While i < Len(s) And Mid$(s, i, 1) = "0"
        i = i + 1
    Loop
    StripLeadingZeros = IIf(i > Len(s), "0", Mid$(s, i))
End Function

Private Function IsAllDigits(ByVal s As String) As Boolean
    Dim i As Long
    For i = 1 To Len(s)
        If Mid$(s, i, 1) < "0" Or Mid$(s, i, 1) > "9" Then Exit Function
    Next
    IsAllDigits = True
End Function

Private Function MagnitudeLE(ByVal a As String, ByVal b As String) As Boolean
    If Len(a) < Len(b) Then
        MagnitudeLE = True
    ElseIf Len(a) > Len(b) Then
        MagnitudeLE = False
    Else
        MagnitudeLE = (a <= b)
    End If
End Function

---

6. Why This Solution Is Correct​

Aspect	Fix() approach	String approach
Detects BigInt safely		✔
Precision safe		✔
Overflow risk		✔
Handles user input		✔
Matches Access Large Number		✔

This approach respects VBA’s limitations instead of fighting them.

---

7. Final Takeaway​

• Fix() is a legitimate, old-school VBA function
• It answers a mathematical question, not a typing one
• Large Number (BigInt) detection:
  • ✔ Use DAO metadata when possible
  • ✔ Use string-based validation when not
  • Never rely on numeric coercion

The curiosity about Fix() was justified — but string validation is the only safe BigInt solution in VBA.

 

[sonic8]

Large numbers pass IsNumeric - good, but the below technique where I test for Long Integer cannot be used. What would be a good alternative?

Here is a crude approach that I find much more comprehensible than Copilot's suggestion:

Public Function IsLargeInteger(ByVal varValue As Variant) As Boolean
    If IsNumeric(varValue) Then
        If CDec(varValue) >= CDec("-9223372036854775808") _
            And CDec(varValue) <= CDec("9223372036854775807") _
        Then
            Dim signed As Boolean: signed = CBool(CDec(varValue) < 0)
            IsLargeInteger = CBool(CStr(varValue) = CStr(Format(varValue, String(Len(varValue) + signed, "0"))))
        End If
    End If
End Function