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.

Not tested but try this

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


"Dave Reardon" wrote:

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

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.

"Dave Reardon" wrote:

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

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

Dave Reardon wrote:
> 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.
>