Embedded SQL producing syntex error message

  • 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 error messages when introducing the
conditional phrasing into an 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
 
No, you cannot use the If statement in JET Query / SQL.
IF statement is a VBA statement, not JET.

Try something like (***untested*** SQL String only):

SELECT TI.*, P.Amount
FROM tblTestInvoice AS TI
LEFT JOIN tblPayment AS P
ON TI.NameID = P.PaymentID
WHERE (BudgetCategory = 'Community Station')
AND (P.Amount >0)
AND ( (TI.BillingCycle= 'm')
OR
( (TI.BillingCyle = 'q')
AND
(DatePart('m', Date()) In (1, 4, 7, 10))
)
)
ORDER BY TI.BillingCycle

You use 2 different spellings in your String:
BillingCycle and BillCycle. Check the spelling and adjust
if required.

Perhaps, you should add the Table qualifier for the Field
BudgetCategory used in the above SQL String.

I used Aliases to shorten the SQL String but you don't
have to use Aliases if you don't want to.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top