ACCESS FEED: Re: [MS_AccessPros] Syntax help

Kamis, 27 Agustus 2015

Re: [MS_AccessPros] Syntax help

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@roadsysinc.com [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.

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Visit Your Group