Embedded SQL statement with syntax error

  • Thread starter Thread starter Lowell Moorcroft
  • Start date Start date
L

Lowell Moorcroft

Hello, all -

I'm posting this in both forms programming and queries
newsgroups.

I'm trying to include invoices billed quarterly with
invoices billed monthly if the current month is right
after the end of a quarter (i.e., the number of the month
is 4,7,10, or 1 if the current month is April, July,
October or January)

I'm switching the record set on the invoice report to
reflect this, using an SQL string in the form. I pass the
SQL string from the form to the report through a variable
and that part works fine.

But I'm getting syntex erro messages when introducing the
conditional phrasing into a SQL statement to choose
between including the quarterly billings or not. I suspect
it's either too complicated or I'm trying to do something
in SQL not permissible under the .OpenRecordSet method.
I'm also uncertain of the SQL code itself, but it worked
before trying to bring in the conditional code.

I'm also uncertain about extracting the month in Access
SQL.

Here's the SQL:

Set rs = db.OpenRecordset(IF DatePart('m', Date) In
('4','7','10','1') THEN " & _
"SELECT tblTestInvoice.*, tblPayment.Amount" & _
"FROM tblTestInvoice " & _
"LEFT JOIN tblPayment ON tblTestInvoice.NameID =
tblPayment.PaymentID " & _
"WHERE BudgetCategory = 'Community Station' " & _
"AND tblPayment.Amount >0 " & _
"AND tblTextInvoice.BillingCyle = 'q' OR
tblTextInvoice.BillingCycle= 'm' " & _
"ELSE SELECT tblTextInvoice.*, tblPayment.Amount"
& _
"FROM tblTestInvoice " & _
"LEFT JOIN tblPayment ON tblTestInvoice.NameID =
tblPayment.PaymentID " & _
"WHERE BudgetCategory = 'Community Station' " & _
"AND tblPayment.Amount >0 " & _
"AND tblTextInvoice.BillingCycle= 'm') " & _
"ORDER BY BillCycle")

Thank you if you can help out, and I'm open to an easier
way.

Lowell Moorcroft
 
You cannot use the IF statement as part of the SQL statement. However, you
can accomplish the same result if you dimension a string variable and build
the SQL statement into that variable, and then use that variable as the
"SQL" argument in the OpenRecordset action.


Dim strSQL As String

Select Case DatePart('m', Date())
Case 4, 7, 10, 1
strSQL = "SELECT tblTestInvoice.*, tblPayment.Amount" & _
"FROM tblTestInvoice " & _
"LEFT JOIN tblPayment ON tblTestInvoice.NameID =
tblPayment.PaymentID " & _
"WHERE BudgetCategory = 'Community Station' " & _
"AND tblPayment.Amount >0 " & _
"AND tblTextInvoice.BillingCyle = 'q' OR
tblTextInvoice.BillingCycle= 'm';"

Case Else
strSQL = "SELECT tblTextInvoice.*, tblPayment.Amount" & _
"FROM tblTestInvoice " & _
"LEFT JOIN tblPayment ON tblTestInvoice.NameID =
tblPayment.PaymentID " & _
"WHERE BudgetCategory = 'Community Station' " & _
"AND tblPayment.Amount >0 " & _
"AND tblTextInvoice.BillingCycle= 'm') " & _
"ORDER BY BillCycle;"

End Select

Set rs = db.OpenRecordset(strSQL)
 
Thank you, Ken.

Your solution would appear to work OK, but I'm now
experiencing the problem that the the recordset is not
being set to the SQL statement, i.e.,

Set rs = db.OpenRecordset(strSQL)

produces the error message something like 'Variable or
Block variable not set', even though this worked before.

I did dim the db and rs variables as follows:

Dim db as DAO.Database
Dim rs as DAO.Recordset

and the References shows the DAO 3.6 library as checked.

(I'm remembering all this, since I'm not where I can check
it).

I pasted your SQL statement directly into the recordsource
box (without the VBA line characters, (ie, '& _' and ") and
the report ran fine. I've meticulously reviewed the SQL and
it looks flawless.

Any idea what's going on? Thanks.

Lowell Moorcroft
 
It appears that you need to add one more line of code. Right after the Dim
statements, put this code line:

Set db = CurrentDB()

I'm guessing that you aren't setting the db variable to be the database
object.

Make sure that you close both rs and db at the end of the code and set both
of them to Nothing:

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
--
Ken Snell
<MS ACCESS MVP>

Lowell Moorcroft said:
Thank you, Ken.

Your solution would appear to work OK, but I'm now
experiencing the problem that the the recordset is not
being set to the SQL statement, i.e.,

Set rs = db.OpenRecordset(strSQL)

produces the error message something like 'Variable or
Block variable not set', even though this worked before.

I did dim the db and rs variables as follows:

Dim db as DAO.Database
Dim rs as DAO.Recordset

and the References shows the DAO 3.6 library as checked.

(I'm remembering all this, since I'm not where I can check
it).

I pasted your SQL statement directly into the recordsource
box (without the VBA line characters, (ie, '& _' and ") and
the report ran fine. I've meticulously reviewed the SQL and
it looks flawless.

Any idea what's going on? Thanks.

Lowell Moorcroft
Click to expand...
 
Back
Top