Syntax error in query expression

[DreamAwake]

I've been staring at this for a while and have had no luck, anyone able to assist?

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set Conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
sql = "INSERT INTO COMMENTS (TXT,MODIFIED,PID,P_SCHEDULE,P_BUDGET,P_SCHEDUAL_COM,P_BUDGET_COM) Values (" & Forms!PROJECT_EDIT!comments & ", Now() , " & Forms!PROJECT_EDIT!PID & " , '" & P_SCHEDULE_VAR & "' , '" & P_BUDGET_VAR & "' , '" & Forms!PROJECT_EDIT!P_SCHEDUAL_COM & "' , '" & Forms!PROJECT_EDIT!P_BUDGET_COM & " ' "
Conn.Execute sql

The error is point it too the end of the document

Thanks in advanced

 

[georgedwilkinson]

Instead of:
Forms!PROJECT_EDIT!comments & ", Now() , " & Forms!PROJECT_EDIT!PID

Try:
Forms!PROJECT_EDIT!comments & #", Date() , "# & Forms!PROJECT_EDIT!PID

Not sure on the "Date", as "Now" might be more appropriate for your needs. The important thing is that you must delimit the value.

Best way to troubleshoot is to type:
Debug.Print sql
after the line that builds the sql. Then copy the value from the immediate window and try it in a query.

 

[DreamAwake]

I tried that but I was still getting the same error. Thank you for your efforts

 

[jal]

Wow. I'm a newbie to Access - can't understand all that.

However, your values clause has an opening parentheses. Does it have a closing one as well?

 

[DreamAwake]

I do have it in there its just the forum replaced it with a emoticon

 

[georgedwilkinson]

Did you put in the "Debug.Print" line? If so, post the sql that the statement builds back here so we can take a look.

 

[DreamAwake]

Hey Guys, I actually found the problem.

If I insert say

Hello World

It works fine, but if I insert

Hello Wo'rld is there a wade to make strings safe my sql inserting.

Cheers,

 

[DreamAwake]

I ended up using using the replace function replace quotes and this resolved my issues.

 

[georgedwilkinson]

Suhweeet!

I don't know how many time an apostrophe in the data has caused me to bang my head for hours. You'd think I'd learn.

Glad you got it fixed!