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

jptaz

New Member
Joined
May 1, 2020
Messages
33
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone, I have a code to format a date from my userform to my database like this : yyyy-mm-dd.

VBA Code:
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.

VBA Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Try using the Text property, that will return the values as they are formatted.
VBA Code:
W.Controls("Text_date" & i) = .Offset(TargetRow, 598 + i).Text
 

jptaz

New Member
Joined
May 1, 2020
Messages
33
Office Version
  1. 2010
Platform
  1. Windows
Try using the Text property, that will return the values as they are formatted.
VBA Code:
W.Controls("Text_date" & i) = .Offset(TargetRow, 598 + i).Text
Wow it was so simple. It works perfectly, thank you very much!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,924
Members
417,247
Latest member
Chitaah

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
Top