Try
Dim Adapter As New OleDbDataAdapter
Dim Command As New OleDbCommand
Dim xFrom As String = dtpFrom.Value.ToString.Substring(0, dtpFrom.Value.ToString.IndexOf(" "))
Dim xTo As String = dtpTo.Value.ToString.Substring(0, dtpTo.Value.ToString.IndexOf(" "))
sqlQuery = MainQuery & " Select TOP 100 LOANS.LoanID,"
sqlQuery = sqlQuery & " Borrowers.NAME,"
sqlQuery = sqlQuery & " Barangay.BRGYNAME,"
sqlQuery = sqlQuery & " LOANS.RELEASED,"
sqlQuery = sqlQuery & " LOANS.DUE,"
sqlQuery = sqlQuery & " LOANS.AMOUNT,"
sqlQuery = sqlQuery & " LOANS.TERMS,"
sqlQuery = sqlQuery & " [AMOUNT]*[INTEREST]*[TERMS] AS TotalInterest,"
sqlQuery = sqlQuery & " [AMOUNT]+[TotalInterest] AS TotalCollections,"
sqlQuery = sqlQuery & " PAYMENTS.AMOUNTPAID, "
sqlQuery = sqlQuery & " PAYMENTS.DATEPAID "
sqlQuery = sqlQuery & " FROM ((((PAYMENTS "
sqlQuery = sqlQuery & " INNER JOIN LOANS ON PAYMENTS.LoanID = LOANS.LoanID) "
sqlQuery = sqlQuery & " INNER JOIN Borrowers ON LOANS.ID = Borrowers.ID) "
sqlQuery = sqlQuery & " INNER JOIN Barangay ON Borrowers.BRGYNO = Barangay.BRGYNO) "
sqlQuery = sqlQuery & " WHERE (LOANS.RELEASED >= @d1) and (LOANS.RELEASED <= @d2)) "
sqlQuery = sqlQuery & " ORDER BY LOANS.RELEASED ASC"
Command.Parameters.Add("@d1", OleDbType.Date).Value = xFrom
Command.Parameters.Add("@d2", OleDbType.Date).Value = xTo
Command.CommandText = sqlQuery
Command.Connection = MainConnection
Table1.Rows.Clear()
Adapter.SelectCommand = Command
Adapter.Fill(Table1)
PopulateListView(lvGenRecord, Table1)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
i need help please >.< the error shows
syntax error in from Clause if i add the
sqlQuery = sqlQuery & " WHERE (LOANS.RELEASED >= @d1) and (LOANS.RELEASED <= @d2))
sqlQuery = sqlQuery & " ORDER BY LOANS.RELEASED ASC"
Sam_49
0
Newbie Poster
Recommended Answers
Jump to PostI believe you are closing one parenthesis from the "FROM" clause in your where.
Try this :sqlQuery = sqlQuery & " INNER JOIN Barangay ON Borrowers.BRGYNO = Barangay.BRGYNO)) " sqlQuery = sqlQuery & " WHERE (LOANS.RELEASED >= @d1) and (LOANS.RELEASED <= @d2) " sqlQuery …
Jump to PostWhere I used to work, we standardized our date formats on all computers and databases/apps as yyyy-mm-dd. For one thing it made comparing dates trivial.
All 5 Replies
Santanu.Das
125
Santanu Das
Reverend Jim
commented:
Please stop shouting
-3
adam_k
239
Master Poster
Santanu.Das
125
Santanu Das
SouthernImp
0
Newbie Poster
Reverend Jim
4,678
Hi, I'm Jim, one of DaniWeb's moderators.
Moderator
Featured Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.