IsNumeric how to retain decimals when exporting to database?

jptaz

New Member
Joined
May 1, 2020
Messages
46
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I have this function to sort numerical values from text in my userform. This gives me the good format in my database. It works well, but when addind to the database, it doesn't retain any decimals...

Does anybody know why and how to fix it?

VBA Code:
Function GetDataType(ByVal Text As String) As Variant
    If IsNumeric(Text) Then
            GetDataType = Val(Text)
      Else
            GetDataType = Text
      End If
End Function

For ind = 1 To 611
cStart.Offset(TargetRow, 2 + ind - 1).Value = GetDataType(Controls("Reg" & ind).Value)
Next ind

VBA Code:
' When we click the 'continue' button
Private Sub CommandButton1_Click()
  Dim Sht As Worksheet, cStart As Range
  Dim LasteRow As Long
  Dim TargetRow As Long 'variable for position control
  Dim FullName As String 'full name
  Dim UserMessage As String 'variable to configure user message at the end
  Dim ind As Long
  Dim Dossier As String
  
  
  ' Désactiver les évènements et le calcul auto
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  '
  Dossier = Reg5
  FullName = Reg4 & " " & Reg3 'concatenate first and surname for use in code below
  ' Define the work sheet
  Set Sht = ThisWorkbook.Sheets("Data")
  Set cStart = Sht.Range("Data_Start")
  ' The last row
  LasteRow = Sht.ListObjects("Tableau1").ListRows.Count
  'begin check if in 'edit' or 'add new' mode
  If Sheets("Engine").Range("B4").Value = "NEW" Then 'in 'new' mode
    'begin validation check 'check if name already exists
    If Application.WorksheetFunction.CountIf(Sht.Range("H8:d" & LasteRow), Dossier) > 0 Then
      MsgBox "Le numéro de dossier existe déjà", 0, "Check"
      Exit Sub 'notify user and exit the routine
    End If
    'end validation check
    TargetRow = Sht.ListObjects("Tableau1").Range(LasteRow, 1).End(xlUp).Row - 7 + 1
    UserMessage = " a été ajouté à la base de données" 'configure user message for add new entry
  Else 'in 'edit' mode
    TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
    UserMessage = " a été modifié" 'configure user message for edit entry
  End If
  '''BEGIN INPUT DATA INTO DATABASE'''
  cStart.Offset(TargetRow, 0).Value = TargetRow 'ref
  cStart.Offset(TargetRow, 1).Value = VBA.UCase(Reg4) & " " & Reg3 'Txt_First 'full name 'concatenate
  
   For ind = 1 To 611
  cStart.Offset(TargetRow, 2 + ind - 1).Value = GetDataType(Controls("Reg" & ind).Value)
  Next ind

' Réactiver les évènements et le calcul auto
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  '''END INPUT DATA INTO DATABASE'''
  Unload Data_UF 'close the userform
  MsgBox FullName & UserMessage, 0, "Complété" 'display message box (configured according to mode)



End Sub

thank you
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Update

When I take a more in-depth look to my problem here's what I discover :

If I write a number with a comma (3,7) it will forget every decimals when added to the database (result = 3)
If I write it with a dot (3.7), the dot will change to a comma and then if I edit it the comma will be erased (result = 3)

Is there anything I can try?

thanks
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top