What's wrong with this sql query which uses a date range?(I'm getting a syntax error)

[ruvi]

Hi,

I am using Vb 6 and Access.
I am getting syntax error in the following sql query.

strSql = "SELECT tblInvoice.Proj ID,tblInvoice.C usID,tblInvoice .InvoiceNo,tblI nvoice.InvDate, tblInvoice.Tota l,tblInvoice.Pa id,tblInvoice.B alance,tblCusto mer.CustomerNam e FROM tblCustomer RIGHT JOIN tblInvoice ON tblCustomer.Cus ID=tblInvoice.C usID WHERE tblInvoice.InvD ate >= #" &sDate& "# AND tblInvoice.InvD ate <= #" &eDate& "# AND tblCustomer.Cus tomerName='" & cboCustomer.Tex t & "' AND tblInvoice.Proj ID=" & projectID & "AND Balance >0"

Can anyone one point out whats the mistake I am making here?
When I substitute dates instead of the variables, there is no error and the query executes perfectly.

Thanks for ur time.

[Killer42]

I believe you just need to insert some spaces around the variable names. For example...

InvDate >= #" & sDate & "# AND

Also, just for readability, I would recommend chopping up such long lines. That's why VB has a continuation character (_).

For example, see which of these is easier to see...

strSql = "SELECT tblInvoice.ProjID,tblInvoice.CusID,tblInvoice.InvoiceNo,tblInvoice.InvDate,tblInvoice.Total,tblInvoice.Paid,tblInvoice.Balance,tblCustomer.CustomerNam e FROM tblCustomer RIGHT JOIN tblInvoice ON tblCustomer.CusID=tblInvoice.CusID WHERE tblInvoice.InvDate >= #" & sDate & "# AND tblInvoice.InvDate <= #" & eDate & "# AND tblCustomer.CustomerName='" & cboCustomer.Text & "' AND tblInvoice.ProjID=" & projectID & "AND Balance >0"

[b]or[/b]

strSql = "SELECT tblInvoice.ProjID, tblInvoice.CusID, tblInvoice.InvoiceNo, " _
       & "tblInvoice.InvDate, tblInvoice.Total, tblInvoice.Paid, " _
       & "tblInvoice.Balance, tblCustomer.CustomerName " _
       & "FROM tblCustomer " _
       & "RIGHT JOIN tblInvoice " _
       & "ON tblCustomer.CusID = tblInvoice.CusID " _
       & "WHERE tblInvoice.InvDate >= #" & sDate & "# " _
       & "AND tblInvoice.InvDate <= #" & eDate & "# " _
       & "AND tblCustomer.CustomerName='" & cboCustomer.Text & "' " _
       & "AND tblInvoice.ProjID = " & projectID _
       & " AND Balance > 0"

Oh, by the way. For your date range, you might want to consider using the BETWEEN operator. It makes for more easily readable code.