IsNumeric how to retain decimals when exporting to database?

[jptaz]

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?

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

' 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

 

[jptaz]

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