将 Access 表单上的 SQL 时间字段显示为 Access medium time hh:mm am/pm [英] Display SQL time field on Access form as Access medium time hh:mm am/pm

问题描述

我正在使用链接到 SQL Server 2008 R2 的 Access 2010.我的问题 - 如何在 Access 表单上将时间显示为 hh:mm am/pm 而不是 SQL Server 的 time(7) 格式的 hh:mm:ss.nnnnnnn?

I am using Access 2010 linked to SQL Server 2008 R2. My problem - how do I display time on an Access form as hh:mm am/pm instead of SQL Server's time(7) format of hh:mm:ss.nnnnnnn?

更复杂的是，我确实有一个半小时时间范围内的所有时间的查找表?前任.在 SQL 中，查找字段显示 07:30:00.0000000 - 我想从 Access 表单中显示为上午 7:30 的下拉列表中选择一个时间，然后在 Access 表单上显示为上午 7:30.如果我选择它，我希望它以 07:30:00:0000000 的形式存储在 SQL Server 中(因为我别无选择).

To complicate things, I do have a look-up table with all the times in half hour time frames? Ex. in SQL, the lookup field says 07:30:00.0000000 - I want to choose a time from the dropdown in my Access form that says 7:30 am and then display it on the Access form as 7:30 am. If I choose it, I want it to store in SQL Server as 07:30:00:0000000 (because I have no other choice).

我在这方面找不到任何东西.

I can't find anything on this.

推荐答案

Access 没有 Time 数据类型，所以当它链接到 SQL Server timecolumn 它将该列映射为 Text.这就是您尝试应用于链接表值的任何 Date/Time 格式说明符无效的原因:就 Access 而言，它是一个字符串.

Access doesn't have a Time data type, so when it links to a SQL Server time column it maps that column as Text. That's why any Date/Time Format specifier you try to apply to the linked table value has no effect: as far as Access is concerned it's a string.

如果我们有一个 SQL Server 表 [约会]:

So if we have a SQL Server table [Appointments]:

我们在 Access 中创建了一个链接表

and we create a Linked Table in Access we get

如果我们为该链接表创建一个 Access 表单并使用普通的旧绑定控件，我们会得到这个

If we create an Access form for that linked table and use plain old bound controls we get this

[apptStart] 的文本框确实有效，但看起来不太好.解决方法是将该文本框保留在表单上，​​但将其隐藏(将其 .Visible 属性设置为 No)，然后将另一个未绑定的文本框添加到表单中.(在这种情况下，我称之为 [txtStartTime].)将新文本框的 .Format 设置为 Medium Time 并根据需要调整任何其他格式(例如，我设置文本对齐到左).

The text box for [apptStart] does work, but it doesn't look so great. The workaround is to leave that text box on the form, but hide it (set its .Visible property to No) and then add another unbound text box to the form. (In this case I called it [txtStartTime].) Set the .Format of the new text box to Medium Time and adjust any other formatting as required (e.g., I set Text Align to Left).

在 On Current 表单事件中，计算与 SQL Server 表中的开始时间对应的 Access Date/time 值，并将该值填充到 [txtStartTime] 文本中盒子.现在我们得到了一些看起来更好的东西:

In the On Current form event, calculate the Access Date/time value corresponding to the start time in SQL Server table and stuff that value into the [txtStartTime] text box. Now we get something that looks a little better:

如果用户想要编辑该值怎么办?在 [txtStartTime] 文本框的 After Update 事件中，我们创建相应的 SQL Server time 字符串并将该值填充到(不可见，绑定)[apptStart] 文本框中.保存记录时，新的时间值会在 SQL Server 表中更新.

Now what if the user wants to edit that value? In the After Update event for the [txtStartTime] text box we create the corresponding SQL Server time string and stuff that value into the (invisible, bound) [apptStart] text box. When the record is saved the new time value is updated in the SQL Server table.

表单后面的代码如下:

Option Compare Database
Option Explicit

Const accessDate0 = "1899-12-30 " ' <- note trailing space
Dim startTimeAsDateTime As Date

Private Sub Form_AfterUpdate()
    Me.Requery
End Sub

Private Sub Form_Current()
    Me.txtStartTime.Value = CDate(accessDate0 & Left(Me.apptStart, 8))
End Sub

Private Sub txtStartTime_AfterUpdate()
    Me.apptStart.Value = Format(startTimeAsDateTime, "hh:nn:ss")
End Sub

Private Sub txtStartTime_BeforeUpdate(Cancel As Integer)
    On Error GoTo txtStartTime_BeforeUpdate_Error
    startTimeAsDateTime = CDate(accessDate0 & Me.txtStartTime.Value)
    Exit Sub

txtStartTime_BeforeUpdate_Error:
        MsgBox "Start Time appears to be invalid."
        Cancel = True
End Sub

这篇关于将 Access 表单上的 SQL 时间字段显示为 Access medium time hh:mm am/pm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！