Insert SQL Error for access

[foody]

I keep getting the error Syntax Error in INSERT into statement. I don't know why

This is the sql statement:

Dim intRequest As Integer
Dim intRequest2 As Integer
Dim strSQL As String
Dim intInvoiceNumber As Integer
Dim strInvoiceDate As String
Dim strName As String
Dim intBalanceDue As Double
repeats:

If Paid.Value = -1 Then
intRequest = MsgBox("Now that you have checked this as been paid, do you wish to finalize this and become irreversable? (If you want further help about this click the Cancel button)", vbInformation + vbYesNoCancel)
If vbNo = intRequest Then
cancellation:
Paid.Value = 0
Call MsgBox("Request cancelled.", vbInformation)
Exit Sub
ElseIf vbYes = intRequest Then
' Perform action here
Invoice_Number.Enabled = True
Invoice_Number.SetFocus
intInvoiceNumber = Val(Invoice_Number.Text)
Invoice_Date.SetFocus
Invoice_Number.Enabled = False
strInvoiceDate = Invoice_Date.Text
Bill_To_Name.SetFocus
strName = Bill_To_Name.Text
strSQL = "INSERT INTO Paid-Address (Invoice-Number, Invoice-Date, Name, Balance-Due) VALUES ('" & Val(intInvoiceNumber) & "','" & strInvoiceDate & "','" & strName & "','32')"
Invoice_Number.Enabled = False
MsgBox (strSQL)
DoCmd.RunSQL strSQL
Else
intRequest2 = MsgBox("You have checked this invoice to be paid but if you don't finalize it, it be assumed an error or the cheque has bounced. If you finalize it in the other hand this invoice will be permenantly deleted from this record and entered into the paid summary instead. Do you wish to return back to the request message or do permenant cancellation?", vbInformation + vbYesNo)
If intRequest2 = vbYes Then GoTo repeats
GoTo cancellation
End If
End If

 

[RuralGuy]

Hi foody,
Let's get you a little further along in the process. You do *not* need to set the focus to a control in order to reference it. You have dimentioned intInvoiceNumber As Integer and then reference it in the SQL by getting it's value, which makes no sense and then convert it to a string with the ' ' delimiters: '" & Val(intInvoiceNumber) & "'
NAME is an Access reserved word and will certainly give you unexpected grief until you change it.
You are putting a string value '32' in a field called Balance-Due. I would hope it is a numeric Currency field.
You are putting a string '" & strInvoiceDate & "' into a field called Invoice-Date that I would hope would be a date field.

Make some adjustments and let's see what we have.

 

[foody]

Refusing to work still, HELP

I have did the new changes as you have suggested and this is the result I have made with the new changes:

strSQL = "INSERT INTO Paid-Address (Invoice-Number, Invoice-Date, CustomerName, Balance-Due) VALUES (" & intInvoiceNumber & ",'" & strInvoiceDate & "','" & strName & "',32.32)"

The table Paid-Address called Paid-Address contains these following fields:
PaidAddressID AutoNumber Primary Key
Invoice-Number Number Long Integer
Invoice-Date Text 50
CustomerName Text 50
Balance-Due Number Decimal Size 2 (two decimal places)

Before it executes the DoCmd.RunSQL strSQL above this line I have another line for debugging purposes it is entered MsgBox (strSQL) the messagebox displays this statement:

INSERT INTO Paid-Address (Invoice-Number, Invoice-Date, CustomerName, Balance-Due) VALUES (223, 'Sep 27, 2000', 'Gestion Abacus', 32.32)

I read this message and I find it to be ok, but as soon as the lin executes this command line DoCmd.RunSQL strSQL I get this error message

Syntax error in INSERT INTO statement and the program halts. What is wrong? What do you suggest I should do, thanks in advance?

 

[RuralGuy]

Congratulations! That looks a lot better. I seem to remember, though I can't remember where, that SQL does *not* like single quotes. Change each single quote (') to two double quotes ("").

strSQL = "INSERT INTO Paid-Address " & _
"(Invoice-Number, Invoice-Date, CustomerName, Balance-Due) " & _
"VALUES (" & intInvoiceNumber & ", """ & strInvoiceDate & _
""", """ & strName & """, 32.32)"

 

[foody]

I follow your advise I remain to obtain the syntax correctly as you adviced and I followed your advice to the word and I still get the error message. Is it because the tables are in a relationship? If you have any idea please tell me, I remain to be stuck.

This is the new line of code from your suggestion by the way:

strSQL = "INSERT INTO Paid-Address (Invoice-Number, Invoice-Date, CustomerName, Balance-Due) VALUES (" & intInvoiceNumber & ",""" & strInvoiceDate & """,""" & strName & """,32.32)"

 

[RuralGuy]

Can you post your mdb? Strip it down to leave just enough to demonstrate the problem removing any sensitive information. If it is still too big after zipping you can send it to Rural Guy at Wild Blue dot Net without and spaces.

 

[foody]

I have added you to my MSN contact list (I hope you are ok with that), my name is Fahed Al Daye. I want to talk to you live to solve the problem, and I want to leave sending you the file as the last resort. Please, can you go online as soon as you read this message? My MSN contact list is devastating_battle@hotmail.com, thanks and appreciate it.

 

[RuralGuy]

I'm on line with MSN Messenger