SQL Problem

[tpenland]

Warning - novice user here....working with an inherited database. I'm trying to add a field so that the query appends one more field than previously. I copy and pasted from the old code and just changed the names but it's not working. Would love some help

This is the old code that works:

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID)" & _


"SELECT #" & AmntDate & "# AS Expr1, " & _
Amnt2Ellis & " AS Expr2, " & _
AmntFull & " AS Expr3, " & _
PropID & " AS Expr5"


This is my new code that gets the error.

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID, BuyerType)" & _
"SELECT #" & AmntDate & "# AS Expr1, " & _
Amnt2Ellis & " AS Expr2, " & _
AmntFull & " AS Expr3, " & _
PropID & " AS Expr5" " & _
BuyerType & " AS Expr6"

[ItsMe]

look at this from the old code
AS Expr5"

look at this from the old code
Expr3, " & _

If you want to add onto the string, you will want to include a coma and some white space. Actually, in this case I am not positive you need the white space.

Anyway, consider something like ...
PropID & " AS Expr5, " & _
BuyerType & " AS Expr6"

Also, it would not hurt to place your string in a variable

Dim strSQL as string
strSQL = "SELECT #" & AmntDate & "# AS Expr1, " & _
               Amnt2Ellis & " AS Expr2, " & _
               AmntFull & " AS Expr3, " & _
               PropID & " AS Expr5"
debug.print strSQL
'Check in the immediate window to find errors in the SQL statement
DoCmd.RunSQL strSQL

Check out this link for more info on checking your SQL via the Immediate Window
http://www.baldyweb.com/immediatewindow.htm

[tpenland]

I'll give it a shot, thanks!

[ItsMe]

OK, let us know how things go. I made an edit to my post where I made a typo in the code. I changed SQL to strSQL. I can check back in tomorrow.

[tpenland]

That worked, thanks!!!

[ItsMe]

OK good news. Oh, and welcome to the forum!

[tpenland]

Spoke to soon! It let me save the code but still gives me an error when running. Here's what I have. I highlighted what the debugger highlights. CompType in that seems to be the problem but not sure why. Thanks!

Private Sub CreatePayments()
DoCmd.SetWarnings False


txtDownPayment.SetFocus
If txtDownPayment.Text > 0 Then
DoCmd.OpenQuery "qryUpdateDownPayment"
End If


txtFees.SetFocus
If txtFees.Text > 0 Then
DoCmd.OpenQuery "qryUpdateFees"
End If


Dim i As Integer
Dim totnum As Integer
Dim Amnt2Ellis As Currency
Dim AmntFull As Currency
Dim AmntPerc As Double
Dim AmntDate As Date
Dim PropID As Integer
Dim CompType As String




txtNumberPayments.SetFocus
totnum = txtNumberPayments.Text


txtMonthlyPayments.SetFocus
AmntFull = txtMonthlyPayments.Text


txtMPPerc.SetFocus
AmntPerc = txtMPPerc


txtFirstPaymentDate.SetFocus
AmntDate = Format(txtFirstPaymentDate.Text, "Short Date")


PropID = [Form_REDEMPTION INPUT].PropertyID


comboCompType.SetFocus
CompType = comboCompType.Text


Amnt2Ellis = AmntFull * txtMPPerc


Dim strSQL As String






For i = 1 To totnum


If i = 1 Then

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID, CompType)" & _
"SELECT #" & AmntDate & "# AS Expr1, " & _
Amnt2Ellis & " AS Expr2, " & _
AmntFull & " AS Expr3, " & _
PropID & " AS Expr5, " & _
CompType & " AS Expr6"

'MsgBox strSQL


Else

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID, CompType )" & _
"SELECT #" & DateAdd("M", i - 1, AmntDate) & "# AS Expr1, " & _
Amnt2Ellis & " AS Expr2, " & _
AmntFull & " AS Expr3, " & _
PropID & " AS Expr5, " & _
CompType & " AS Expr6"

'MsgBox strSQL

End If


Next i




[Form_REDEMPTION INPUT].frmPartialPay.Requery
DoCmd.SetWarnings True
End Sub

[ItsMe]

The keyboard shortcut to open the Immediate Window is Ctrl+G. Use the debug.print rather than the msgbox.

Take a look at post #2. You need to assign your SQL statement to a variable

strSQL = "SELECT #" & AmntDate & "# AS Expr1, " & _
               Amnt2Ellis & " AS Expr2, " & _
               AmntFull & " AS Expr3, " & _
               PropID & " AS Expr5"

Then you can debug.print the variable

debug.print strSQL

Then you can copy the resulting SQL from the immediate window. You can test it in a Query Object and or share it with us here.

Also, you can use your variable in your Docmd method

DoCmd.RunSQL strSQL

[tpenland]

Ok so pulling my hair out. If I saved a database could I send to you to look at? Thanks so much!

[davegri]

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID, CompType) " & _

Will cause Syntax Error without the space.

[ItsMe]

If you make a copy, remove sensitive information, remove extra forms, reports, objects, compact and repair, compress into folder, then you can upload it here. We can take a look.

[rpeare]

why are you aliasing field names in an insert query the syntax would be

insert into TABLENAME (FIELDNAME1, FIELDNAME2, FIELDNAME3) values (x, y, z)

You don't need to alias the field names at all

Also, you would only use the SELECT statement if you were appending records from another data source into your table.

In your code you are appending a single record at a time with distinct value

DoCmd.RunSQL "INSERT INTO tblPartialPay ( PaymentDueDate, AmntDue, FullPaid, LoanID, CompType) VALUES (#" & AmntDate & "#, " & Amnt2Ellis & ", " & AmntFull & ", " PropID & ", " & CompType & ")"

[tpenland]

Attached is the database. It will give 2 errors upon opening but it's just because I deleted forms that weren't relevant to my current issue.
Database4.zip

This is screenshot of what I need. So much thanks!!!

[ItsMe]

Sorry I missed replying to this, earlier. You are using CompType. However, I do not see it declared anywhere in your code and it is not initialized. In other words, CompType is not anything.

Additionally, CompType is not a column name in tblPartial Pay. So even if you get het syntax correct, it is not going to work because there is not a column to append to and there is not a value for CompType, the variable.

[tpenland]

How would I fix this - declare it and initialize it. In this database the field is called Company. That's the name of the column in tblPartial Pay. Thanks!!!