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?

 

[SkipVought]

Hi,

The Format function returns a STRING nt a DATE

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

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Thanks Skip for your repsonse.

I had that code originally (thanks PHV) but it throws up a run-time 170 error, incorrect syntax near '#'.

The help option on my PC is intermittent and is not worrking today, and I've checked the web for a description of the error but to no avail.

Any ideas?

 

[SkipVought]

Check you SPACES

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

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Hmm checked spaces and they're okay but error remains.

Still no joy searching for an axplanation of the error message ont he web.

 

[SkipVought]

the SPACES were NOT OK in your original post. There was no SPACE between the DATE and where. Did you take care of that?

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Sorry - yes I checked, moved, adjusted all spaces - still no difference.

Rather curiously if I remove the second # the error message is "Invlaid column name '#25'".

25 is the start of the date 25/01/200 that I'm trying to update into the table.

 

[SkipVought]

Of course, because #'s are DELIMITERS for dates just as "'s are delimiters for text. It takes a PAIR.

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Perhaps I'm missing something or have the incorrect syntax in the update clause but I thought the

(#" & Format(rs.Fields(0), "dd-mm-yyyy") & "#)"

was ensuring the format of the value to be updated into the cell.

Why would the error message say it' an

 

[DomDom3]

...sorry cut off

why would the error message say it's an invalid column name. I wasn't aware I was fiddling with the column name - which is still rs.fields(0) (or in actual fact [date].

I thought I was only changing the format of the value of a value int he field and not its title.

 

[SkipVought]

if I remove the second # the error message is "Invlaid column name '#25'".

And I replied that the #'s are DELIMITERS requireing TWO of them.

You removed ONE of them, thereby changing what the compiler interpreted as a DATE to an unknown FIELD or HEADING ie #25/01/200.

The error messages are not always concisely related to the actual problem.




Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

What is the value of rs.Fields(0) ?

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

 

[DomDom3]

The value of fields(0) is #25/10/2006# - this is what it should be, I'm foxed as to why it won't update.

 

[SkipVought]

Are your REGIONAL SETTING compatable with the dd/mm/yyyy format. The ## delimiters does a COMVERSION for the TEXT that is within the delimiters. It does not know the difference between 5/4/2006 being May 4 or April 5. It makes the determination based on your REGIONAL SETTINGS.

I would suggest using the DateSerial function, arg1 is year, arg2 is month, arg3 is day. There is NO ambiguity.

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Thanks for you response Skip, I've struggled to get Dateserial into the code - how do I do it?

Thanks

 

[SkipVought]

From a STRING, use Left, Mid & Right string functions

s = "4/5/2006"  'd/m/yyyy format
d = DateSerial(Right(s,4),Mid(s,3,1),Left(s,1))
...
s = "4/5/2006"  'm/d/yyyy format
d = DateSerial(Right(s,4),Left(s,1),Mid(s,3,1))

Skip,
[sub]
[red][/red]
[/sub]

 

[DomDom3]

Thanks for showing me where to go Skip.

I've done as you suggest but still get the same incorrect updates.


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)

The values of the the DateSerial are as intended 01, 02 and 2006 but still when I look in the table the cell is 00:00:00 (datetime format).

I'm nearing the end of my tether...any further ideas?

Thanks

 

[PHV]

What about this ?
strsql1 = "01/01/2006"
strSQL = "UPDATE WorkPackages1 SET [planned start date]=#" & Format(strsql1, "yyyy-mm-dd") & "# WHERE WPID=2878"
DoCmd.RunSQL strSQL


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

 

[DomDom3]

Thanks PH,

This is a slight change to one of your other suggestions at the beginning of this thread. It gives the same error -

Incorrect syntax near #

...I have no hair left to pull out :-(

 

[PHV]

So, are you playing with an MDB ?
What is the data type of [planned start date] in WorkPackages1 ?
What is WorkPackages1 ?

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