compile error: expected end of statement

compile error: expected end of statement

CurrentDb.Execute "INSERT INTO tblMRecord2" (Name,Date,Clinic,Bil_No,Remarks/Reasons,Medical_Leave,Medical_Fees) Values (" & Me.Name & "," & Me.Date & "," & Me.Clinic & "," & Me.Bil_No & "," & Me.Remarks/Reasons & "," & Me.Medical_Leave & "," & Me.Medical_Fees& ");"

Open in new window

Poop HolyAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
And reserved word as field names must be bracketed, and values properly formatted and quoted:

CurrentDb.Execute "INSERT INTO tblMRecord2 ([Name],[Date],Clinic,Bil_No,[Remarks/Reasons],Medical_Leave,Medical_Fees) Values ('" & Me.Name & "',#" & Format(Me.Date, "yyyy\/mm\/dd") & "#," & Me.Clinic & "," & Me.Bil_No & ",'" & Me.Remarks/Reasons & "'," & Me.Medical_Leave & "," & Str(Me.Medical_Fees) & ");"

Open in new window

Also, consider my helper function for this: CSql
0
 
NorieVBA ExpertCommented:
You have some misplaced " and missing spaces, try this.
CurrentDb.Execute "INSERT INTO tblMRecord2 (Name,Date,Clinic,Bil_No,Remarks/Reasons,Medical_Leave,Medical_Fees) Values (" & Me.Name & "," & Me.Date & "," & Me.Clinic & "," & Me.Bil_No & "," & Me.Remarks / Reasons & "," & Me.Medical_Leave & "," & Me.Medical_Fees & ");"

Open in new window

0
 
mbizupCommented:
1. Your TEXT data needs to be delimited by quotes.  (Name data should use chr(34) rather than ', to avoid errors from names like O'Malley)
2. Your DATE/TIME data needs to be delimited by hash marks (#).
3. Your NUMERIC data needs to have  NO delimiters.
4.  Special characters, like "/" should be avoided.  If you do use them, you need to enclose the field/object name in square brackets: [Remarks/Reasons]

Also, line breaks make your code easier to read and maintain:

In the code below, I'm guessing that Name, Clinic, Bil_No, Remarks/Reasons are text and that Date is date/time

CurrentDb.Execute "INSERT INTO tblMRecord2 (Name,Date,Clinic,Bil_No,Remarks/Reasons,Medical_Leave,Medical_Fees) Values ("  _
                         & chr(34) & Me.Name &  chr(34)  & "," _
                         & "#" & Me.Date & "#,"  _
                         & chr(34) & Me.Clinic & chr(34) & ","  _
                         & chr(34) &  Me.Bil_No & chr(34) &  ","  _
                         & chr(34) & Me.[Remarks/Reasons]  & chr(34)  & "," _
                         & Me.Medical_Leave & ","  _
                         & Me.Medical_Fees & ");"

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
as a side comment, to help troubleshoot doing SQL like this, you should do it like this:

Dim strSQL as string

strSQL = "INSERT INTO tblMRecord2 (Name,Date,Clinic,Bil_No,Remarks/Reasons,Medical_Leave,Medical_Fees) Values ("  _
                         & chr(34) & Me.Name &  chr(34)  & "," _
                         & "#" & Me.Date & "#,"  _
                         & chr(34) & Me.Clinic & chr(34) & ","  _
                         & chr(34) &  Me.Bil_No & chr(34) &  ","  _
                         & chr(34) & Me.[Remarks/Reasons]  & chr(34)  & "," _
                         & Me.Medical_Leave & ","  _
                         & Me.Medical_Fees & ");"

CurrentDB().execute strSQL, dbFailOnError

  While it's some extra overhead, in doing it this way you can put a break point or add a stop after the "strSQL ="  statement and inspect the SQL that will be executed before it is. That really helps in debugging a bad SQL statement.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.