By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

How to update a date field from another records date

P: 19
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.
Jan 26 '08 #1
Share this Question
Share on Google+
22 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
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) & "#," & _
Jan 27 '08 #2

P: 19
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).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
Jan 27 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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..
Jan 27 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
P.S. If your field is a date/time data type, try the expression this way:

"[delegation].[on] = " & Nz(rst.Fields(9).Value, 0) & "," & _
Jan 27 '08 #5

P: 19
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.
Jan 28 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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.Fields(9),rst.Fields(9).Value,"") & "#" & _
Jan 29 '08 #7

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

"[delegation].[on] = #" & IIf Not IsNull(rst.Fields(9),rst.Fields(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.Fields(9),rst.Fields(9).Value,"") & "#" & _
"where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![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.Fields(9)), rst.Fields(9).Value, "") & "# " & _

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.
Jan 29 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
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.Fields(9),rst.Fields(9).Value,"") & "#" & _
"where ([delegation].[id] = '" & [Forms]![frmdelnewproperties]![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.Fields(9)), rst.Fields(9).Value, "") & "# " & _

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.Fields(9)), rst.Fields(9).Value, "") & "# " & _

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


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.
Jan 29 '08 #9

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

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


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
Jan 30 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
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.Fields(9)), rst.Fields(9).Value, "") & "#"
"[delegation].[on] =" & strDateCriteria & _

or this way:

strDateCriteria = "#" & IIf(Not IsNull(rst.Fields(9)), rst.Fields(9).Value, "") & "#"
"[delegation].[on] ='" & strDateCriteria & "'" & _
Jan 30 '08 #11

P: 19
Try this:
__________________________________________________ _______________
Dim strDateCriteria As String

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

or this way:

strDateCriteria = "#" & IIf(Not IsNull(rst.Fields(9)), rst.Fields(9).Value, "") & "#"
"[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
Jan 31 '08 #12

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

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

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

"[delegation].[on] = " & IIf(Not IsNull(rst.Fields(9)), "#" & rst.Fields(9).Value & "#", "'##'") & _
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
Jan 31 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
I had a play around with what you gave me and almost got it to work!

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

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

"[delegation].[on] = " & IIf(Not IsNull(rst.Fields(9)), "#" & rst.Fields(9).Value & "#", "'##'") & _
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.
Jan 31 '08 #14

