How to test for Large Number

[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

 

[DaveMD]

It's clear from the follow-up responses that I did not correctly interpret the intent of the OP's post. My response was assuming the intent was to be able to handle large numbers (more than the 4 byte limitation of the Long data type) to determine if the input parameter is an integer (whole number with no fraction, in the mathematical sense). This is something I have encountered in my work when working with large numbers while still supporting 32 bit Access. In VBA in 32 bit Access, using the Double variable type (while it is inherently a floating point type), provides a way to handle 8 byte integers. But, there are instances where you may want to determine whether that double is actually a whole number (integer) even though you are using the double type.

 

[isladogs]

It's clear from the follow-up responses that I did not correctly interpret the intent of the OP's post. My response was assuming the intent was to be able to handle large numbers (more than the 4 byte limitation of the Long data type) to determine if the input parameter is an integer (whole number with no fraction, in the mathematical sense). This is something I have encountered in my work when working with large numbers while still supporting 32 bit Access. In VBA in 32 bit Access, using the Double variable type (while it is inherently a floating point type), provides a way to handle 8 byte integers. But, there are instances where you may want to determine whether that double is actually a whole number (integer) even though you are using the double type.

The problem is that it doesn't actually check whether the value is within the large integer (BigInt) range: -9223372036854775808 to 9223372036854775807 (-2^63 to 2^63-1).
For example -92233720368547758080 is wrongly marked as a large integer using your code

Both the CoPilot code in post #2 and @sonic8's much simpler code in post #7 appear to always give the right answer.
However CoPilot doesn't like the code in post #7 either. Details to follow

 

[isladogs]

As stated above, both the code in post #2 and that in post #7 appear to give correct results in my tests
However, here is the CoPilot critique of Philipp's simple function from post #7

The function you posted is not a valid or reliable test for a large integer (BigInt) value in VBA for several reasons:

• It uses IsNumeric, which accepts values that can be coerced to Double, allowing out-of-range values.
• It uses CDec for range checks, but Decimal conversions can accept values outside the 64-bit integer range due to floating-point parsing and scientific notation.
• The formatting comparison to detect integer-ness fails on scientific notation, leading zeros, and trailing decimal points.
• It mishandles the minimum 64-bit integer boundary value because of how it calculates the format string length.
• It can incorrectly accept out-of-range values that appear valid after Decimal rounding.

Overall, this function is unsafe and inaccurate for validating 64-bit integers. The best practice is to do string-based magnitude comparisons without relying on floating-point or Decimal conversions, which ensures correct handling of all edge cases and the full 64-bit range.

After a couple of false starts, it offered this condensed version instead which also appears to work without using IsNumeric or CDec:

Public Function IsValidBigInt(ByVal s As String) As Boolean
    Const MINV$ = "-9223372036854775808"
    Const MAXV$ = "9223372036854775807"

    s = Trim$(s)
    If s = "" Then Exit Function

    Dim neg As Boolean: neg = (Left$(s, 1) = "-")
    If neg Or Left$(s, 1) = "+" Then s = Mid$(s, 2)

    If s = "" Or Not s Like String(Len(s), "#") Then Exit Function

    Do While Len(s) > 1 And Left$(s, 1) = "0": s = Mid$(s, 2): Loop

    If s = "0" Then IsValidBigInt = True: Exit Function

    If neg Then
        IsValidBigInt = CompareMag(s, Mid$(MINV, 2)) <= 0
    Else
        IsValidBigInt = CompareMag(s, MAXV) <= 0
    End If
End Function

Private Function CompareMag(a$, b$) As Integer
    If Len(a) <> Len(b) Then CompareMag = Sgn(Len(a) - Len(b)) _
    Else CompareMag = Sgn(StrComp(a, b, vbBinaryCompare))
End Function