Geek Answers Handbook

'=' not recognized in SQL statement

I get an error when the equal sign is not recognized. I always check my code in Access to make sure it will work. Does anyone know why this is happening?

Code:

SELECT SUM( IIf(TransactionType = 'Cash', TotalPrice, 0) ) AS TotalCash, SUM( IIf(TransactionType = 'Credit', TotalPrice, 0) ) AS TotalCredit, SUM( IIf(TransactionType = 'Check', TotalPrice, 0) ) AS TotalCheck, SUM( IIf(TransactionType = 'Multiple', IIf(MultiCash = 0, 0, MultiCash), 0) ) AS MultipleCash, SUM( IIf(TransactionType = 'Multiple', IIf(MultiCredit= 0, 0, MultiCredit), 0) ) AS MultipleCredit, SUM( IIf(TransactionType = 'Multiple', IIf(MultiCheck = 0, 0, MultiCheck), 0) ) AS MultipleCheck FROM RECEIPT WHERE ReceiptDate BETWEEN ? AND ?

Error:

 Generated SELECT statement. Error in list of function arguments: '=' not recongized. Unable to parse query text.

EDIT:

Let me tell you what I want to do, not just send the code. This may be another simple solution to this problem. Therefore, I want to withdraw the report from the report viewer in vb.net to tell me the tender that was used between specific dates.

Ex.

 1/20/2013 Transaction Type Amount Cash $100.00 Check $300.00 Credit $1,000.00 MultiCash $1,500.00 MultiCheck $1,500.00 MultiCredit $1,500.00

Something like this in a report viewer. The user will be able to select specific dates. The My Access database table for retrieving information from is shown below.

  RECEIPT ----------- ReceiptNumber (PK) ReceiptDate TotalPrice TransactionType MultiCash MultiCheck MultiCredit TotalTax

Any other ways to do this, maybe?

Thanks for the help.

+4
source share
1 answer

Classes like:

 IIf(MultiCash = 0, 0, MultiCash)

Absolutely nothing. You say: "if multicash is 0, use 0, otherwise use the value ..."

This seems like an overly complicated way to do this. Try this, it will give you the data in a slightly different format, but I suspect that it will be close to what you are after

 SELECT TransactionType, SUM(TotalPrice) AS SumTotalPrice, SUM(MultiCash ) AS MultipleCash, SUM(MultiCredit) AS MultipleCredit, SUM(MultiCheck ) AS MultipleCheck FROM Receipt WHERE ReceiptDate BETWEEN ? AND ? GROUP BY TransactionType

It also has growth potential, which, of course, will handle the new types of payments introduced later.

+1
source

More articles:


All Articles