如何从另一个记录日期更新日期字段 [英] How to update a date field from another records date

问题描述

我有一个Sql更新语句，我正在使用它来更新用户从列表框lstDelFrom中选择的记录中的数据。


这适用于所有文本字段&安培;更新很好。一旦我添加了一个Date字段的字段，我就无法使更新生效。
ie）这一行[代表]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃，" &安培; _

有人可以帮助我使用正确的语法。


如果没有，我还需要考虑该字段可能为空在日期字段中输入。


这是我到目前为止的代码。我想要更新的字段称为[委托]。[on]

它被定义为委托表中的短日期。

sqlupdate =" ;更新授权 &安培; _

" set [delegation]。[seg] =''" &安培; Nz（rst.Fields（1）.Value，""）& "，" &安培; _

" [delegation]。[other] =''" &安培; Nz（rst.Fields（2）.Value，""）& "，" &安培; _

" [delegation]。[name] =''" &安培; Nz（rst.Fields（3）.Value，""）& "，" &安培; _

" [delegation]。[title] =''" &安培; Nz（rst.Fields（4）.Value，""）& "，" &安培; _

" [delegation]。[section] =''" &安培; Nz（rst.Fields（5）.Value，""）& "，" &安培; _

" [delegation]。[keywords] =''" &安培; Nz（rst.Fields（6）.Value，""）& "，" &安培; _

[代表团]。[已采用] =''" &安培; Nz（rst.Fields（8）.Value，""）& "，" &安培; _

" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃，" &安培; _

" where（[delegation]。[id] =''"& [Forms]！[frmdelnewproperties]！[id]&"''）"


MsgBox从代表团复制详细信息 - " &安培; lstDelFrom.Column（0）& " " &安培; lstDelFrom.Column（3）& " - " &安培; lstDelFrom.Column（4）

wrk.BeginTrans

dbs.Execute sqlupdate

wrk.CommitTrans


感谢任何人都可以给我的帮助。

I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom.

This is working for all the text fields & updates fine. Once I add in one of the fields that is a Date field I cannot get the update to work.
ie) this line "[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#," & _
Could someone assist me with the correct syntax to use.

I also need to consider that the field may be null if nothing was entered in against the date field.

Here is my code so far. The field I am trying to update is called [delegation].[on]
It is defined as a short date in the delegation table.

sqlupdate = "update delegation " & _
"set [delegation].[seg] = ''" & Nz(rst.Fields(1).Value, " ") & "''," & _
"[delegation].[other] = ''" & Nz(rst.Fields(2).Value, " ") & "''," & _
"[delegation].[name] = ''" & Nz(rst.Fields(3).Value, " ") & "''," & _
"[delegation].[title] = ''" & Nz(rst.Fields(4).Value, " ") & "''," & _
"[delegation].[section] = ''" & Nz(rst.Fields(5).Value, " ") & "''," & _
"[delegation].[keywords] = ''" & Nz(rst.Fields(6).Value, " ") & "''," & _
"[delegation].[adopted] = ''" & Nz(rst.Fields(8).Value, " ") & "''," & _
"[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#," & _
"where ([delegation].[id] = ''" & [Forms]![frmdelnewproperties]![id] & "'')"

MsgBox "Copying details from Delegation - " & lstDelFrom.Column(0) & " " & lstDelFrom.Column(3) & "-" & lstDelFrom.Column(4)

wrk.BeginTrans
dbs.Execute sqlupdate
wrk.CommitTrans

Thanks for any assistance that anyone may be able to give me.

推荐答案

我有一个Sql更新语句，我正在使用它来更新用户从列表框lstDelFrom中选择的记录中的数据。


这适用于所有文本字段和放大器;更新很好。一旦我添加了一个Date字段的字段，我就无法使更新生效。
ie）这一行[代表]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃，" &安培; _

有人可以帮助我使用正确的语法。


如果没有，我还需要考虑该字段可能为空在日期字段中输入。


这是我到目前为止的代码。我想要更新的字段称为[委托]。[on]

它被定义为委托表中的短日期。

sqlupdate =" ;更新授权 &安培; _

" set [delegation]。[seg] =''" &安培; Nz（rst.Fields（1）.Value，""）& "，" &安培; _

" [delegation]。[other] =''" &安培; Nz（rst.Fields（2）.Value，""）& "，" &安培; _

" [delegation]。[name] =''" &安培; Nz（rst.Fields（3）.Value，""）& "，" &安培; _

" [delegation]。[title] =''" &安培; Nz（rst.Fields（4）.Value，""）& "，" &安培; _

" [delegation]。[section] =''" &安培; Nz（rst.Fields（5）.Value，""）& "，" &安培; _

" [delegation]。[keywords] =''" &安培; Nz（rst.Fields（6）.Value，""）& "，" &安培; _

[代表团]。[已采用] =''" &安培; Nz（rst.Fields（8）.Value，""）& "，" &安培; _

" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃，" &安培; _

" where（[delegation]。[id] =''"& [Forms]！[frmdelnewproperties]！[id]&"''）"


MsgBox从代表团复制详细信息 - " &安培; lstDelFrom.Column（0）& " " &安培; lstDelFrom.Column（3）& " - " &安培; lstDelFrom.Column（4）

wrk.BeginTrans

dbs.Execute sqlupdate

wrk.CommitTrans


感谢任何人都可以给我的帮助。

I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom.

