How to update a date field from another records date

[tonialbrown]

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]![frmdelnewproper ties]![id] & "')"

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

wrk.BeginTrans
dbs.Execute sqlupdate
wrk.CommitTrans

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

[puppydogbuddy]

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]![frmdelnewproper ties]![id] & "')"

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

wrk.BeginTrans
dbs.Execute sqlupdate
wrk.CommitTrans

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

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) & "#," & _

[tonialbrown]

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) & "#," & _

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).V alue & "#)" & _

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

[puppydogbuddy]

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).V alue & "#)" & _

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..

[puppydogbuddy]

P.S. If your field is a date/time data type, try the expression this way:

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

[tonialbrown]

P.S. If your field is a date/time data type, try the expression this way:

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

The date is formatted as a Date/Time field in the table (short date).

I tried what you suggested but get a "invalid use of null" when the field I am trying to copy is null.

When I copy a field that has a date in it now - say 5/05/2007 when it places the data in the new field it now becomes the date 30/12/1899

Previously when I used

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

This placed the correct date in but did not work for nulls.

[puppydogbuddy]

The date is formatted as a Date/Time field in the table (short date).

I tried what you suggested but get a "invalid use of null" when the field I am trying to copy is null.

When I copy a field that has a date in it now - say 5/05/2007 when it places the data in the new field it now becomes the date 30/12/1899

Previously when I used

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

This placed the correct date in but did not work for nulls.

Ok, if it works except for the nulls using your syntax, try it this way:

"[delegation].[on] = #" & IIf Not IsNull(rst.Fiel ds(9),rst.Field s(9).Value,"") & "#" & _

[tonialbrown]

Ok, if it works except for the nulls using your syntax, try it this way:

"[delegation].[on] = #" & IIf Not IsNull(rst.Fiel ds(9),rst.Field s(9).Value,"") & "#" & _

For some reason everytime I add this line it changes my code from below:

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] = #" & IIf Not IsNull(rst.Fiel ds(9),rst.Field s(9).Value,"") & "#" & _
"where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"

to

sqlupdate = Not "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] = #" & IIf _

when I move off the line. At first I thought I was typing it incorrectly but after 3 attempts realised Access was rearranging what I was typing.

So I changed to the line to this

"[delegation].[on] = #" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "# " & _

but now I get the error message

Syntax error in date in query expression '# #'

It seems it puts the hashes in the sql update query when there is null values.

It still works when I have a valid date.

[puppydogbuddy]

For some reason everytime I add this line it changes my code from below:

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] = #" & IIf Not IsNull(rst.Fiel ds(9),rst.Field s(9).Value,"") & "#" & _
"where ([delegation].[id] = '" & [Forms]![frmdelnewproper ties]![id] & "')"

to

sqlupdate = Not "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] = #" & IIf _

when I move off the line. At first I thought I was typing it incorrectly but after 3 attempts realised Access was rearranging what I was typing.

So I changed to the line to this

"[delegation].[on] = #" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "# " & _

but now I get the error message

Syntax error in date in query expression '# #'

It seems it puts the hashes in the sql update query when there is null values.

It still works when I have a valid date.

Ok, it looks like I forgot a space. Try changing this:
"[delegation].[on] = #" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "# " & _

to this:
"[delegation].[on] = # " & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & " # " & _


If the above does not work, place a control break after the last sql line above and type ? sqlupdate in the immmediate window and then print out the full sqlupdate statement as it has been interpreted by MS Access.

[tonialbrown]

Ok, it looks like I forgot a space. Try changing this:
"[delegation].[on] = #" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "# " & _

to this:
"[delegation].[on] = # " & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & " # " & _


If the above does not work, place a control break after the last sql line above and type ? sqlupdate in the immmediate window and then print out the full sqlupdate statement as it has been interpreted by MS Access.

After changing the line - the error is still 'Syntax error in date in query expression '# #'

The line from the immediate window (when a NULL value in [delegation].[on]) is:

