Transfer a date from database to userform without reformating itself to US format

[jptaz]

Hello everyone, I have a code to format a date from my userform to my database like this : yyyy-mm-dd.

Function GetDataTypeDate(ByVal Text As String) As Variant
    If Text Like "####-##-##" And IsDate(Text) Then
            GetDataTypeDate = DateValue(Text)
       Else
            GetDataTypeDate = Text
      End If
End Function

  For ind = 1 To 23
  cStart.Offset(TargetRow, 599 + ind - 1).Value = GetDataTypeDate(Controls("Text_date" & ind).Value)
  Next ind

However, if I want to edit some infos, the dates in my userform are reformated like this dd/mm/yyyy when re-inserted.

Private Sub CommandButton1_Click()

Dim TargetRow As Integer 'variable to save position of this person in database
Dim i As Long

'use Match worksheet function to find position of chosen name
TargetRow = Application.WorksheetFunction.Match(ColumnE_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0)
Sheets("Engine").Range("B5").Value = TargetRow 'save position in the engine, for use later

Unload Find_Entry_UF 'unload the userform to select a name
    
Dim W
Set W = Data_UF
    
'''Begin retrieving data from database'''
With Sheets("Data").Range("Data_Start")

For i = 1 To 597
W.Controls("Reg" & i) = .Offset(TargetRow, 1 + i)

Next i

[U]For i = 1 To 23
W.Controls("Text_date" & i) = .Offset(TargetRow, 598 + i)

Next i[/U]
   

End With


'''End retrieving data from database'''

W.Caption = "Modifier" 'set caption to show that the user is editing
W.Show 'show the user form with the details loaded in

End Sub

Is there any way to retrieve a date from a database and format it to text so that the format stays like this ####-##-##

Thanks for your help

JP

 

[Norie]

Try using the Text property, that will return the values as they are formatted.

W.Controls("Text_date" & i) = .Offset(TargetRow, 598 + i).Text

 

[jptaz]

Try using the Text property, that will return the values as they are formatted.

W.Controls("Text_date" & i) = .Offset(TargetRow, 598 + i).Text

Wow it was so simple. It works perfectly, thank you very much!!