This is working for all the text fields & updates fine. Once I add in one of the fields that is a Date field I cannot get the update to work.
ie) this line "[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#," & _
Could someone assist me with the correct syntax to use.

I also need to consider that the field may be null if nothing was entered in against the date field.

Here is my code so far. The field I am trying to update is called [delegation].[on]
It is defined as a short date in the delegation table.

sqlupdate = "update delegation " & _
"set [delegation].[seg] = ''" & Nz(rst.Fields(1).Value, " ") & "''," & _
"[delegation].[other] = ''" & Nz(rst.Fields(2).Value, " ") & "''," & _
"[delegation].[name] = ''" & Nz(rst.Fields(3).Value, " ") & "''," & _
"[delegation].[title] = ''" & Nz(rst.Fields(4).Value, " ") & "''," & _
"[delegation].[section] = ''" & Nz(rst.Fields(5).Value, " ") & "''," & _
"[delegation].[keywords] = ''" & Nz(rst.Fields(6).Value, " ") & "''," & _
"[delegation].[adopted] = ''" & Nz(rst.Fields(8).Value, " ") & "''," & _
"[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#," & _
"where ([delegation].[id] = ''" & [Forms]![frmdelnewproperties]![id] & "'')"

MsgBox "Copying details from Delegation - " & lstDelFrom.Column(0) & " " & lstDelFrom.Column(3) & "-" & lstDelFrom.Column(4)

wrk.BeginTrans
dbs.Execute sqlupdate
wrk.CommitTrans

Thanks for any assistance that anyone may be able to give me.

日期数据类型是数字，您要将空值转换为 " （空格），这是文本。因此，尝试将空值转换为零并查看它是否有帮助：


" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，0）& "＃，" &安培; _

A date data type is numeric, and you are converting the nulls to " " (spaces), which is text. So try converting the nulls to zero and see if it helps:

"[delegation].[on] = #" & Nz(rst.Fields(9).Value, 0) & "#," & _

日期数据类型是数字，您要将空值转换为 " （空格），这是文本。因此，尝试将空值转换为零并查看它是否有帮助：


" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，0）& "＃，" &安培; _

A date data type is numeric, and you are converting the nulls to " " (spaces), which is text. So try converting the nulls to zero and see if it helps:

"[delegation].[on] = #" & Nz(rst.Fields(9).Value, 0) & "#," & _

非常感谢您的建议，我确实试过这个，但不幸的是我得到了一个 SYNATX ERROR IN UPDATE STATEMENT


我也尝试过以下方法：

" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃" &安培; _


如果该字段不是我复制的空字段，则上述行有效，如果该字段为空则不起作用。


所以我试图改变我的方式并使用IIF语句但没有成功 - 最后还出现语法错误

" [delegation]。 [on] = IIf（IsNull（rst.Fields（9）.Value），"，＃"& rst.Fields（9）.Value&"＃）" &安培; _


如果我能得到一个适用于空字段的工作，我认为我最接近这个但不知道如何处理空值

Many thanks for your suggestion, I did try this but unfortunately I get a ''SYNTAX ERROR IN UPDATE STATEMENT''

I have also tried the following:

"[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#" & _

The above line works if the field is not null that I am copying across, if it is null it does not work.

So I tried to change the way I was doing it and used an IIF statement but had no success - ended up with an Syntax error as well

"[delegation].[on] = IIf(IsNull(rst.Fields(9).Value), "", #" & rst.Fields(9).Value & "#)" & _

If I can get the one working that works for null fields I think I am the closest with this but do not know how to deal with the null values

非常感谢您的建议，我确实尝试了这一点，但不幸的是我得到了一个 SYNATX ERROR IN UPDATE STATEMENT ''


我也尝试过以下方法：

" [delegation]。[on] =＃" &安培; Nz（rst.Fields（9）.Value，""）& "＃" &安培; _


如果该字段不是我复制的空字段，则上述行有效，如果该字段为空则不起作用。


所以我试图改变我的方式并使用IIF语句但没有成功 - 最后还出现语法错误

" [delegation]。 [on] = IIf（IsNull（rst.Fields（9）.Value），"，＃"& rst.Fields（9）.Value&"＃）" &安培; _


如果我能得到一个适用于空字段的工作，我认为我最接近这个但不知道如何处理空值

Many thanks for your suggestion, I did try this but unfortunately I get a ''SYNTAX ERROR IN UPDATE STATEMENT''

I have also tried the following:

"[delegation].[on] = #" & Nz(rst.Fields(9).Value, "") & "#" & _

The above line works if the field is not null that I am copying across, if it is null it does not work.

So I tried to change the way I was doing it and used an IIF statement but had no success - ended up with an Syntax error as well

"[delegation].[on] = IIf(IsNull(rst.Fields(9).Value), "", #" & rst.Fields(9).Value & "#)" & _

If I can get the one working that works for null fields I think I am the closest with this but do not know how to deal with the null values

您的日期字段是在表中定义为日期/时间数据类型还是定义为文本数据类型，并格式化为日期字符串？区别很重要，因为有日期函数/表达式使用格式化日期字符串，还有其他函数/表达式使用日期作为日期为数字的日期/时间数据类型..

Is your date field defined as a date/time data type in your table or is defined as a text data type, and formatted as a date string? The distinction is important because there are date functions/expressions that work with formatted date strings and there are other functions/expressions that work with the date as a date/time data type for which the date is numeric..

这篇关于如何从另一个记录日期更新日期字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！