UPDATE date value

[DrAvi via AccessMonster.com]

Hello,

I'm trying to update date value in "MachineClockDate" column in SQL table
"TblRules" , and the following tries returns the following errors:

strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = # " & 24/01/2010 &
" # ;"
Error message: incorrect syntax near '#"

strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = ' " & 24/01/2010 &
" ' ;"
Error message: the conversion of a char data type to datetime date type
resulted in an out-of-range datetime value

strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = " & 24/01/2010 & "
;"
No error message, but the value stored is "03/01/1900"….

strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = " & 40202 & " ;"
Where 40202 represent the days count value. The value stored has shift of two
days, 26/01/2010 (why is that)?

How should I write the string, using Date() to update the SQL column value ?

Thank you,
DrAvi

 

[Tom Wickerath]

Hi DrAvi,

Check out this article, by Access MVP Allen Browne:

International Dates in Access
http://www.allenbrowne.com/ser-36.html

Do you get the correct result with this test procedure shown below?

Sub Test()
On Error GoTo ProcError

Dim strSQL As String
Dim varDate As Variant

varDate = "24/01/2010"


strSQL = "UPDATE TblRules " _
& "SET TblRules.MachineClockDate = " _
& Format$(varDate, "\#mm\/dd\/yyyy\#")

Debug.Print strSQL
CurrentDb.Execute strSQL

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Test..."
Debug.Print "Error " & Err.Number & ": " & Err.Description
Debug.Print
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Spencer]

You have spaces after or before the # delimiter and 24/01/2010 is a division
problem that will yield a very small number that equates to a time on Dec 30,
1899.

strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = #" & "24/01/2010" & "#"

If you want to update to today's date all you need is:
strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = Date()"

If you are trying to do this as a PASS THROUGH query then the SQL statement
for MS SQL Server would look like:
strSQL = "UPDATE TblRules SET TblRules.MachineClockDate = '2010-01-24'"

The other queries are suffering from the fact that MS SQL DateTime values go
back to Jan 1, 1900 as the earliest and the numbering of the dates start on
Jan 1, 1900 while Access starts at Dec 30, 1899 (a 2 day difference).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Oh and I should mention that literal date strings must be in the US format of
mm/dd/yyyy or in the unambiguous format yyyy/mm/dd. If they aren't then you
will get unexpected results on any date where the DAY number is 12 or less.
12/01/2010 is December 1, 2010 instead of January 12, 2010.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[DrAvi via AccessMonster.com]

Thank you both, Tom and john, for your help.

To the question raised, I am using pass trough query (DAO).

The only solution that finally worked for me was the one with the '2010-01-
24' format. The other options returned with error messages. At least I got A
solution, thank you.

Another question – in this query, I'm updating my column with date value
taken from the frontend calendar. What I really want is that the column will
be updated with the server date, according to it's calendar.

Can I do it with pass trough query, from the frontend?

DrAvi

 

[Tom Wickerath]

Hi DrAvi,

Another question – in this query, I'm updating my column with date value
taken from the frontend calendar. What I really want is that the column will
be updated with the server date, according to it's calendar.

I'm fairly sure you can do this, but I'm not familiar enough with SQL
Server. Consult Books On-Line to determine the equivalent function for Date()
in Access. My very foggy memory is suggesting to me--without taking the time
to look it up--that it might be GetDate().


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Spencer]

Good memory. The function is GetDate() although that returns both the date
and time.

I think that this will work to get just the date.
Cast(Convert(Char,GetDate(),110) as DateTime)

There is probably a better way to get just the date and if I were trying to
find it, I would check out the MS SQL newsgroups.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[DrAvi via AccessMonster.com]

I tried ti and you were both right!

Get date as it is returns server date and time, while the convert expression
returned only the server date.

I appreciate your help a lot - Thank you.

DrAvi