update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Pam Barber',[delegation].[title] = 'Supply Co ordinator',[delegation].[section] = 'Finance',[delegation].[keywords] = 'Support Services
Support Services',[delegation].[adopted] = ' ',[delegation].[on] = # # where ([delegation].[id] = 'DEB0009B')

The line from the immediate window ( when the [on] field is not a null) is:
NOTE - the following update works

update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Kris Palman',[delegation].[title] = 'Administration Team Leader',[delegation].[section] = 'Town Planning Services',[delegation].[keywords] = 'External Services',[delegation].[adopted] = 'end of Dec',[delegation].[on] = # 15/03/2006 # where ([delegation].[id] = 'DAA0017A')

So the problem appears to be the # # when it is a null value but I don't seem to be able to get the syntax right for the iif when I include the # # within the iif as opposed to around the whole field.

Thanks again for your patience and help

[puppydogbuddy]

After changing the line - the error is still 'Syntax error in date in query expression '# #'

The line from the immediate window (when a NULL value in [delegation].[on]) is:

update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Pam Barber',[delegation].[title] = 'Supply Co ordinator',[delegation].[section] = 'Finance',[delegation].[keywords] = 'Support Services
Support Services',[delegation].[adopted] = ' ',[delegation].[on] = # # where ([delegation].[id] = 'DEB0009B')

The line from the immediate window ( when the [on] field is not a null) is:
NOTE - the following update works

update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Kris Palman',[delegation].[title] = 'Administration Team Leader',[delegation].[section] = 'Town Planning Services',[delegation].[keywords] = 'External Services',[delegation].[adopted] = 'end of Dec',[delegation].[on] = # 15/03/2006 # where ([delegation].[id] = 'DAA0017A')

So the problem appears to be the # # when it is a null value but I don't seem to be able to get the syntax right for the iif when I include the # # within the iif as opposed to around the whole field.

Thanks again for your patience and help

Try this:
_______________ _______________ _______________ _______________ _____
Dim strDateCriteria As String

strDateCriteria = "#" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "#"
"[delegation].[on] =" & strDateCriteria & _

or this way:

strDateCriteria = "#" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "#"
"[delegation].[on] ='" & strDateCriteria & "'" & _

[tonialbrown]

Try this:
_______________ _______________ _______________ _______________ _____
Dim strDateCriteria As String

strDateCriteria = "#" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "#"
"[delegation].[on] =" & strDateCriteria & _

or this way:

strDateCriteria = "#" & IIf(Not IsNull(rst.Fiel ds(9)), rst.Fields(9).V alue, "") & "#"
"[delegation].[on] ='" & strDateCriteria & "'" & _

Thanks I tried it.

The first way does not work for the nulls (only for dates) and comes up the sql date syntax error as before (basically same as before).

The Sql from the immediate window ends up being
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Kay Kell',[delegation].[title] = 'Service Manager (Child, Family & Youth)',[delegation].[section] = 'Community Services',[delegation].[keywords] = 'City Planning',[delegation].[adopted] = ' ',[delegation].[on] =##where ([delegation].[id] = 'DCA0005B')


the second way works for nulls but NOT for the dates any more. It does not give an error but does not copy the date in.

The sql for nulls is:
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Kay Kell',[delegation].[title] = 'Service Manager (Child, Family & Youth)',[delegation].[section] = 'Community Services',[delegation].[keywords] = 'City Planning',[delegation].[adopted] = ' ',[delegation].[on] ='##'where ([delegation].[id] = 'DCA0006B')

The sql for a date is:
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Jo Lewin',[delegation].[title] = 'Executive Manager - Community Partnerships',[delegation].[section] = 'Community Services',[delegation].[keywords] = 'City Planning',[delegation].[adopted] = ' ',[delegation].[on] ='#5/05/2007#'where ([delegation].[id] = 'DCA0006B')

Any thing else you can think of???

Thanks

Tonia

[tonialbrown]

I had a play around with what you gave me and almost got it to work!

strDateCriteria = IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'##'")
"[delegation].[on] = " & strDateCriteria & _

then further streamlined by getting rid of the strDateCriteria field to this:

"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'##'") & _
HOPE IT IS OK TO DO THIS???

