|
|
I have the following sql:
DIM TodayDate as DATE
TodayDate=Date
"UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
DefendantID=" & ID
Everything is fine BUT the date that is updated to the table is way off:
The date that is written to the table is 12/30/1899.
I am doing a debug print to see what the different values are and here is
the result.
Now 12/31/2008 1:32:45 PM
Date 12/31/2008
TodayDate1 12:00:00 AM (upon being initialized)
TodayDate2 12/31/2008 (after being assigned the value of Date)
|
|
On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr wrote:
[Quoted Text] > I have the following sql:
>
> DIM TodayDate as DATE
>
> TodayDate=Date
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
> DefendantID=" & ID
>
> Everything is fine BUT the date that is updated to the table is way off:
>
> The date that is written to the table is 12/30/1899.
>
> I am doing a debug print to see what the different values are and here
> is the result.
>
> Now 12/31/2008 1:32:45 PM
> Date 12/31/2008
> TodayDate1 12:00:00 AM (upon being initialized) TodayDate2 12/31/2008
> (after being assigned the value of Date)
Where is the SQL being executed? In your code?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
"Rick Brandt" wrote:
[Quoted Text] > On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr wrote:
>
> > I have the following sql:
> >
> > DIM TodayDate as DATE
> >
> > TodayDate=Date
> >
> > "UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
> > DefendantID=" & ID
> >
> > Everything is fine BUT the date that is updated to the table is way off:
> >
> > The date that is written to the table is 12/30/1899.
> >
> > I am doing a debug print to see what the different values are and here
> > is the result.
> >
> > Now 12/31/2008 1:32:45 PM
> > Date 12/31/2008
> > TodayDate1 12:00:00 AM (upon being initialized) TodayDate2 12/31/2008
> > (after being assigned the value of Date)
>
> Where is the SQL being executed? In your code?
>
It is attached to a command button. The purpose of the command button is to
ultimately merge the data with a form letter. Prior to the merge function,
it updates the fields to include the date that the summons was generated.
Thanks,
Ray.
|
|
On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr
<RayToddJr[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >I have the following sql:
>
>DIM TodayDate as DATE
>
>TodayDate=Date
>
>"UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
>DefendantID=" & ID
>
>Everything is fine BUT the date that is updated to the table is way off:
>
>The date that is written to the table is 12/30/1899.
>
>I am doing a debug print to see what the different values are and here is
>the result.
>
>Now 12/31/2008 1:32:45 PM
>Date 12/31/2008
>TodayDate1 12:00:00 AM (upon being initialized)
>TodayDate2 12/31/2008 (after being assigned the value of Date)
To insert a date value in an update query use # delimiters:
"UPDATE taDEFENDANTS SET SummonsGeneratedDate=#" & TodayDate & "# WHERE
DefendantID=" & ID
or more simply, bypass the variable altogether:
"UPDATE taDEFENDANTS SET SummonsGeneratedDate=Date() WHERE DefendantID=" & ID
What's happening is that Access is interpreting 12/31/2008 as an arithmatic
exprssion equal to .00019277727798483485, which corresponds to some 16 seconds
after midnight, December 30, 1899 when interpreted as a date.
--
John W. Vinson [MVP]
|
|
On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr wrote:
[Quoted Text] > I have the following sql:
>
> DIM TodayDate as DATE
>
> TodayDate=Date
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
> DefendantID=" & ID
>
> Everything is fine BUT the date that is updated to the table is way off:
>
> The date that is written to the table is 12/30/1899.
>
> I am doing a debug print to see what the different values are and here is
> the result.
>
> Now 12/31/2008 1:32:45 PM
> Date 12/31/2008
> TodayDate1 12:00:00 AM (upon being initialized)
> TodayDate2 12/31/2008 (after being assigned the value of Date)
Surround the date variable with the date delimiter symbol #.
"UPDATE taDEFENDANTS SET SummonsGeneratedDate= #" & TodayDate & "#
WHERE DefendantID=" & ID
Also, your code indicates that DefendentID is a Number datatype.
If, in fact, it is a Text datatype, then uise:
...... "# WHERE DefendantID='" & ID & "'"
But why do you need the TodayDate variable at all?
"UPDATE taDEFENDANTS SET SummonsGeneratedDate= #" & Date & "# WHERE
DefendantID=" & ID
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
|
|
That did it. Thanks for the help...
"John W. Vinson" wrote:
[Quoted Text] > On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr
> <RayToddJr[ at ]discussions.microsoft.com> wrote:
>
> >I have the following sql:
> >
> >DIM TodayDate as DATE
> >
> >TodayDate=Date
> >
> >"UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
> >DefendantID=" & ID
> >
> >Everything is fine BUT the date that is updated to the table is way off:
> >
> >The date that is written to the table is 12/30/1899.
> >
> >I am doing a debug print to see what the different values are and here is
> >the result.
> >
> >Now 12/31/2008 1:32:45 PM
> >Date 12/31/2008
> >TodayDate1 12:00:00 AM (upon being initialized)
> >TodayDate2 12/31/2008 (after being assigned the value of Date)
>
> To insert a date value in an update query use # delimiters:
>
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate=#" & TodayDate & "# WHERE
> DefendantID=" & ID
>
> or more simply, bypass the variable altogether:
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate=Date() WHERE DefendantID=" & ID
>
> What's happening is that Access is interpreting 12/31/2008 as an arithmatic
> exprssion equal to .00019277727798483485, which corresponds to some 16 seconds
> after midnight, December 30, 1899 when interpreted as a date.
> --
>
> John W. Vinson [MVP]
>
|
|
Thanks for the help, that worked....
Ray.
"fredg" wrote:
[Quoted Text] > On Wed, 31 Dec 2008 10:40:01 -0800, Ray Todd Jr wrote:
>
> > I have the following sql:
> >
> > DIM TodayDate as DATE
> >
> > TodayDate=Date
> >
> > "UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & TodayDate & " WHERE
> > DefendantID=" & ID
> >
> > Everything is fine BUT the date that is updated to the table is way off:
> >
> > The date that is written to the table is 12/30/1899.
> >
> > I am doing a debug print to see what the different values are and here is
> > the result.
> >
> > Now 12/31/2008 1:32:45 PM
> > Date 12/31/2008
> > TodayDate1 12:00:00 AM (upon being initialized)
> > TodayDate2 12/31/2008 (after being assigned the value of Date)
>
>
> Surround the date variable with the date delimiter symbol #.
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate= #" & TodayDate & "#
> WHERE DefendantID=" & ID
>
> Also, your code indicates that DefendentID is a Number datatype.
> If, in fact, it is a Text datatype, then uise:
>
> ...... "# WHERE DefendantID='" & ID & "'"
>
> But why do you need the TodayDate variable at all?
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate= #" & Date & "# WHERE
> DefendantID=" & ID
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
|
|
<picky>
Not everone has his/her Short Date format set to mm/dd/yyyy, which is what
Access expects in SQL statements.
To be more generic, the solution should be
"UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & _
Format(TodayDate, "\#yyyy\-mm\-dd\#") & _
"WHERE DefendantID=" & ID
</picky>
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"John W. Vinson" <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote in message
news:sognl491cbmcmjiqnv673cvcc2ub17in06[ at ]4ax.com...
[Quoted Text] >
> To insert a date value in an update query use # delimiters:
>
>
> "UPDATE taDEFENDANTS SET SummonsGeneratedDate=#" & TodayDate & "# WHERE
> DefendantID=" & ID
|
|
On Wed, 31 Dec 2008 15:19:14 -0500, "Douglas J. Steele"
<NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote:
[Quoted Text] ><picky>
>
>Not everone has his/her Short Date format set to mm/dd/yyyy, which is what
>Access expects in SQL statements.
>
>To be more generic, the solution should be
>
>"UPDATE taDEFENDANTS SET SummonsGeneratedDate=" & _
> Format(TodayDate, "\#yyyy\-mm\-dd\#") & _
> "WHERE DefendantID=" & ID
>
></picky>
Thanks, Douglas... quite right!
That's probably another advantage of just updating to Date(), which will be
interpreted correctly since JET itself knows the function.
--
John W. Vinson [MVP]
|
|
|