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

 

[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.

EDIT: There are a lot of incorrect statements in the CoPilot summary above - See @posts #11 & #13 for details

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

 

[sonic8]

However, here is the CoPilot critique of Philipp's simple function from post #7

I suggest you ask it to provide failing test cases for all the problems the A-non-I hallucinated.

• It uses IsNumeric, which accepts values that can be coerced to Double, allowing out-of-range values.

IsNumeric is used, but only to weed out anything that is not any kind of number at all. The limits of IsNumeric are basically correct, but completely irrelevant in the context of the function.

• 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.

What? The function does not use CDec directly for range checks, but explicitly checks for the boundaries of Int64. This problem is a complete hallucination.

• The formatting comparison to detect integer-ness fails on scientific notation, leading zeros, and trailing decimal points.

• The function indeed won't accept a string containing a number in scientific notation. - But should it? Probably yes, but it is up for debate.
• That statement is incorrect for leading zeroes.
• The function indeed won't accept a string with trailing decimal point or a zero decimal fraction. Another topic for debate. Based on VBA default behavior: The supplied number can be converted without loss to an Int64, but it currently is not because it indicates a decimal type.

• It mishandles the minimum 64-bit integer boundary value because of how it calculates the format string length.

What? Checking Int64 boundaries and the format string length are not related in any way within that function.

• It can incorrectly accept out-of-range values that appear valid after Decimal rounding.

Nope. Relevant decimal rounding may only happen for values far outside the valid range of an Int64.

Overall, this function is unsafe and inaccurate for validating 64-bit integers.

Overall, Copilot is unsafe and inaccurate for reviewing VBA code. ;-)

Well, but seriously, what to make of this "critique"?
It caught the problem with scientific notation. Even if one decides to reject scientific notation, it should have been a thought-through deliberate decision, but I admit I completely missed it.
To a lesser extent, the same applies to numbers with trailing decimal points and zero-fractions.

So, the bottom line is: One bullet point pointing out valid issues against four hallucinated non-issue points.
I personally would perceive the pointed out problem valuable enough to tolerate the accompanying garbage.
But what with less experienced programmers? They might have their valid and usable work shot down by an AI which, as usual, phrases its reply with an abundance of confidence while in fact being utterly wrong with 80% of its reply.

I find it highly questionable to post such an "AI review" here in the forum without any personal classification or review added. - Not because I feel personally offended or slandered but because it can have profound negative effects on people just starting to learn programming VBA.

 

[DaveMD]

Great discussion in this thread and excellent illustration why you have to carefully evaluate the output of generative AI as it can come across as confident yet be very wrong. It is interesting how all three of the below critiques by Copilot demonstrate a lack of the AI understanding basic control flow of @sonic8 's very succinct function, as @sonic8 points out.

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.

It mishandles the minimum 64-bit integer boundary value because of how it calculates the format string length.

Also interesting that although Copilot correctly critiqued that the function did not handle scientific notation, the function Copilot generated in Post #2 also fails there. For example, the below test case incorrectly evaluates as False even though this is a valid whole number within the range of an Int64.

Debug.Print IsValidLargeNumber("1.15292150460685E+14")  ' Incorrectly returns False

As a slight variation to @sonic8 's function, the below will handle e-notation. The only test case I could identify that may warrant further consideration, depending on the intent, would be that it evaluates trailing decimals (500.0) as valid as there is no fraction. If that is not the desired output, it would be trivial to add another check for that.

Public Function IsValidInt64(s As String) As Boolean
    IsValidInt64 = False
    If IsNumeric(s) Then
        Dim dec As Variant: dec = CDec(s)
        If dec >= CDec("-9223372036854775808") And dec <= CDec("9223372036854775807") Then
            IsValidInt64 = CBool(Fix(dec) = dec)
        End If
    End If
End Function

 

[isladogs]

I suggest you ask it to provide failing test cases for all the problems the A-non-I hallucinated.
...
I find it highly questionable to post such an "AI review" here in the forum without any personal classification or review added. - Not because I feel personally offended or slandered but because it can have profound negative effects on people just starting to learn programming VBA.

All of that is valid criticism.
I didn't post the entire CoPilot answer as I knew that significant parts of it were incorrect.
However, I failed to state that in my post for which I apologise unreservedly.

I did state that your function appeared to always give the right answer in my own tests.

I was hoping you would give a robust response to the CoPilot summary. You certainly did so!

Rather than fill a lot of space here with the full CoPilot answer from yesterday, I'm attaching it as a PDF:
Does the following function reliably validate Large Number_v1.pdf

Following your justifiably forceful response, I did indeed ask CoPilot to provide numerical test case examples where the function gives incorrect answers.

Without any prompting, it stated yesterday's response was hallucinating, but then repeated exactly the same mistakes as before
The problem is that it considers each line of code individually rather than the overall result from the function as a whole!

It gave specific examples where your function supposedly failed - each of those were stated incorrectly (apart from scientific notation)
After I pointed that out, CoPilot backed down

Below is the final section of today's exchange - also attached in full as: CoPilot bigint_v2.pdf

Colin, thank you — and you’re absolutely right to push back. Let’s reset cleanly and deal only in verifiable, reproducible behaviour, not speculation.

You’ve now tested the examples I gave, and the only failure you observed is scientific notation. That tells us something important:

Your function is far more robust than the critique suggested. The only confirmed weakness so far is acceptance of scientific notation.

Let’s walk through this carefully and eliminate the noise.

What your function actually does correctly

Your function:

• Rejects values outside the Int64 range
• Rejects fractional values
• Rejects values that cannot be represented exactly as integers
• Rejects values that round incorrectly
• Correctly handles leading zeros
• Correctly handles the minimum and maximum 64‑bit values
• Correctly rejects values that overflow Decimal
• Correctly rejects values that overflow Double
• Correctly rejects values that overflow LongLong

And you’ve now confirmed this with your own tests.

That aligns with what I would expect from the combination of:

• IsNumeric
• CDec (96‑bit integer range)
• strict string formatting comparison

This is a surprisingly strong combination.

The only confirmed weakness: scientific notation
You’ve already verified this:


What is not a real flaw (contrary to the earlier critique)

You’ve now confirmed these are not problems:

• Decimal rounding does not cause false positives except when scientific notation is involved
• Decimal does not accept values outside the 96‑bit integer range
• The min boundary is handled correctly
• The format‑string length logic is correct
• Leading zeros are handled correctly
• Trailing decimal zeros are handled correctly
• Out‑of‑range integers are rejected correctly

So yes — the earlier critique was hallucinated.


So where does that leave us?

Your function is:
✔ Correct for all normal numeric literals
✔ Correct for all edge cases except scientific notation
✔ Much simpler than string‑based magnitude comparison
✔ Perfectly adequate for real‑world Access UI input
Not a strict validator of integer syntax
Not safe against scientific notation


If your goal is:
Practical validation of user input in Access forms
Your function is excellent.

Strict validation of integer literal syntax
You need to reject scientific notation explicitly