date not correctly inserted when using sql in visual basic

A

AccessDeveloper

I am inserting a date in an Access table using Visual Basic. The date
inserted is not correct. It always inserts 12/30/1899. Please help.
 
D

Douglas J. Steele

How are you attempting to insert the date? 30 Dec, 1899 indicates that
you're passing a 0 to the field rather than a valid date.
 
J

John Spencer (MVP)

Or you are trying to insert 5/10/2004 vice #5/10/2004#.

The first is a nice little division problem and will get you something just a
little larger than 0. That will then store as a date and time on Dec 30, 1899.

So to help sole your problem can you post your code?
 
A

AccessDeveloper

This is my code:
DoCmd.RunSQL ("Insert into
EmployeeRates(EmployeeID,RateAsOf,PayRate,BillRate)
Values( " & Me.EmployeeID & "," & Me.HireDate & "," & Me.PayRate & "," &
Me.BillRate & ")")

I go in debug mode and check the value of Me.HireDate and it shows
"4/17/2005".
 
R

Rick Brandt

AccessDeveloper said:
This is my code:
DoCmd.RunSQL ("Insert into
EmployeeRates(EmployeeID,RateAsOf,PayRate,BillRate)
Values( " & Me.EmployeeID & "," & Me.HireDate & "," & Me.PayRate &
"," & Me.BillRate & ")")

I go in debug mode and check the value of Me.HireDate and it shows
"4/17/2005".
Click to expand...

Did you read John's response? You need to delimit dates with #.

DoCmd.RunSQL ("Insert into EmployeeRates(EmployeeID,RateAsOf,PayRate,BillRate)
Values( " & Me.EmployeeID & ", #" & Me.HireDate & "#," & Me.PayRate & "," &
Me.BillRate & ")")
 
D

Douglas J. Steele

You're right, that's a real common problem. (Of course, if you represent
today as 2005/05/17, as I always do, you won't end up with something just a
little larger than 0!)
 
A

AccessDeveloper

Thank you all for your help. It worked. Since I've worked mostly in Oracle,
I wasn't familiar with this date issue. Thanks again!
 
T

Tim Ferguson

You're right, that's a real common problem. (Of course, if you
represent today as 2005/05/17, as I always do, you won't end up with
something just a little larger than 0!)
Click to expand...

Doug: is this a legal or common date format where you live? It's obviously
close to the ISO standard, but I wasn't previously not aware of anywhere
that routinely uses y/m/d with obliques rather than dashes. And does Jet
recognise it as a date?

All the best


Tim F
 
M

Marshall Barton

"Douglas J. Steele" wrote
Tim said:
Doug: is this a legal or common date format where you live? It's obviously
close to the ISO standard, but I wasn't previously not aware of anywhere
that routinely uses y/m/d with obliques rather than dashes. And does Jet
recognise it as a date?
Click to expand...


Aw, Tim, I think Doug was joking. 2005/5/17 is still a
division, it's just not all that close to zero.

AFAICT, Access is very forgiving about the date separator
character. When you use the # literal identifier, you can
use #2005/5/17# or #2005-5-17# or # 2005 5 17 #
interchangably. (Regional settings can further confuse the
issue.)

But, then, you probably already knew that.
 
T

Tim Ferguson

But, then, you probably already knew that.
Click to expand...

Yes: I was asking more in terms of advising other people along the lines of
"At least this [ISO] format is unambiguous and you can use it without
fear..."

Or perhaps it's my Asperger gene showing through..<g>

All the best


Tim F
 
M

Marshall Barton

Tim said:
Yes: I was asking more in terms of advising other people along the lines of
"At least this [ISO] format is unambiguous and you can use it without
fear..."
Click to expand...


AFAIAC, it is the best way to specify dates.

However, it's impossible to force user's to do the same.

If you're talking about recommending to other developers,
does it matter? I think that as long as the regional
setting for the date separator character might be
unaccaptable within # signs, you still have to use the
Format function to be safe, so what matters the format
string you use?
 
T

Tim Ferguson

If you're talking about recommending to other developers,
does it matter? I think that as long as the regional
setting for the date separator character might be
unaccaptable within # signs, you still have to use the
Format function to be safe, so what matters the format
string you use?
Click to expand...

I think we are slightly at cross-purposes here, and not disagreeing.

Yes, I was thinking of the other developers, but of the kind that posts
here saying

"SELECT ... " & _
"WHERE LogDate BETWEEN #" & Date() & "# AND #" & Date()+7 & "#"

Perhaps I've just spent so long looking at these that I get really
finicky and boring about dates everywhere. My favourite at the moment is
some of the forms we have to fill in for tests, with only six boxes, so I
just scrawl in 20/04/20 and leave the rest up to the lab...


All the best


Tim F
 
M

Marshall Barton

Marshall Barton wrote
Tim said:
I think we are slightly at cross-purposes here, and not disagreeing.

Yes, I was thinking of the other developers, but of the kind that posts
here saying

"SELECT ... " & _
"WHERE LogDate BETWEEN #" & Date() & "# AND #" & Date()+7 & "#"

Perhaps I've just spent so long looking at these that I get really
finicky and boring about dates everywhere. My favourite at the moment is
some of the forms we have to fill in for tests, with only six boxes, so I
just scrawl in 20/04/20 and leave the rest up to the lab...
Click to expand...


I know what you mean, but because of all the possible
regional settings a user might have, your example could blow
up in a variety of ways. That's why I try to force myself
to code dates as:
..." & Format(Date(), "\#m\/d\/yyyy\#") & "...
but
..." & Format(Date(), "\#yyyy\/m\/d\#") & "...
would work as well. But as I said before, who cares which
one someone uses.
 
Top