Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format Numeric Field To Show Commas

Posted on 2002-05-15
9
Medium Priority
?
536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 1

Expert Comment

by:carpediem
ID: 7012065
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 200 total points
ID: 7012069
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
ID: 7012075
'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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Expert Comment

by:toys032498
ID: 7012082
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
 
LVL 4

Expert Comment

by:RichW
ID: 7012104
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
ID: 7012269
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
ID: 7012327
x="123,123.45"
if format$(x)=clng(format$(x)) then msgbox "It's valid"
0
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 7013083
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
ID: 7013499
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

688 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