date format

[muybn]

I am trying to insert a date with format "2008-10-21" into an Access database
but the first line shown in the code below inserts "1997" (obviously using
the dashes as minus signs). What would be the correct format to do what I
want?

strDate = Format(Date, "yyyy-MM-dd")

Set csConn = CreateObject("adodb.connection")
csConn.ConnectionString = "data source=C:\Data\0\jbDB.mdb;
Provider=Microsoft.Jet.OLEDB.4.0"
csConn.Open
strSQL = "UPDATE jbDBTable SET Sent =" & strDate & " where jbID =" & strID
csConn.Execute strSQL
csConn.Close

 

[Jan Hyde (VB MVP)]

muybn <[email protected]>'s wild thoughts were
released on Tue, 21 Oct 2008 01:50:01 -0700 bearing the
following fruit:

I am trying to insert a date with format "2008-10-21" into an Access database
but the first line shown in the code below inserts "1997" (obviously using
the dashes as minus signs). What would be the correct format to do what I
want?

strDate = Format(Date, "yyyy-MM-dd")

Set csConn = CreateObject("adodb.connection")
csConn.ConnectionString = "data source=C:\Data\0\jbDB.mdb;
Provider=Microsoft.Jet.OLEDB.4.0"
csConn.Open
strSQL = "UPDATE jbDBTable SET Sent =" & strDate & " where jbID =" & strID
csConn.Execute strSQL
csConn.Close

I don't know access, but it's usual to have quotes round a
date

send = '2008-10-21'

Also, unless 'sent' is a string then you cannot save a date
with a specific format.

 

[muybn]

Thanks, but none of these suggestions worked. "strSend" in my code represents
a string.

 

[Jan Hyde (VB MVP)]

muybn <[email protected]>'s wild thoughts were
released on Wed, 22 Oct 2008 00:18:00 -0700 bearing the
following fruit:

Thanks, but none of these suggestions worked. "strSend" in my code represents
a string.

Well your OP states that this line

strDate = Format(Date, "yyyy-MM-dd")

sets strDate to '1997'

Is this what you really mean?

 

[muybn]

I don't understand what you're trying to say with "Is this what you really
mean?"

The following statement inserts today's date just fine in the format
"10/22/2008" but I need it in the format "2008-10-22".

strSQL = "UPDATE jbDBTable SET Sent = date() where jbID =" & strID

 

[muybn]

To clarify more, the code line strDate = Format(Date, "yyyy-mm-dd") does fine
as long as you use it just for Word--that is, it results in the string
"2008-10-22" in a Word statement but if I use this string to update my
database as in the following statement listed in my OP (shouldn't matter if
it's Access or not since it's a generic SQL statement), the result in the DB
table is 1998, 1997, and today it would probably be 1996.

strDate = Format(Date, "yyyy-MM-dd")
[snip]
strSQL = "UPDATE jbDBTable SET Sent =" & strDate & " where jbID =" & strID

 

[muybn]

I finally figured out the answer:

strDate = Format(Date, "yyyy-mm-dd")
[snip]
strSQL = "UPDATE jbDBTable SET Sent = '" & strDate & "' where jbID =" &
strID

Or, if you want to skip the strDate part of it (substitute your names where
I have []; you can also use Date instead of Now):

strSQL = "UPDATE [yourTableName] SET [tableDateColumn] = '" & Format(Now,
"yyyy-mm-dd") & "' where [idColumn] =" & [idVariable]

 

[Jan Hyde (VB MVP)]

muybn <[email protected]>'s wild thoughts were
released on Wed, 22 Oct 2008 11:34:05 -0700 bearing the
following fruit:

I don't understand what you're trying to say with "Is this what you really
mean?"

The following statement inserts today's date just fine in the format
"10/22/2008" but I need it in the format "2008-10-22".

strSQL = "UPDATE jbDBTable SET Sent = date() where jbID =" & strID

I'll say again 'you cannot save a format along with a date.'

The way to deal with dates is to format them before you
display them.

 

[Jan Hyde (VB MVP)]

muybn <[email protected]>'s wild thoughts were
released on Wed, 22 Oct 2008 12:23:01 -0700 bearing the
following fruit:

I finally figured out the answer:

strDate = Format(Date, "yyyy-mm-dd")
[snip]
strSQL = "UPDATE jbDBTable SET Sent = '" & strDate & "' where jbID =" &
strID

Hold on, your solution is to enclose the date in single
quotes? You said that didn't work when I suggested it?