Date Function and SQL Question

[Ray Todd Jr]

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)

 

[Rick Brandt]

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?

 

[Ray Todd Jr]

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.

 

[John W. Vinson]

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.

 

[fredg]

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

 

[Ray Todd Jr]

That did it. Thanks for the help...

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.

 

[Ray Todd Jr]

Thanks for the help, that worked....

Ray.

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

 

[Douglas J. Steele]

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

 

[John W. Vinson]

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