P: 19
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.Fields(9)), "#" & rst.Fields(9).Value & "#", "'" & 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.Fields(9)), "#" & Format(rst.Fields(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
Jan 31 '08 #15

puppydogbuddy
Expert 100+
P: 1,923
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.Fields(9)), "#" & rst.Fields(9).Value & "#", "'" & 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.Fields(9)), "#" & Format(rst.Fields(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
Tonia,
Glad you got it resolved.

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

Yes, except that you can use Chr(35) for each/all # delimeters.
Jan 31 '08 #16

Expert 100+
P: 446
Tonia,
Glad you got it resolved.
Hi
I'm really glad this worked for you guys because I can't get this syntax working on my computer. I find this disturbing because I process a lot of dates and thought I knew the business inside out.

I liked you Chr(35) though Jag !

I built a test-bed to check-out this problem of handling Null dates (attached). You will see I have used a DoCmd.RunSQL statement, which compiles every time you take the cursor off the line, so should stop you making silly mistakes.

I have to say that double-quote/single-quote/double-quote causes the remainder of the string to become a comment (turns green)

Neither can I get a date field to accept ## (I've tried all combinations of quotes around it and I've tried Chr(35) & Chr(35))

I can save a Null to a date field but if it comes from an IIf() statement it must be as a "Null" string , which I find curious.

I have a solution that involves evaluating the date and putting the formatted output into a variant type variable, then using the variable in the SQL; all a bit convoluted but it works.

S7
Attached Files
File Type: zip db4.zip (33.9 KB, 98 views)
Jan 31 '08 #17

P: 19
Hi
I'm really glad this worked for you guys because I can't get this syntax working on my computer. I find this disturbing because I process a lot of dates and thought I knew the business inside out.

I liked you Chr(35) though Jag !

I built a test-bed to check-out this problem of handling Null dates (attached). You will see I have used a DoCmd.RunSQL statement, which compiles every time you take the cursor off the line, so should stop you making silly mistakes.

I have to say that double-quote/single-quote/double-quote causes the remainder of the string to become a comment (turns green)

Neither can I get a date field to accept ## (I've tried all combinations of quotes around it and I've tried Chr(35) & Chr(35))

I can save a Null to a date field but if it comes from an IIf() statement it must be as a "Null" string , which I find curious.

I have a solution that involves evaluating the date and putting the formatted output into a variant type variable, then using the variable in the SQL; all a bit convoluted but it works.

S7
I was unable to open the attached file as it is in a later version of Access. I am only using Access 2000 so perhaps that is the difference to why some things are not working.
Feb 1 '08 #18

Expert 100+
P: 446
I was unable to open the attached file as it is in a later version of Access. I am only using Access 2000 so perhaps that is the difference to why some things are not working.
Hi Tonia

Please find attached a '2000' database.

On re-reading my posting I seemed to have said "should stop you making silly mistakes..", should have read "should stop me making silly mistakes " :)

If you do read the code under my button, I have commented on some other ways of updating the 'target' table. The first instance (at block 100) uses a SELECT statement and works irrespective of Nulls and formats dates correctly. 'Un-comment' it and try.

At that point I wondered what all of the fuss was about because, as I said, I have been manipulating dates for years and not had any trouble, except with date formatting.

I work from the UK where we put the day before the month and I have found that Access will revert to 'American' dates, wherever possible. I used to have anomalies when testing towards the end of a month and dates based on 31st July would be OK but next day would be interpreted as 8th January.

I had other (different) problems with French dates.

Microsoft's fix (so long ago not to hand) is to always format "yyyy-mm-dd" when calculating or storing in the database. This apparently gives commonality with SQLServer and other databases. When displaying dates in a Form or Report you can re-format as required.

Sorry for rambling. .

S7
Feb 1 '08 #19

NeoPa
Expert Mod 15k+
P: 31,660
Hi S7.
I noticed this and I thought you may benefit from (Literal DateTimes and Their Delimiters (#)). The m/d/y format used in databases is actually a SQL standard and mirrors standard American format. This isn't really an Access issue as such.
Feb 2 '08 #20

Expert 100+
P: 446
Hi S7.
I noticed this and I thought you may benefit from (Literal DateTimes and Their Delimiters (#)). The m/d/y format used in databases is actually a SQL standard and mirrors standard American format. This isn't really an Access issue as such.
NeoPa
Thank you, that was interesting. It's perhaps not surprising that ANSI would advocate the 'American' style date nor perhaps that ISO 8601 say something different.

Regrettably, I can't turn up the Microsoft article that caused me to revise my developments to use the ISO notation, although I have spent a couple of hours looking on their website. (For my own benefit, not to be argumentative!) What is surprising is there are hundreds of instances where interpretation of dates has caused problems and while most of the solutions involve formatting 'ANSI' others refer to ISO. For instance:-
SQL Server 2005 Books Online
http://technet.microsoft.com/en-us/l.../ms174472.aspx
While this may not be directly "an Access issue" it is important that when you format a literal to be stored as a date in a database, that the correct value is stored. Tonia used a format I have not come across before which prompted my comment.

However, this thread was initially about storing a Null date. Tonia seems to have a solution that works for her. I'm still curious why it won't work for me (I've probably got a punctuation wrong somewhere!)

S7
Feb 4 '08 #21

NeoPa
Expert Mod 15k+
P: 31,660
S7,

Technical arguments are a good thing. Don't worry :)
As far as dates go in SQL, it's worth remembering that it's only the SQL we're referring to here - not how it's stored or displayed generally within the database. I followed the link, but couldn't see anything to suggest that anything other than the ANSI standards are ever used in any form of SQL.
I've never come across ISO standards pertaining to SQL so I suspect you were talking about standards relating to storage of dates. Please feel free to correct me if I'm mistaken in this.

Now I'm posting anyway, can I ask if you've received the PM I sent you recently?
Feb 4 '08 #22

Expert 100+
P: 446
Hello again Tonia - if you are still watching

Looking back through your posts it seems that all you problems stemmed from "How to copy Multiple fields from one record to another existing record". It would appear that you wanted assistance to build an APPEND query but in absence of that help have been struggling on trying to extract data from a list box instead. I have tried to answer how to do that in case you need to refer to it in the future.

Creating an UPDATE query with reference to a form contol (list box) has obviously proved problematic because the need to accommodate Nulls. This particular thread was to address null Dates. I seem to have gone off on a detour with NeoPa because I incorrectly advised about date formats, but that's irrelevant to your particular problem. In fact now that I have read further into the subject your Format() string was more correct than mine.

I refer to an article on the AllenBrown site http://allenbrowne.com/ser-36.html
He suggests writing a 'Wrapper Function' to evaluate date strings for inclusion in SQL. I have done a small modification to take account of Null.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Function SQLDate(varDate As Variant) As String
  3. 'Wrapper function by Allen Browne
  4. 'Purpose: Return a delimited string in the date format used natively by JET SQL.
  5. 'Argument: A date/time value.
  6. 'Note: Returns just the date format if the argument has no time component,
  7. ' or a date/time format if it does.
  8. 'Author: Allen Browne. allen@allenbrowne.com, June 2006.
  9. If IsDate(varDate) Then
  10. If DateValue(varDate) = varDate Then
  11. SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
  12. Else
  13. SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
  14. End If
  15. Else 'modified to include "Null"
  16. SQLDate = "Null"
  17. End If
  18.  
  19. End Function
  20.  
I have used this in my test-bed to convert two dates before inserting into my Target table.
Expand|Select|Wrap|Line Numbers
  1.  
  2. '600 Use of a Wrapper Function SQLDate() to produce string for SQL statement
  3. DoCmd.RunSQL "INSERT INTO tblTarget ( BoxNum, StartDate, EndDate)" & _
  4. " Values( " & Me!BoxNum & _
  5. "," & SQLDate(StartDate) & _
  6. "," & SQLDate(EndDate) & _
  7. " );"
  8.  
It does not matter whether these are populated or Null as you can test in the attached sample.

It would appear you have been stuck on this issue for a number of weeks. I hope the remainder of your project will progress more smoothly.

S7

NeoPa
I have responded to your PM. If I find something more to say about the ISO - ANSI issue you will be the first to know! Right now it's like saying you can't use an ISO expression in an ANSI language, which is logical; but even the French have le weekend. I need to find my original source because I didn't make it up!
Attached Files
File Type: zip db5_2000.zip (37.7 KB, 93 views)
Feb 4 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.