文本框的VBA格式功能不起作用 [英] VBA Format function into Textbox not working

问题描述

美好的一天

我试图构建一个外接程序，无论Windows区域设置如何，都可以以相同的方式处理来自MySQL数据库的值和传入MySQL数据库的值.

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.

当区域设置将短日期"设置为"dd MMM yyyy"时，我不知道如何将格式更改为"dd/mm/yyyy"

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"

当我使用时:

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

它翻译为:2020年5月6日

It translates to: 05 06 2020

哪个错误出在数据库上.我已经尝试了CDate和DateSerial，但无济于事.

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

我基本上将名为dateFormat的Global Const变量设置为:

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)

但是，文本框包含的值如下:2020年5月6日

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)

然后出现日期格式错误，提示即使我尝试以"dd/mm/yyy"格式发送"dd mm yyyy"格式，也不接受

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"

有什么想法吗?

推荐答案

要使用

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

注意:此输出中将删除前导零，因此一个月的第一天为 1 而不是 01 .

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

这篇关于文本框的VBA格式功能不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！