Problems with Date

[Andrew]

Hi,

I have a text box (Txt_Comments_Date) on a form. I have entered the date
15/07/08 (and it is formatted "dd/mm/yy").

Comments_Date = Format(Me!Txt_Comments_Date, "dd/mm/yy")

I want to place that value into a table using:

DoCmd.RunSQL "UPDATE Tbl_TAR_Toy_Cat_Replies SET
Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date] = #" & Comments_Date &
"# WHERE (((Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date]) Is Null));"

I always end up with 08/07/15 in the table even though the table field is
also formatted dd/mm/yy.

Please advise

 

[Graham Mandeno]

Hi Andrew

I can tell you don't live in America ;-)

SQL is very fussy about its date formatting and it pays no attention to your
regional settings. Two acceptable formats are "mm/dd/yyyy" (the North
American default) and "yyyy-mm-dd". I prefer the latter because it is less
ambiguous.

I suggest you write a function that takes a date and formats it in the
correct way, enclosed in hash (pound) signs:

Public Function SqlDate( d as Date ) as String
Const cFormat = "\#yyyy\-mm\-dd\#"
SqlDate = Format( d, cFormat )
End Function

Then you can use it thus:

strSQL = "UPDATE Tbl_TAR_Toy_Cat_Replies " _
& "SET [Buying Offce Comments Date] = " _
& SqlDate(Comments_Date) _
& " WHERE ([Buying Offce Comments Date] Is Null);"
CurrentDb.Execute strSQL, dbFailOnError

Note that using CurrentDb.Execure will circumvent the Access warning
messages.

 

[Andrew]

Hi Graham,

Yes, I live in Geelong - 1hr south of Melbourne, Australia.

I also had to alter my code - from Comments_Date =
Format(Me!Txt_Comments_Date, "dd/mm/yy") to Comments_Date =
Me!Txt_Comments_Date
as well as add Dim Comments_Date As Date

Thanks for your assistance!!


--
Andrew

Hi Andrew

I can tell you don't live in America ;-)

SQL is very fussy about its date formatting and it pays no attention to your
regional settings. Two acceptable formats are "mm/dd/yyyy" (the North
American default) and "yyyy-mm-dd". I prefer the latter because it is less
ambiguous.

I suggest you write a function that takes a date and formats it in the
correct way, enclosed in hash (pound) signs:

Public Function SqlDate( d as Date ) as String
Const cFormat = "\#yyyy\-mm\-dd\#"
SqlDate = Format( d, cFormat )
End Function

Then you can use it thus:

strSQL = "UPDATE Tbl_TAR_Toy_Cat_Replies " _
& "SET [Buying Offce Comments Date] = " _
& SqlDate(Comments_Date) _
& " WHERE ([Buying Offce Comments Date] Is Null);"
CurrentDb.Execute strSQL, dbFailOnError

Note that using CurrentDb.Execure will circumvent the Access warning
messages.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a text box (Txt_Comments_Date) on a form. I have entered the date
15/07/08 (and it is formatted "dd/mm/yy").

Comments_Date = Format(Me!Txt_Comments_Date, "dd/mm/yy")

I want to place that value into a table using:

DoCmd.RunSQL "UPDATE Tbl_TAR_Toy_Cat_Replies SET
Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date] = #" & Comments_Date
&
"# WHERE (((Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date]) Is
Null));"

I always end up with 08/07/15 in the table even though the table field is
also formatted dd/mm/yy.

Please advise

 

[Douglas J. Steele]

In addition to Graham's advise, you should probably read what Allen Browne
has at http://www.allenbrowne.com/ser-36.html, or my September, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". (You can download
the column, and sample database, for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html )

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Graham,

Yes, I live in Geelong - 1hr south of Melbourne, Australia.

I also had to alter my code - from Comments_Date =
Format(Me!Txt_Comments_Date, "dd/mm/yy") to Comments_Date =
Me!Txt_Comments_Date
as well as add Dim Comments_Date As Date

Thanks for your assistance!!


--
Andrew

Hi Andrew

I can tell you don't live in America ;-)

SQL is very fussy about its date formatting and it pays no attention to
your
regional settings. Two acceptable formats are "mm/dd/yyyy" (the North
American default) and "yyyy-mm-dd". I prefer the latter because it is
less
ambiguous.

I suggest you write a function that takes a date and formats it in the
correct way, enclosed in hash (pound) signs:

Public Function SqlDate( d as Date ) as String
Const cFormat = "\#yyyy\-mm\-dd\#"
SqlDate = Format( d, cFormat )
End Function

Then you can use it thus:

strSQL = "UPDATE Tbl_TAR_Toy_Cat_Replies " _
& "SET [Buying Offce Comments Date] = " _
& SqlDate(Comments_Date) _
& " WHERE ([Buying Offce Comments Date] Is Null);"
CurrentDb.Execute strSQL, dbFailOnError

Note that using CurrentDb.Execure will circumvent the Access warning
messages.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a text box (Txt_Comments_Date) on a form. I have entered the
date
15/07/08 (and it is formatted "dd/mm/yy").

Comments_Date = Format(Me!Txt_Comments_Date, "dd/mm/yy")

I want to place that value into a table using:

DoCmd.RunSQL "UPDATE Tbl_TAR_Toy_Cat_Replies SET
Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date] = #" &
Comments_Date
&
"# WHERE (((Tbl_TAR_Toy_Cat_Replies.[Buying Offce Comments Date]) Is
Null));"

I always end up with 08/07/15 in the table even though the table field
is
also formatted dd/mm/yy.

Please advise