ACCESS FEED: Re: [MS_AccessPros] Syntax help

Kamis, 27 Agustus 2015

Re: [MS_AccessPros] Syntax help

John,

That was it!! Thanks so much. I do know not to use Date and other keywords as field names. This is a database that someone else started. I would like to change that but I'm afraid it would break something.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Doyce-

Can the vbCRLF and just use a space:

strSQL = "INSERT INTO tblResults ( [Record No], [Form ID], [Pass/Fail], [Score], [Possible], [Date] ) " & _
"Values ( " & lngRecNo & ", '" & strTestNo & "', '" & strPassFail & "', '" & strScore & "', '" & strPossible & "', #" & dteAward & "#);"

Also, are you sure that the fields Record No, Form ID, Pass/Fail, Score, Possible, and Date exist in tblResuts? BTW, you should NEVER name a field "Date" because that's also the name of a built-in function. Use something like TestDate instead.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 27, 2015, at 11:44 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Darrell,

I tried both ways. Each way I'm getting a message of error 3061. Too few parameters. Expected 3.
Doyce

---In MS_Access_Professionals@yahoogroups.com, <DEmbrey@...> wrote :

String or text data must be delimited by either an apostrophe (') or quote marks ("). If you choose to use quote marks, you must double the quote marks since you are concatenating into a string variable.
Apostrophe or single-quote method:
strSQL = "INSERT INTO tblResults ( [Record No], [Form ID], [Pass/Fail], [Score], [Possible], [Date] )" & vbCrLf & _
             "Values ( " & lngRecNo & ", '" & strTestNo & "', '" & strPassFail & "', '" & strScore & "', '" & strPossible & "', #" & dteAward & "#);"
Double-quote method:
strSQL = "INSERT INTO tblResults ( [Record No], [Form ID], [Pass/Fail], [Score], [Possible], [Date] )" & vbCrLf & _
             "Values ( " & lngRecNo & ", """ & strTestNo & """, """ & strPassFail & """, """ & strScore & """, """ & strPossible & """, #" & dteAward & "#);"

Darrell

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, August 27, 2015 5:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Syntax help

Friends,
Can't quite get the syntax right on this SQL statement trying to append a record from a form to a table.
Here is the VBA SQL:
strSQL = "INSERT INTO tblResults ( [Record No], [Form ID], [Pass/Fail], [Score], [Possible], [Date] )" & vbCrLf & _
             "Values ( " & lngRecNo & ", " & strTestNo & ", " & strPassFail & ", " & strScore & ", " & strPossible & ", #" & dteAward & "#);"

It returns:
INSERT INTO tblResults ( [Record No], [Form ID], [Pass/Fail], [Score], [Possible], [Date] )
Values ( 2, 01, Pass, 9, 9, #8/27/2015#);

But I'm getting error 3061. Too few parameters. Expected 1.
Record No is a long
Form ID is text
Pass/Fail is text
Score is tex t
Possible is text
Date is a date.

Doyce

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.