By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,324 Members | 1,931 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,324 IT Pros & Developers. It's quick & easy.

Syntax

P: 11
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
Nov 28 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,661
Expand|Select|Wrap|Line Numbers
  1. sSQL = "INSERT INTO MANUFACTUREDGOODS(ENTRYDATE,DESCRIPTION,CETHNo)" & _
  2.     "VALUES(#" & Format(ENTRYDATE,"m/d/yyyy") & _
  3.     "#,'" & 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.
Nov 28 '06 #2

P: 11
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
Nov 29 '06 #3

P: 11
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
Nov 29 '06 #4

Expert 5K+
P: 8,434
...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!)
Expand|Select|Wrap|Line Numbers
  1. sSQL = "INSERT INTO MANUFACTUREDGOODS (ENTRYDATE, DESCRIPTION, CETHNo)" & _
  2. " VALUES(#" & Format(ENTRYDATE, "m/d/yyyy") & _
  3. "#,'" & DESCRIPTION & "'," & CETHNo & ");"
  4.  
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.
Nov 29 '06 #5

NeoPa
Expert Mod 15k+
P: 31,661
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
Jan 2 '07 #6

Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport stDocName, acNormal, "Query1", "DATE = #" & SELECTDATE & "#"
  2. or
  3. DoCmd.OpenReport stDocName, acNormal, "Query1", "DATE = [SELECTDATE]"
  4.  
Can't guarantee either of them will work, though. Good luck!
Jan 2 '07 #7

NeoPa
Expert Mod 15k+
P: 31,661
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.
Jan 2 '07 #8

Expert 5K+
P: 8,434
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".
Jan 2 '07 #9

NeoPa
Expert Mod 15k+
P: 31,661
Yes - Hence the last line of my earlier post.
(He actually tried it twice.)
Jan 2 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.