0
    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"
3
Contributors
3
Replies
20
Views
1 Week
Discussion Span
Last Post by Shark_1
-1

FROM WHICH TABLE YOU ARE GETTING THESE [AMOUNT],[INTEREST],[TERMS], [TotalInterest]. YOU MUST SPECIFY THEM IN FROM CLAUSE.

YOU CAN USE BETWEEN CLAUSE FOR A RANGE OF DATES IN WHERE CLAUSE.
`WHERE (LOANS.RELEASED BETWEEN @d1 and @d2)

Votes + Comments
Please stop shouting
0

I 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 = sqlQuery & " ORDER BY LOANS.RELEASED ASC"

The FROM needs to close before going to the WHERE.

0

How could you use a string value to a date type field i.e.
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(" "))
Command.Parameters.Add("@d1", OleDbType.Date).Value = xFrom Command.Parameters.Add("@d2", OleDbType.Date).Value = xTo

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.