Syntax Error in From Clause. Selecting between two Dates

[Sam_49]

    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"

[Santanu.Das]

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)

[adam_k]

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.

[Santanu.Das]

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

[SouthernImp]

As with most developers we've had enormous trouble with dates but we have a simple rule; Do not compare dates with anything other than the DateDiff() function! Another thing we always do is format dates as dd-MMM-yyyy for example; 13-Jun-2018 - we're fortunate in that every server we use, it's always using English.

So you should use

DateDiff(d, LOANS.RELEASED, @d1) >= 0 AND DateDiff(d, @d2, LOANS.RELEASED) >= 0

• you then don't have times to worry about too.

The important thing for your query, and to eliminate the error, is to format your dates correctly and if you are using an English server, use my suggestion above.

[Reverend Jim]

Where 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.