Syntax

[odbcmsaccess]

i'm trying to insert integer, string, date data types into a table, code is as below

Error: data type not matched for integer and Date

sSQL = "INSERT INTO MANUFACTUREDGOODS(ENTRYDATE,DESCRIPTION,CETHNo ) VALUES( " ENTRYDATE " ,
' " & DESCRIPTION & " ', ' " & CETHNo & " ' )"

plz give me syntax for DATE and INTEGER data types.

ENTRYDATE = DATE FIELD
CETHNo= INTEGER FIELD

hoping positive

[NeoPa]

sSQL = "INSERT INTO MANUFACTUREDGOODS(ENTRYDATE,DESCRIPTION,CETHNo)" & _
    "VALUES(#" & Format(ENTRYDATE,"m/d/yyyy") & _
    "#,'" & DESCRIPTION & "'," & CETHNo & ")"

Dates should be formatted as M/D/YYYY and surrounded by "#" chars.
Strings should be surrounded by single-quote (') chars.
Numbers should be as they are without anything extra.

[odbcmsaccess]

thank for your reply, it worked
but it is giving another ERROR: Too few parameters

sSQL = "INSERT INTO MANUFACTUREDGOODS
(ENTRYDATE,DESCRIPTION,CETHNo)" & _
"VALUES(#" & Format(ENTRYDATE, "m/d/yyyy") & _
"#,'" & DESCRIPTION & "'," & CETHNo & ")"

hoping answer

i'm trying to insert integer, string, date data types into a table, code is as below

Error: data type not matched for integer and Date

sSQL = "INSERT INTO MANUFACTUREDGOODS(ENTRYDATE,DESCRIPTION,CETHNo ) VALUES( " ENTRYDATE " ,
' " & DESCRIPTION & " ', ' " & CETHNo & " ' )"

plz give me syntax for DATE and INTEGER data types.

ENTRYDATE = DATE FIELD
CETHNo= INTEGER FIELD

hoping positive

[odbcmsaccess]

I have created an ODBCMicrosoftAccessDriver of SystemDSN
but it is working first time only

second time it gives ERROR: run tmime error:2147467259(80004005)
Microsoft(ODBCMicrosoftAccessDriver)The database has been placed in a state unknown by an unknownuser that prevents it from being opened or locked.

give me solution

any other method other than system DSN

hoping positive

[Killer42]

...it worked but it is giving another ERROR: Too few parameters

Just to satisfy my curiosity, could you try pasting this code over the top of your existing one, just to see whether it makes any difference? (Making sure you have a copy of your own code, of course!)

sSQL = "INSERT INTO MANUFACTUREDGOODS (ENTRYDATE, DESCRIPTION, CETHNo)" & _
" VALUES(#" & Format(ENTRYDATE, "m/d/yyyy") & _
"#,'" & DESCRIPTION & "'," & CETHNo & ");"
 

Plus, I have a question. We already know the format of the database fields. What are the formats of the variables you are concatenating here? One thing whichi occurs to me is that you could have an empty string or a Null in variable CETHNo, and maybe the database field requires a value.

One other thing - I don't know what the usual naming concentions are, but it might reduce confusion if you rename the variables slightly to differentiate between them and the database fields.

[NeoPa]

Reported ?!? by OP :
i want to select data from a table, criteria is date

i used following code
but it is not working

DoCmd.OpenReport stDocName, acNormal, "Query1", DATE = "#SELECTDATE#"

hoping reply

[Killer42]

Reported ?!? by OP :
i want to select data from a table, criteria is date

i used following code
but it is not working

DoCmd.OpenReport stDocName, acNormal, "Query1", DATE = "#SELECTDATE#"

hoping reply

This is a bit of a confusing message, especially coming after such a long break in the thread. What's the story here? Anyway...

What does SELECTDATE represent? Is it supposed to cause Access to prompt the user for a date, or is it a variable name, or what?

In any case, the syntax of the fourth parameter in this statement looks completely wrong, to me. I'm surprised you didn't have something to say about it, NeoPa. I'm not that familiar with OpenReport, but the fourth parameter (wherecondition) is supposed to be a string expression, which the above is not.

I can think of a couple of possibilities to try...

DoCmd.OpenReport stDocName, acNormal, "Query1", "DATE = #" & SELECTDATE & "#"
 or
DoCmd.OpenReport stDocName, acNormal, "Query1", "DATE = [SELECTDATE]"
 

Can't guarantee either of them will work, though. Good luck!

[NeoPa]

Killer,
I had nothing to do with this except to post the text that the OP had 'Reported'.
I had no real reason to comment - this is so far 'beyond' that I could see no benefit.

[Killer42]

Killer,
I had nothing to do with this except to post the text that the OP had 'Reported'.
I had no real reason to comment - this is so far 'beyond' that I could see no benefit.

Oh, now I get it! You mean the OP hit the "report" button instead of "reply".

[NeoPa]

Yes - Hence the last line of my earlier post.
(He actually tried it twice.)