Update conected MSSQL Table with DATESERIAL(DATEPART .. crashes AC

[ronny.ziegner]

Hallo,

I tried to update a connected SQL Server Table (with ODBC) in Access 2003.
Like this --> I need the last day of the month in this table.

UPDATE tblTime

SET

Extra = DATESERIAL(DATEPART(('yyyy',Timestamp),DATEPART('m',Timestamp) + 1
,0);

But MS Access 2003 hangs up... with critical error and restarts..
Does anyone have a workaround or a solution for this problem?

Thanks..

 

[John Spencer]

TimeStamp is a special type of field in SQL. IT is not a date time field.

Is timestamp in your table a field of type Date or is it of type Timestamp?

If it is a date field then I see nothing wrong with your SQL statement,
although I would have used the Year and Month functions and being a bit
paranoid would have ensured that there is a value in the TimeStamp field.

UPDATE tblTime
SET Extra = DateSerial(Year(TimeStamp),Month(TimeStamp)+1, 0)
WHERE TimeStamp is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ronny.ziegner]

Hallo John,

thank you for your answer. No the Fieldname is not Timestamp, it's 'Datum'
and the Type is 'datetime'. The field 'Extra' is a varchar - field.

I've tried it with Year and Month, but Access crashes, too. If I use this
query to an access table, then it works fine.

I have no idear to solve this problem.

 

[John Spencer]

So you are trying to stuff a date field into a text field. That could be a
problem. Try forcing the data type using CStr or using the format function
or just building the string


UPDATE tblTime
SET Extra = CStr( DateSerial(Year(TimeStamp),Month(TimeStamp)+1, 0))
WHERE TimeStamp is Not Null

OR


UPDATE tblTime
SET Extra = Format( DateSerial(Year(TimeStamp),Month(TimeStamp)+1,
0),"yyyy-mm-dd")
WHERE TimeStamp is Not Null

I am curious why you would store date type information in a varChar field.
Storing date information in a text field would just make it difficult to use
the date information meaningfully in sorts, searches, and calculations
(without converting the string back to a date type.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ronny.ziegner]

Hallo,

you are right, in an other szenario I would use a date field....
Now I have tested the same in MS Acces 2007 (Testversion) and it runs.
Is there a major diffrence between 2003 and 2007?

Thanks.

 

[ronny.ziegner]

Hallo,

this doesn't solve my problem. Wich component have to be replaced to work
without errors?
Why it doesn't work in Access 2003?

Regards, Ronny Ziegner.

 

[John Spencer]

I have no idea on why it works in Access 2007 and fails in Access 2003. I
would think that if your tables are set up the same then it should work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..