Solved

Format Numeric Field To Show Commas

Posted on 2002-05-15
9
503 Views
Last Modified: 2012-08-14
I'm using VB6 against an SQL Server database.

Does anyone know of a format function where I can check for a valid number entered with these requirements:
--Must Be Numeric
--Can have commas
--Cannot have decimals

We need this since we are going to take this number and insert it into a sql server table.

I tried format (txt1,"Currency") but this allows for decimals.

Thanks in Advance

John
0
Comment
Question by:jtrapat1
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 1

Expert Comment

by:carpediem
Comment Utility
You can use user-defined format types instead of pre-built ones like "Currency"

Format(txt1,"##,###")

or use a masked edit control to control what is entered.  Again, you would have to enter a user-defined format as the mask.
0
 
LVL 4

Accepted Solution

by:
trkcorp earned 50 total points
Comment Utility
I am not sure I understand your question. Formatting a number does not "check" anything.
But for what its worth, you might run this:

Dim X As Single
X = 1234.56
MsgBox Format$(X, "#####0") '= 1235
MsgBox Format$(X, "###,##0") '= 1,235
MsgBox Format$(X, "#####0.00") '= 1234.56
MsgBox Format$(X, "###,##0.00") '= 1,234.56
MsgBox Format$(X, "###,##0.0") ' = 1,234.6
MsgBox Format$(Int(X), "###,##0") '= 1,234

If you want to suppress decimals then do so in a keypress event:
Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 46 Then KeyAscii = 0
End Sub

0
 
LVL 1

Expert Comment

by:toys032498
Comment Utility
'If number is numeric then it can only have numbers, decimals, and commas in it.  So all we have to do is determine if its numeric then format it to remove decimals

Private strMyNumber As String

strMyNumber = "100,000.00"

strMyNumber = FormatMyNumber(strMyNumber)

'***********************************
Private Function FormatMyNumber(ByVal strMyNumber As String) As String

    If IsNumeric(strMyNumber) = True Then
        strMyNumber = Format(strMyNumber, "#,###")
    End If
   
End Function
'***********************************

OR

'***********************************
Private Function FormatMyNumber2(ByVal strMyNumber As String) As String
   
    Const DECIMAL_VALUE = "."
   
    If IsNumeric(strMyNumber) = True Then
        strMyNumber = Left(strMyNumber, InStr(1, strMyNumber, DECIMAL_VALUE) - 1)
       
    End If
   
End Function
0
 
LVL 1

Expert Comment

by:toys032498
Comment Utility
Remember that you have to convert Currency and or Decimal/Float data type to string in order for my functions to work, then convert back to Currency and or Decimal/Float when inserting into SQL.  This is done by using the CDbl or CCur or CDec should work just fine.

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Expert Comment

by:RichW
Comment Utility
I believe this validation fits the criteria.

--Must Be Numeric
--Can have commas
--Cannot have decimals


Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
    Exit Sub
Else
    If KeyAscii = 44 Then
       KeyAscii = 44
       Exit Sub
    End If
    If KeyAscii = 46 Then KeyAscii = 0
    If KeyAscii < 48 Then KeyAscii = 0
    If KeyAscii > 57 Then
       
        KeyAscii = 0
    End If
End If
End Sub

RichW
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
RichW has the right idea...
'This allows only 1234567890, and Backspace
Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 OR KeyAscii = 44 Then Exit Sub 'BS/comma OK
If KeyAscii < 48 OR KeyAscii > 57 Then KeyAscii = 0 'Only numerics OK
End Sub
0
 
LVL 10

Expert Comment

by:smegghead
Comment Utility
x="123,123.45"
if format$(x)=clng(format$(x)) then msgbox "It's valid"
0
 
LVL 2

Expert Comment

by:corvanderlinden
Comment Utility
Try this (for different formats, adjust for your own purpose)

'************************************************************************************
' Purpose   :   validate a textbox's text
'               does full validation if the argument KeyLevel is False
'
' Accepts   :   ctrl        : Control containing Text to filter
'               Valuetype   : one of VbVarType types
'               KeyLevel    : if True only valid chars are checked. Use this in a
'                             change event
'                             if False full validation. Use this in a Validate event
' Returns   :   retval      : True if the current value is correct
'************************************************************************************
'
Function Validate(ctrl As Control, Valuetype As VbVarType,
                  Optional ByVal KeyLevel As Boolean) As Boolean
   
    Dim Text As String
    Dim i As Integer
    Dim validTbl As String
    Dim value As Variant
   
    ' determine valid chars
    Select Case Valuetype
        Case vbInteger, vbLong
            validTbl = "-0123456789"
        Case vbSingle, vbDouble, vbCurrency
            validTbl = "-0123456789.,"
        Case vbDate
            validTbl = "0123456789."
    End Select
   
    Text = ctrl.Text
   
    ' check that all chars are valid
    For i = 1 To Len(Text)
        If Len(validTbl) > 0 And _
           InStr(1, validTbl, Mid$(Text, i, 1), vbTextCompare) = 0 Then
            ' this char isn't in the list of valid ones
            Exit Function
        End If
    Next
   
    ' check that the sign symbol and the
    ' decimal separator are OK
    Select Case Valuetype
        Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
            ' the sign symbol can only be the first char
            If InStrRev(Text, "-") > 1 Then Exit Function
            ' there can't be two or more decimal symbols
            If Text Like "*,*,*" Then Exit Function
        Case vbDate
            ' there can't be more than two date separators
            If Text Like "*.*.*.*" Then Exit Function
            ' there can't be two consecutive date separators
            If Text Like "*..*" Then Exit Function
            ' the first char can't be a date separator
            If Text Like ".*" Then Exit Function
    End Select
   
   
    ' this is the end of tests that can be
    ' applied when the value isn't final
    If KeyLevel Then
        Validate = True
        Exit Function
    End If
   
    ' check if required field
    If Len(Text) = 0 Then
        ' return True
        Validate = True
        Exit Function
    End If
       
   
    ' try an assignment to a variable of the proper type
    On Error Resume Next
   
    Select Case Valuetype
        Case vbInteger
            value = CInt(Text)
        Case vbLong
            value = CLng(Text)
        Case vbSingle
            value = CSng(Text)
        Case vbDouble
            value = CDbl(Text)
        Case vbCurrency
            value = CCur(Text)
        Case vbDate
            value = CDate(Text)
        Case Else
            ' no further validation is required
            value = Text
    End Select
   
    ' if value is still empty, exit
    If IsEmpty(value) Then Exit Function
   
    If Valuetype = vbDate Then
        If Format(value, "dd.mm.yyyy") <> Text And _
           Format(value, "dd.mm.yy") <> Text Then
            Exit Function
        End If
    End If
   
    ' if all tests pass, return true
    Validate = True
   
End Function
0
 
LVL 4

Expert Comment

by:RichW
Comment Utility
MSDN
Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
-----
You can use the added test to make my code block test for the value against the range of the Currency data type:

Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
   Exit Sub
Else
   If KeyAscii = 44 Then
      KeyAscii = 44
      Exit Sub
   End If
   If KeyAscii = 46 Then KeyAscii = 0
   If KeyAscii < 48 Then KeyAscii = 0
   If KeyAscii > 57 Then
       
       KeyAscii = 0
   End If
End If

 ' This test checks for the Currency data type range
If Val(Text1) > 922337203685478# Then
    MsgBox "Value is too high." 'Replace with what you want
End If
End Sub

This fits your criteria.
RichW
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now