INSERT INTO is failing on simple query

[CarlRostron]

I am using the following code to insert a record into the tblPayments Table
I am confident that the values are correct in the query see screenshot and the table schema is: PaymentID, InvoiceID, PaymentDate, PaymentAmount, Notes

  theQuery = "INSERT INTO tblPayments " _
        & "(InvoiceID,PaymentDate,PaymentMethod,PaymentAmount,Notes) VALUES " _
        & "(" & theInvoiceID & "," & [PaymentDate] & "," & PaymentMethodType & "," & [PaymentAmount] & "," & [Notes] & ");"

Please advise where I might be going wrong?

 

[spikepl]

"Failing"means something to you but not to the readers. What are the symptoms? An error, and if so which? Or simply nothing inserted? Or something else entirely?

Failing, to no avail, it doesn't work .. are all expresions of unhappiness without any useful informational content.

 

[jdraw]

Try it with the numbersign surrounding the date value

theQuery = "INSERT INTO tblPayments " _
& "(InvoiceID,PaymentDate,PaymentMethod,PaymentAmount,Notes) VALUES " _
& "(" & theInvoiceID & ",#" & [PaymentDate] & "#," & PaymentMethodType & "," & [PaymentAmount] & "," & [Notes] & ");"

 

[CarlRostron]

That does the trick, sorry for the lack of info in the first post I am still quite new to posting.

I was hoping you could help me with one other thing though, not sure if I should open in a new thread or not...

In my tblPayments I have 5 records - schema as in the first post. Each of the records has a 1 in the InvoiceID field. When I run the following query only 3 results are returned:

SELECT tblPayments.PaymentID, tblPayments.InvoiceID, tblPayments.PaymentDate, tblPayments.PaymentAmount, tblPayments.Notes, tblPaymentMethod.PaymentMethod
FROM tblPaymentMethod INNER JOIN tblPayments ON tblPaymentMethod.PaymentMethodID = tblPayments.PaymentMethod
WHERE (((tblPayments.InvoiceID)=1));

Essentially I am trying to find all records with InvoiceID=1 so I would have thought all records would be returned?

 

[jdraw]

What happens when you run

SELECT * from tblPayments
where InvoiceId=1

 

[CarlRostron]

I have since discovered that unless all of my fields (notes excepted) are populated it will not attach them in the query and just leaves those records out. I wonder why that is? To get around it, I have validated the INSERT INTO query by ensuring the user populates all fields. Maybe this may not be such a bad idea in practice anyway.

I appreciate you taking the time to help me though.

 

[jdraw]

Lookup Left and Right Joins

see http://www.w3schools.com/sql/sql_join_left.asp