Date field format in INSERT query (Access 2000) 2

[Sashanan2]

Having trouble getting an INSERT query unleashed on an Access 2000 database from a VB6 application to work. The following query works fine:

INSERT INTO Progress(GameID, TimeSpent, Progress) VALUES (1,1,'text')

However I've got a date field in there as well, and as soon as I turn the query into:

INSERT INTO Progress(GameID, Date, TimeSpent, Progress) VALUES (1,#07-04-2004#,1,'text')

It throws an unspecified error ("There is a syntax error in your Access query.").

Now I'm not used to using Access, so I may well have gotten the # # part wrong, but how *should* I do it in that case?

 

[jrbarnett]

You are correct in putting the date value in hash marks is needed to delimit it, however the problem is likely to be the fieldname "date" which is a VBA function name and a reserved word in Access.

I would therefore rename the field in your table (use table design) and change the code and any bound fields.

John

 

[BobJacksonNCI]

INSERT INTO Progress(GameID, Date, TimeSpent, Progress) VALUES (1,#07/04/2004#,1,'text')

HTH,
Bob

 

[amorous]

Date is a reserved word ... try this:

INSERT INTO Progress(GameID, [Date], TimeSpent, Progress) VALUES (1,#07-04-2004#,1,'text')

-VJ

 

[Sashanan2]

*slaps forehead* Of course it's reserved. Should have known that. Changing the colum name solved the problem and the query now runs fine.

One little problem remains and that's that the date I enter is interpreted as American (mm-dd-yy) when it's intended to be European (dd-mm-yy). Can I force the database to read it as a European date like I can if I'm using SQL Server, with a convert function of sorts?

 

[amorous]

something like this:

CONVERT(yourfieldtype,yourfieldname,105)

example:

CONVERT(varchar(11), GETDATE(), 105)

-VJ

 

[Sashanan2]

Yeah, that's the SQL Server solution, but I thought that one was platform specific. Was just wondering if Access had an option for it as well.

There's no longer any rush for the answer, though, I just created a function that turns my European date in VB into an American one just prior to executing the query, so everything's now working as planned, and the ultimate in geekiness (a little database application in which I keep track of which video games I play on what date, for how long and what progress I made in them) is now operational. Thanks for the help, everybody!

 

[PHV]

Use the ANSI like notation: #yyyy-mm-dd#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244