Update Queries and Date Formats

[DomDom3]

Hi there,

I have an update query taking a date from a smalldatetime field which I want to put into a datetime field.

I've used the code below:-

DoCmd.RunSQL "update [WorkPackages1]" _
& " set [Planned Start Date] = (" & Format(rs.Fields(0), "dd-mm-yyyy") & ")" & "where [WPID] = 2878"

But it seems only to take the first 2 digits (dd) and enter them into the table.

Can anyone hep please?

 

[DomDom3]

No its an adp - SQL Server back-end.

I've been changing the data type of [Planned Start Date] to try and work out the problem.

When I use the code:
strsql1 = "01/01/2006"
strSQL = "update [WorkPackages1] set [planned start date] = " & DateSerial(Right(strsql1, 4), Mid(strsql1, 4, 2), Left(strsql1, 2)) & "where [WPID] = 2878"
DoCmd.RunSQL (strSQL)

And the data type is nvarchar the value entered ito the field is 0.

And when the dataype is datetime the value entered into the field is 00:00:00,




datetime

 

[PHV]

And what about this ?
strSQL = "UPDATE WorkPackages1 SET [planned start date]=[tt]'"[/tt] & Format(strsql1, "yyyy-mm-dd") & [tt]"'[/tt] WHERE WPID=2878"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomDom3]

Eureka!

It worked - thanks a million.

I'm in so deep though I can't see the wood from the trees so am having difficulty understandiong why the extra ' used would have the affect they do.

Now that answer part of my problem, I have the rest to battle through now.

Cheers, my last strands of hair are saved ;-)