By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

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

P: 6
Hi,

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

strSql = "SELECT tblInvoice.ProjID,tblInvoice.CusID,tblInvoice.Invo iceNo,tblInvoice.InvDate,tblInvoice.Total,tblInvoi ce.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"

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.
May 7 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 5K+
P: 8,434
I believe you just need to insert some spaces around the variable names. For example...
Expand|Select|Wrap|Line Numbers
  1. 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...
Expand|Select|Wrap|Line Numbers
  1. 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"
  2.  
  3. or
  4.  
  5. strSql = "SELECT tblInvoice.ProjID, tblInvoice.CusID, tblInvoice.InvoiceNo, " _
  6.        & "tblInvoice.InvDate, tblInvoice.Total, tblInvoice.Paid, " _
  7.        & "tblInvoice.Balance, tblCustomer.CustomerName " _
  8.        & "FROM tblCustomer " _
  9.        & "RIGHT JOIN tblInvoice " _
  10.        & "ON tblCustomer.CusID = tblInvoice.CusID " _
  11.        & "WHERE tblInvoice.InvDate >= #" & sDate & "# " _
  12.        & "AND tblInvoice.InvDate <= #" & eDate & "# " _
  13.        & "AND tblCustomer.CustomerName='" & cboCustomer.Text & "' " _
  14.        & "AND tblInvoice.ProjID = " & projectID _
  15.        & " 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.
May 7 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.