Insert into problem

[helloworld]

Hey guys,

Can anyone tell me what is wrong with this statement? I keep getting a syntax error when the Record Create Identifiers are equal to 'johnsmith' and i get another syntax error when the record create identifiers are equal to something to something like 'John.D'Souza'.

Your help would be much appreciated!

DoCmd.RunSQL "INSERT INTO [Submission Component] ( [Submission Component].[Standard_Submission Identifier], [Submission Component].[Standard_Component Identifier], [Submission Component].[Record Create Date], [Submission Component].[Record Create Stakeholder Identifier], [Submission Component].[Record Effective Date], [Submission Component].[Record Effective Stakeholder Identifier], [Submission Component].[Record End Date], [Submission Component].[Record End Stakeholder Identifier] ) VALUES (" & [Forms]![tester]![Submission_ID] & "," & [Forms]![tester]![Standard_Component Identifier] & "," & [Forms]![tester]![Record Create Date] & "," & [Forms]![tester]![Record Create Stakeholder Identifier] & "," & [Forms]![tester]![Record Effective Date] & "," & [Forms]![tester]![Record Effective Stakeholder Identifier] & "," & [Forms]![tester]![Record End Date] & "," & [Forms]![tester]![Record End Stakeholder Identifier] & ");"

 

[Pat Hartman]

In this context, text strings need to be delimited with quotes. Since your data contains single quotes as in 'John.D'Souza', you must delimit the string with double quotes as in "John.D'Souza". The alternative is to double the single quote between the D and S so that the string is 'John.D''Souza'. I know that a double quote and a single quote look identical in this post but if you copy and paste, you'll see that there are two single quotes side by side in the last example.

You can find many solutions if you search for them.

 

[helloworld]

Hi Pat...thanks for your reply, but how would you include the double quote in the SQL Statement?

PS: I tried searching the forum but did not find anything that specifically regarded my problem.

 

[WayneRyan]

Hello,

http://www.access-programmers.co.uk/forums/showthread.php?t=43183&highlight=double-quotes

Wayne

 

[helloworld]

Thanks for the help Wayne...but one more question...what do I do if I have a date? I tried the # as many people have suggested throughout the forums but that did not help. I enter a date in the form and it turns out to be a totally different date in the tables. Your help would be much appreciated.

 

[WayneRyan]

Hello,

How is your date represented on the form? Is it formatted as a short date?
You should only have to put the # around it. Post your code if you still
have trouble.

Wayne

 

[Pat Hartman]

I'm assuming that your windows system settings are something non-US because if you use US date formats you shouldn't have any confusion. Take a look at this post and see if it sheds any light on your problem:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54145

I also posted something else on the topic in the code forum but I don't have a link so you'll need to search for it.