But before I get too excited, my problem is that the date is being reversed in some instances when it is copied in ie) 5/03/2007 stored in original field becomes 3/05/2007 in copied field. This may have happened before - I am not certain as I have changed it so many times.

When I check the ?sqlupdate it shows that it should be copied in correctly ie) SQL shows

In immediate window:

on5/03/2007
lastrev5/03/2007
nextrev5/03/2007
deleffdate6/03/2007
delenddate7/03/2007

?sqlupdate
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Pam Barber',[delegation].[title] = 'Supply Co ordinator',[delegation].[section] = 'Finance',[delegation].[keywords] = 'Support Services
Support Services',[delegation].[adopted] = ' ',[delegation].[on] = #5/03/2007#,[delegation].[lastrev] = #5/03/2007#,[delegation].[nextrev] = #5/03/2007#,[delegation].[deleffdate] = #6/03/2007#,[delegation].[delenddate] = #7/03/2007#where ([delegation].[id] = 'DCB0008B')

Also why is it that I can copy in a string field into a date field???

Thanks

Tonia

[puppydogbuddy]

I had a play around with what you gave me and almost got it to work!

strDateCriteria = IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'##'")
"[delegation].[on] = " & strDateCriteria & _

then further streamlined by getting rid of the strDateCriteria field to this:

"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'##'") & _
HOPE IT IS OK TO DO THIS???

But before I get too excited, my problem is that the date is being reversed in some instances when it is copied in ie) 5/03/2007 stored in original field becomes 3/05/2007 in copied field. This may have happened before - I am not certain as I have changed it so many times.

When I check the ?sqlupdate it shows that it should be copied in correctly ie) SQL shows

In immediate window:

on5/03/2007
lastrev5/03/2007
nextrev5/03/2007
deleffdate6/03/2007
delenddate7/03/2007

?sqlupdate
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Pam Barber',[delegation].[title] = 'Supply Co ordinator',[delegation].[section] = 'Finance',[delegation].[keywords] = 'Support Services
Support Services',[delegation].[adopted] = ' ',[delegation].[on] = #5/03/2007#,[delegation].[lastrev] = #5/03/2007#,[delegation].[nextrev] = #5/03/2007#,[delegation].[deleffdate] = #6/03/2007#,[delegation].[delenddate] = #7/03/2007#where ([delegation].[id] = 'DCB0008B')

Also why is it that I can copy in a string field into a date field???

Thanks

Tonia

Hi Tonia,

The placement of the date delimiters for the IIf statement was good thinking on your part. I can't believe I overlooked that. As for whether you can use "'##'": I have not seen that syntax before, but if Access interprets it correctly it should be ok. I think you can accomplish the same thing by using the Ascii value for the # delimiter Chr(35), which is more commonly done. if you care to try, you don't need quotes; syntax would be:
& Chr(35) &

Re: Your reversed date problem, you need to use the format function on your copied field as follows:
Format([yourDatefield], "mm/dd/yyyy")

Other correction needed: You need a space between the # delimiter and the "Where" shown below:
#7/03/2007#where ([delegation].[id] = 'DCB0008B')

Re: Also why is it that I can copy in a string field into a date field???
A date field will accept as input, any string formatted as a date as long as it is a valid date.

[tonialbrown]

Thankyou, thankyou, thankyou....

I changed the line to use the chr(35) and got the same result. Is the below what you meant???

"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'" & Chr(35) & Chr(35) & "'") & "," & _

With the format for the dates, I did the following - just a couple of minor changes & a bit of playing around to get it working successfully in the iif statement.

"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)), "#" & Format(rst.Fiel ds(9).Value, "mm\/dd\/yyyy") & "#", "'" & Chr(35) & Chr(35) & "'") & "," & _
I just want to say I really appreciate you taking the time to work through this problem to help me get a solution and for being so patient and responding to my endless questions so quickly.

I am only half way through this copying process. Next comes page 2 of the form where I have to copy in multiple rows in another table from a recordset. I have this partly working but may need to open a separate post - maybe if you are game you will look out for it!!

Many thanks again.

Tonia