On the Custom Reports page in desktop version 17.6.39, enter the following and click the Run button (note the space within the quotes):
SELECT * FROM CashTransactions WHERE Description = “abc def"
You will get an error message - Syntax error ‘“’ (at index 14) - but this is valid SQL
Now change the statement to the following and click Run again (single quotes this time instead of double quotes):
SELECT * FROM CashTransactions WHERE Description = ‘abc def’
Error message displayed is - Character literal must contain exactly one character (at index 14)
Now change the single quotes back to double quotes and click Run again:
SELECT * FROM CashTransactions WHERE Description = “abc def"
This time there will be no error message for exactly the same statement that was entered the first time.
Column Headings are displayed but no data is shown even if the value exists in the Description field. I used the filter function on the Cash Accounts page for my bank account to verify that.
Note that if the value between the quotes does not contain a space, the syntax error is always flagged and the statement is never executed.
SELECT Description, Amount FROM GeneralLedgerTransactions where Account = “Cash & cash equivalents — ExactAccountName” and Description = “ExactDescription”
Note that you must get the account name and the description exactly right. Things are case sensitive. There is a space before and after the dash in “Cash & cash equivalents — ExactAccountName”
There is no documentation for Custom Reports, so the only thing we users can do is search the forum for examples that work and use them as guides.
I’m reporting two separate problems. I guess I should have noted that, although my accounting knowledge is of the seat-of-the-pants variety, I have many years of programming experience including extensive use of SQL.
The first one is a pure programming issue - using double quotes around a value resulted in a syntax error message in what is a valid SQL statement. As noted, I eventually got past the syntax error by first replacing the double quotes with single quotes (which also resulted in an error), then going back to double quotes. I reported that because I’m hoping the developer would be interested in knowing it.
Since the original post, I have discovered the reason for the second problem of not finding the data I was looking for.
By listing all the CashTransactions table rows, I discovered that the string I was looking for is actually in the Contact Column, not the Description column even though it is shown in the Description Column on my bank account page. I think this is because the string was originally entered in the Payee field when I created a Spend Money transaction. When I amended my SELECT statement to search in the Contact column, the correct data was displayed. Nevertheless, it is confusing to see it listed in the Description column in one place and in the Contact column in another.
Lesson learned: if you are trying to Select by something you entered into the Payee field, look for it in the Contact column not the Description column.