Update Date problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update Date problem

Author

Topic

superkid
Starting Member

5 Posts

Posted - 2003-07-31 : 00:34:19

Hi,

I have problems updating my db. Was told that it was a syntax error in the sql statement. Anyone with any idea what's the problem with my statment.

Thanks in advance

chkdate = DateAdd("d", 7, rsUser("Date"))

UpdateSQL = "UPDATE Misc SET Date = '" & chkdate & "' where ID = 1 "

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-31 : 02:44:39

Maybe this:

UpdateSQL=
"UPDATE Misc SET Date=" & Chr(34) & chkdate & Chr(34) & " where ID = 1 "

PS Chr(34) is Double Quote >> " <<

- Vit

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 03:43:02

noo, noo, noo, noo, nooooooooooo

1.Date is a reserved word so you'll need to enclose it in square brackets, eg [Date]

2. Strictly, you should also enclose your date in # rather than "

3. You should specify a date format, rather than rely on system settings

putting it all together

chkdate = DateAdd("d", 7, rsUser("Date"))

UpdateSQL = "UPDATE Misc SET [Date] = #" & Format(chkdate,"dd-mmm-yyyy")
& "# where ID = 1"

And if that doesn't work - you can have your money back

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-31 : 05:11:37

Rob,

I totally agree with you but paradoxically enough your code in Red
doesn't work in my version of Access.
Have you ever worked in a non-english version of MS Access? :)

- Vit

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-31 : 16:14:28

Non English version??? oh you mean Latin.

Sorry Stoad I could not resist.

As a rule I never use date as a Field name, Too many possible problems. Try somthing like Mydate.

Jim
Users <> Logic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-31 : 16:43:11

I say: never format the date when you wish to do this. By default, Access will format the date into the same format it accepts (which should make sense!)

chkdate = DateAdd("d", 7, rsUser("Date"))

UpdateSQL = "UPDATE Misc SET [Date] = #" & chkdate & "# where ID = 1"

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 19:44:06

Oh Mr Cross Join (he's actually quite a nice guy and not very cross at all...)

I'm afraid that philosophically I can't agree with you. I ALWAYS format dates when compiling them into a SQL string, and quite frankly you gotta do this on SQL Server. Maybe I'm just anal, or maybe I happen to live in that small percentage of the world which is not in the USA!

Oh, and the code worx for me (just tested it again). Now, when you say it doesn't work (and before you get your money back)- can you post the code for your entire subroutine?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-31 : 22:37:18

I should have made it clearer that I meant in ACCESS only-- i agree about SQL Server.

but the default display format for a date in Access should be the exact same one it accepts by default.

run the update statement, then change your date settings to something else, then run it again ... you'll see it is good to go.

SQL Server is different because the client and the server are two different beasts ...

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 23:19:24

Yep, I understand that it will work for Access - but there are a lot of things that will work in Access which should be avoided like the plague!

My point is a philosphical (not technical) one. I like to be able to transport my code and reuse it - eg from Access to ASP etc etc

Maybe we here down-under are a little over-sensitive about datetime - or maybe it's just me! (sorry if I'm a bit boisterous - I'm in a good mood today!

)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 23:27:37

Tell me more about the strictness value of this # thing.

Sam

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 23:30:52

what would you like to know...?

as I understand it (and I could be wrong - have been many times before) using the # tells Access to interpret the following as a date - similar in concept to using convert(bla) in SQL Server.

If you just use quotes, there is an implicit conversion going on, hence the strictness comment. As pointed out by the not-so-very cross join - it'll work either way. Personally, my experience suggests avoiding implicit conversions, because if you ever make the mistake of reusing your code in a less forgiving circumstance, you're screwed.

OK so I'm anal...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 23:42:08

Arg ! I didn't realize this was an Access group !

There it is, right in the upper left. MS Access.

Also, I agree that Mr. Cross Join is an all around helpful guy. Although I've only seen one post from him using a Cross Join since he earned that title.

I'm going to recommend he be promoted to Dr. Cross Join when he hits 2000.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-31 : 23:44:37

No need to apologize !! I hear you 100% !

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 23:49:34

quote:
Originally posted by jsmith8858

No need to apologize !! I hear you 100% !

- Jeff

See what I mean? I think he should be called Mr Not-so-very-cross Join

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-01 : 02:44:44

s = "update tt set dt=#" & Format(Date, "dd-mmm-yyyy") & "#"

DoCmd.RunSQL s

- Vit

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-08-01 : 03:08:19

hmmmm....

I just pasted your code into my .mdb file and it worked...(as it should)

Could you print the value of the string s before doing the DoCmd.RunSQL - so I can see what it has?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-01 : 03:24:06

This is MsgBox s:

BTW,
these work:

s = "update tt set dt=#" & Date & "#"
s = "update tt set dt=#" & Format(Date, "dd.mm.yy") & "#"

but these don't work:

s = "update tt set dt=#" & Format(Date, "dd-mmm-yy") & "#"
s = "update tt set dt=#" & Format(Date, "dd.mm.yyyy") & "#"

- Vit

superkid
Starting Member

5 Posts

Posted - 2003-08-03 : 01:23:18

Thanks for the solution.
Healthy discussion ;p

Once again thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-08-03 : 20:26:20

Hi Stoad

OK, I've never seen a date written as dd.mm.yy before...looks like some kind regional difference...I'll have to remember that next time I'm coding in russian....

by the way, what about Format(date, "yyyymmmdd")

?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-04 : 02:43:11

Hi Rob!!

No, this doesn't work too...
In general, "yyyy" or "mmm" don't work.

- Vit

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-08-04 : 02:55:04

Good to see the Y2K problem is alive and well....Just out of interest - what version of Access do you have?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources