CODEWITHSUNDEEP
excelvba
Renier Wessels
Renier Wessels

Reputation: 151

VBA Format function into Textbox not working

Good day

I am trying to build an add-in that can handle values from and into a MySQL database the same way regardless of the Windows Region settings.

When the region settings has set the Short Date as "dd MMM yyyy" I have no idea how to change that format into "dd/mm/yyyy"

When I use:

Format(Date, "dd/mm/yyyy")

It translates to: 05 06 2020

Which errors out on the database. I have tried CDate and DateSerial, all to no avail.

I essentially set my Global Const variable called dateFormat as:

Public Const dateFormat = "dd/mm/yyyy"

And then I use the following to try and write to values into the Textboxes I use:

txtCreated.Value = Format(Date, dateFormat)

But the Textbox then contains the value as: 05 06 2020

When writing this value into my db, I use:

rs!ncreate = Format(txtCreated.Value, dateFormat)

And then get a date formatting error saying the format "dd mm yyyy" is not accepted even though I am trying to send it in format "dd/mm/yyy"

Any ideas?

Upvotes: 0

Views: 492

Answers (2)

Renier Wessels
Renier Wessels

Reputation: 151

Based on Samuel's suggestions in the comments on my post, I have built a custom function to convert dates for me now:

Function FormatDate(DateIn As Variant) As String

    ' Do nothing if the value is not a date.
    If Not IsDate(DateIn) Then Exit Function

    ' Format the date value as "dd/mm/yyyy"
    FormatDate = Format(DateIn, "dd") & "/" & Format(DateIn, "mm") & "/" & Format(DateIn, "yyyy")

End Function

Using the Format() function allows the output to show leading zeros, for example; the input "1 May 2020" would return #01/05/2020# rather than #1/5/2020#.

Upvotes: 1

Samuel Everson
Samuel Everson

Reputation: 2102

To use this Microsoft doc as a reference, this would output your date as desired:

Public Function MakeEuroDate(DateToFormat As Variant) As String 
 
    ' Do nothing if the value is not a date. 
    If Not IsDate(DateToFormat) Then Exit Function 

    ' Format the date value as "dd/mm/yyyy" date formatted string expression.
    MakeEuroDate = "#" & Day(DateToFormat) & "/" & Month(DateToFormat) & "/" & Year(DateToFormat) & "#"

End Function

Note: leading zeros are removed in this output so the first day of a month would be 1 not 01.

Upvotes: 2

Related Questions