Trouble with INSERT of Date/Time value. 1

[Robeen]

I'm trying to insert a value into an Access table. The field is defined as Date/Time in the Access table.
I have not had a problem Inserting String/Text or numeric values - just the Date/Time value.
Here's what I have tried:

INSERT INTO Receipt (Folio, Amount, Code, DateTime) 
	VALUES ('44444',555.50,
                'TUI4',#29-Feb-76#)

I can also Update any of the other three fields in the table but the #29-Feb-76# does not work.

Any help will be appreciated.

 

[CCLINT]

So, what happens? (error or just nothing changes?)

Try:

VALUES ('44444',555.50,'TUI4'," & Format$(#29-Feb-76#, "\#mm\/dd\/yyyy\#&quot & &quot

 

[Robeen]

I tried your suggestion . . .
Nothing happens - the record is not inserted [or - in the case of an update - updated].

Thanks!!

 

[CCLINT]

Please post your connection string here.

Also, add brackets around the DateTime field as in: [DateTime]

And, does the INSERT work right when you leave out the DateTime field?

Gotta run. I'll get back with you tomorrow.

 

[DonPeters]

Robeen - Are you getting an error - I created a simple Access2000 db and get a systax error when I try do do an insert as you have described - I suspect it has something to do with the field name (DateTime) - I believe DateTime is a reserved word. Try putting brackets around the field name - [DateTime]

 

[Robeen]

Here's my connection string:
Public Function ConnectString() _
As String
ConnectString = "Provider=" & _
"Microsoft.Jet.OLEDB." & _
"4.0;Data Source=" & _
App.Path & "\bpsacdat-3-14-03.mdb"
'App.Path & "\NWind.mdb"
End Function

I am not getting an error - but nothing gets inserted.
I am able to insert into other tables which don't have Date/Time fields. I can update records in this table as long as I don't try updating the value in the DateTime field.
Also - I can't insert without the DateTime field as DateTime is part of teh key in this table [along with Folio].
I tried changing the name of the field from DateTime but that didn't work. I've tried so many things . . ..
I tried the brackets around the field name - [DateTime] - that didn't work either. Thanks!!

 

[CCLINT]

Go into the data base table and report back the field type for the DateTime field.

Also, using the field DateTime in the query with-out brackets should error out with a syntax error. Is this not happening?

Last, when you use this in the debug/immediate window, what value is returned? (do this right after the connection is opened)

Debug.Print conn.Properties("Jet OLEDB:Global Partial Bulk Ops&quot

 

[Robeen]

Woops! There IS an error reported in the Form . . .
This SQL:
INSERT INTO receipts (Folio,
Amount,Code,DateTime)
VALUES ('44444',555.50,'TUI4'," & Format$(#29-Feb-76#, "\#mm\/dd\/yyyy\#&quot & &quot

Gives me this error message:
'Execute SQL Error: Syntax error in INSERT INTO statement.'

I never thought to look down there in the bottom of the Form where the SQL Error message is being displayed.

But I knew all along that it is a syntax issue - probably something to do with how I couch the value with some kind of quotes or something before I try inserting it . . .

This SQL:
INSERT INTO receipts (Folio,
Amount,Code,[DateTime])
VALUES ('44444',555.50,'TUI4'," & Format$(#29-Feb-76#, "\#mm\/dd\/yyyy\#&quot & &quot

- I put the field name DateTime in brackets [DateTime] . . .

Gives me this error message:
'Execute SQL Error: Syntax error - missing operator in query expression " ' & Format$(#29-Feb-76#, "\#mm\/dd\/yyyy\#&quot & ' "

??

 

[Robeen]

Hi Again!
I finally made a LITTLE progress . . .

This:
INSERT INTO receipts (Folio,
Amount,Code,[DateTime])
VALUES ('44444',555.50,'TUI4',Now())

WORKED!

It took the result of the Now() function and inserted it.

That might work for now - but I still want to learn how to take complete control of inserting a date into the table.
In this one table - since the date field is part of the key - I have to be able to insert it successfully . . .
Fortunately for me - I don't work with VB & Access - so this is not critical to my paycheck!! Still - any more ideas would be welcome!! I'll keep trying here & I'll let you know If I figure it out. Thanks!!!

 

[DonPeters]

Take out the format statement and try it...

INSERT INTO Receipt (Folio, Amount, Code, [DateTime])
VALUES ('44444',555.50,
'TUI4',#29-Feb-76#)

OR

INSERT INTO Receipt (Folio, Amount, Code, [DateTime])
VALUES ('44444',555.50,
'TUI4','29-Feb-76')

let me know

 

[Robeen]

Thanks Don!
That worked. Both '29-Feb-76' AND #29-Feb-76# - with the field name DateTime in brackets thus - [DateTime]. . .
In retrospect - I think that was what you had advised me to try the first time you posted a suggestion.
My bad. I don't know how many different things I tried after I started doing the '[DateTime]' thing - but I must not have tried it with this 29-Feb-76 format . . .
Thanks so much everyone.
Sheepishly . . .
Robin

 

[CCLINT]

> but I still want to learn how to take complete control of inserting a date into the table

Robeen:

Three things:
1. As I mentioned in my second post to use brackets around the DateTime field, and as DonPeters also mentioned later, it may be a good idea to get in the habit of using brackets around all Table and Field names - always.
Obviously, that would have solved the problem at the begining.

2. You should always format the date being inserted, unless the date is hard coded to the provider's specific syntax.
The reason for this, is if you use a date variable, or a string variable to insert a date, and your program is ran on another local, you will have problems.
Formating the date will insure that this doesn't happen, as the date will always be formated in US format.

JET:
"INSERT INTO receipts (Folio,Amount,Code,[DateTime]) VALUES ('44444',555.50,'TUI4'," & Format$(dtSomeDate, "\#mm\/dd\/yyyy\#&quot & &quot"

Replace the # sign as needed, with ', depending on the
provider's specifications:

SQL Server:
"INSERT INTO receipts (Folio,Amount,Code,[DateTime]) VALUES ('44444',555.50,'TUI4'," & Format$(dtSomeDate, "\'mm\/dd\/yyyy\'&quot & &quot"

HOWEVER, a word of caution, when running your program on a differen local:
In this case, you are JET. Putting single quotes around the date will not work correctly on certain locals.
Jet handles these values as strings, and not dates, so it converts the value based on the local's settings.
This means that a date of 10-02-76 may be converted to 02-10-76 (as in German: dd.mm.yyyy)!

Using #10-Feb-76# will work however, if hard coded, but not in a date variable. It will format to the local settings when passed the the SQL statement.

And '10-Oct-76' will not work because the conversion cannot take place, as the local may not be in english lanuageand "Oct" will not work (German Okt = Oct).

And #10-Okt-76# (German short month name) will not work because the month is not in english, and you are trying to send a US date as identified by use of the # sign.

So, the ONLY way to have complete control over the dates in an SQL statement is to format the date into US format as I have shown above in the Insert statement.

3.
One thing that was curious in your original posts, was the fact that an error wasn't returned when there were no brackets around that DateTime field.
This should have produced an error from the start, and not just continue with-out updating the recordset.
So, it looks like the code is indeed erroring out and posting an error "at the bottom of the form".
For this type of error, it may be better to display it in a messagebox in order to make you fully aware of it, at least until the program is finished.

 

[johnwm]

CCLINT,

That's a very nice summary of the "DATE" issues in db queries.

Would you consider writing it up as a FAQ? - if, so please post the FAQ number in this thread so we can refer others to it.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Robeen]

CCLINT,
Thanks for the additional info on the date format etc.
In fact, the next step for me in my VB journey is going to be how to get a date from a user and insert it and other information into a table . . .. I know I'm going to keep coming back to your format suggestions sooner or later - so I'm going to try it out while it's still fresh.
Thanks again - everyone! Much appreciated.
Robin [Robeen]

 

[Robeen]

Post Script:
CClint: The error message was showing all along in a StatusBar "at the bottom of the form". I am re-teaching myself VB & used a tutorial off the internet. I focused on inserting the DateTime value & didn't pay much attention to the fact that I had pasted in code that was displaying error messages. The way the Form was positioned & sized, the StatusBar "at the bottom of the form" was not visible till I actually happened to drag the Form up at run time & see the message.

I have, since, managed several variations using user-entered dates and variables and so on.

I'm only just beginning to understand about what has been said in this thread about the date being treated as a string . . .

I tried:
Dim dtMyDate as Date
dtMyDate = 04-02-03
txtSQL = "INSERT INTO receipts (Folio,Amount,Code,[DateTime])" _
& "VALUES ('99999',995.50,'TUI4'," & Format$(dtMyDate, "\#mm\/dd\/yyyy\#&quot & " )"

. . . then I executed the SQL

. . . and I ended up with a date of - 12/29/1899 -

so I changed:
dtMyDate = 04-02-03
to:
dtMyDate = "04-02-03"

and then, of course, it worked well.

Thanks again!

Robeen

 

[CCLINT]

johnwm:

Thanks for your comment. Always nice to hear things like that.
I guess one of these days when I get the time I will post an FAQ on this, along with some others which I have been asked to do, or feel should be done. And, I wouldn't want to just start typing away with-out taking the time to put some thought into it, and into the correct wording.

 

[johnwm]

CCLINT

We look forward to it when you're ready - and I know what you mean about TIME - I have a couple of faqs just building up inside me!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'