Date woes using VBA

[Dave Reardon]

I have a table with a field for dates, which contains four rows. I am
attempting to use the first date to populate a field in a different table,
using an update query written in SQL. The syntax is:
rs.ActiveConnection = DR

rs.Open "AttDatesT"

With rs
rs.MoveFirst
datDate = .Fields("DateId")

MsgBox strDate
strSQL1 = "UPDATE [Students-T] SET [Students-T].Date1 = "
DoCmd.RunSQL strSQL1 & datDate
End With
rs.Close

The message box displays the date correctly, eg 10/02/2010 but when I go to
the field to look at the data in the table it typically displays something
like 00:03:35 I assume it is to do with a date conversion, but I don't seem
to find any reference to this anywhere. Any help very welcome.

 

[RonaldoOneNil]

Not tested but try this

strSQL1 = "UPDATE [Students-T] SET [Students-T].Date1 = #"
DoCmd.RunSQL strSQL1 & datDate & "#"

 

[MPM1100]

As specified in the last post, you do need to provide the format charcter for
dates "#" on either side of your variable. You should also check the
recipient field to ensure that it is a date field that it isn't formatted
adversely. It looks as though you have a time format in that field.

 

[John Spencer]

MsgBox is using a variable strDate and the SQL statement is using a variable
named datDate.

Assuming that datDate is actually getting the same date and is a datetype you
need to modify your sql string so it has the date delimiters # surrounding the
date. RIght Now you are passing in the result of a division so Dec 30 2000 is
probably the results of dividing 12 by 30 and then dividing that by 2000 which
is going to give you a rather small decimal fraction on the zero day.

DoCmd.RunSQL strSQL1 & Format(datDate,"\#yyyy-mm-dd\#")

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