473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

datetime field overflow (year 1899)

Hi,

I'm getting the datetime field overflow error using odbc to connect to an
sql database from access. This happens when I try to enter the date
13/01/1899. It did happen when using smalldatetime, which apparently does
have that problem of only going as far back as 1900, but since changing to
datetime the error still pops up.

Is there anything I can do?

Cheers,
Chris
Nov 12 '05 #1
6 6467
After you change the datatype to datetime in SQL Server you need to refresh
the linked table in Access.

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Hi,

I'm getting the datetime field overflow error using odbc to connect to an
sql database from access. This happens when I try to enter the date
13/01/1899. It did happen when using smalldatetime, which apparently does
have that problem of only going as far back as 1900, but since changing to
datetime the error still pops up.

Is there anything I can do?

Cheers,
Chris

Nov 12 '05 #2
Thanks, but that didn't work :o( still get the conversion errors

Cheers,
Chris
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:aI********************@karoo.co.uk...
After you change the datatype to datetime in SQL Server you need to refresh the linked table in Access.

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Hi,

I'm getting the datetime field overflow error using odbc to connect to an sql database from access. This happens when I try to enter the date
13/01/1899. It did happen when using smalldatetime, which apparently does have that problem of only going as far back as 1900, but since changing to datetime the error still pops up.

Is there anything I can do?

Cheers,
Chris


Nov 12 '05 #3
Strange it worked here.

Which version of Access / SQL are you using ?

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Thanks, but that didn't work :o( still get the conversion errors

Cheers,
Chris
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:aI********************@karoo.co.uk...
After you change the datatype to datetime in SQL Server you need to refresh
the linked table in Access.

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Hi,

I'm getting the datetime field overflow error using odbc to connect to an sql database from access. This happens when I try to enter the date
13/01/1899. It did happen when using smalldatetime, which apparently does have that problem of only going as far back as 1900, but since
changing to datetime the error still pops up.

Is there anything I can do?

Cheers,
Chris



Nov 12 '05 #4
SQL 8, Access 2002.... I guess the problem must be hiding someplace else..

cheers,
Chris
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Ls********************@karoo.co.uk...
Strange it worked here.

Which version of Access / SQL are you using ?

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Thanks, but that didn't work :o( still get the conversion errors

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:aI********************@karoo.co.uk...
After you change the datatype to datetime in SQL Server you need to

refresh
the linked table in Access.

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
> Hi,
>
> I'm getting the datetime field overflow error using odbc to connect
to an
> sql database from access. This happens when I try to enter the date
> 13/01/1899. It did happen when using smalldatetime, which
apparently does
> have that problem of only going as far back as 1900, but since

changing
to
> datetime the error still pops up.
>
> Is there anything I can do?

Nov 12 '05 #5
SQL Server 2000 and Access XP, those are the same versions I used to test
this.

Hmm, strange.

I'd suggest you go back and check through the steps again.

Make sure the field type on SQL is now datetime
Go into Access and use the Tools/Database Utilities/Linked Table Wizard
to refresh the link
Test whether you can add the data through the table
Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:c0**********@ucsnew1.ncl.ac.uk...
SQL 8, Access 2002.... I guess the problem must be hiding someplace else..

cheers,
Chris
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Ls********************@karoo.co.uk...
Strange it worked here.

Which version of Access / SQL are you using ?

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Thanks, but that didn't work :o( still get the conversion errors

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:aI********************@karoo.co.uk...
> After you change the datatype to datetime in SQL Server you need to
refresh
> the linked table in Access.
>
> "Not Me" <No****@faker.fake.fa.ke> wrote in message
> news:bv**********@ucsnew1.ncl.ac.uk...
> > Hi,
> >
> > I'm getting the datetime field overflow error using odbc to connect
to
an
> > sql database from access. This happens when I try to enter the
date > > 13/01/1899. It did happen when using smalldatetime, which

apparently does
> > have that problem of only going as far back as 1900, but since

changing
to
> > datetime the error still pops up.
> >
> > Is there anything I can do?


Nov 12 '05 #6
I think the SQL requires US-format for dates: mm/dd/yyyy and it may be
choking up on a thirteenth month.

Larry Linson
Microsoft Access MVP

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:c0**********@ucsnew1.ncl.ac.uk...
SQL 8, Access 2002.... I guess the problem must be hiding someplace else..

cheers,
Chris
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Ls********************@karoo.co.uk...
Strange it worked here.

Which version of Access / SQL are you using ?

Terry
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bv**********@ucsnew1.ncl.ac.uk...
Thanks, but that didn't work :o( still get the conversion errors

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:aI********************@karoo.co.uk...
> After you change the datatype to datetime in SQL Server you need to
refresh
> the linked table in Access.
>
> "Not Me" <No****@faker.fake.fa.ke> wrote in message
> news:bv**********@ucsnew1.ncl.ac.uk...
> > Hi,
> >
> > I'm getting the datetime field overflow error using odbc to connect
to
an
> > sql database from access. This happens when I try to enter the
date > > 13/01/1899. It did happen when using smalldatetime, which

apparently does
> > have that problem of only going as far back as 1900, but since

changing
to
> > datetime the error still pops up.
> >
> > Is there anything I can do?


Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: fowlertrainer | last post by:
Hi ! I think that I have been found a bug in mx.DateTime... So: I use FireBird, and in this RDBMS the datetime fields are "doubles". So if I set them to 0, the values the fields are '1899-12-30...
9
by: MGRIDEOUT | last post by:
Hello , I have a sub that gets all records in a table. Then displays the times in a drop-down list (ddlStartTime). My problem is that the ddl contain the date AND time. i.e. 9:00 AM in the...
12
by: Mark | last post by:
I feel embarassed asking such a simple question --- Year(Date()) returns 2004; I want only the last two digits. Format(Year(Date()),"yy") returns 05. How do I get 04? Thanks! Mark
3
by: La di da Limey | last post by:
Hi, I have a field in a database called "Timestamp" which has the date and time of an event, for example: "01/02/2002 09:07:59" The format is MM/DD/YYYY HH:MM:SS I want to chop the field...
5
by: A.M | last post by:
Hi, I know we can assigne year, month and day to a DateTime value by the constructor like Dim dt As DateTime = New DateTime(2003,8,1) How can i assign value by year, month and day? I am...
5
by: rs | last post by:
I have a table with a timestamp field which contains the date and time. ie. 9/13/2004 9:10:00 AM. I would like to split this field into 2 fields, one with just the DATE portion ie 9/13/2004 and...
4
by: Mohan | last post by:
In the database, we are storing time in 3 columns: hours, Minutes and AM,PM. After retrieving it from the database, I want to load these values into a DateTime variable. I haven't had much...
1
by: Pablo | last post by:
Hello, I have big problem with method ParseExact from class DateTime. Here is code sample: ...
2
by: drurjen | last post by:
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.