[Solved] Change multiplier in query (Access2K Back End)

[kc8ufv]

I'm working on converting a database front end from Access 2000 format to OpenOffice Base, and having a problem with a case select formula in a query. What I have is listed below, in the first code block. The data in the "Bond Type" field will be either "Cash/Surety/10% Cash" or "Cash/Surety" on the relevant records. For those where they are "Cash/Surety/10% Cash", we need to multiply the "recieved amount" by .9, and subtract the "Fees/Costs Due", or all other records, we need to just take the difference of those two fields, and want them coming back in a column called "Refund". I've tried putting the calculation within the CASEWHEN statement, tried outside the statement, tried splitting that statement out to what I have in the second code block as well. The code refused to save with what I had in the second code block in place of the formula. When I try opening this query, I get "The data content could not be loaded. Undefined function 'CASEWHEN' in expression." The back end is still in Access format, and it would be best to leave it in that format, as we have multiple front ends accessing that back end.

Code: Select all

SELECT `Case`, `Defendant`, `Surety name`, `Bond Type`, CASEWHEN( `Bond Type` = `Cash/Surety/10% Cash`, 0.9, 1 ) * `recieved amnt` - `Fees/Costs Due` AS `Refund`, `date`, `recieved amnt`, `Fees/Costs Due`, `Refunded` FROM `Bonds` WHERE `Surety` = FALSE AND `Ready4release` = TRUE AND `Forefieted` = FALSE AND `Escheated` = FALSE AND `Refunded` = FALSE ORDER BY `Case` ASC

Code: Select all

CASE WHEN `Bond Type` = "Cash/Surety/10% Cash" THEN (`recieved amnt` * (0.9)) - `Fees/Costs Due`)
ELSE (`recieved amnt` - `Fees/Costs Due`) AS `Refund`

[Sliderule]

The back end is still in Access format, and it would be best to leave it in that format, as we have multiple front ends accessing that back end.

Since, you intend to keep Microsoft Access as your back-end database, you can only use Microsoft Access functions. Therefore, I think you probably will want to use the Access function IIF that is similar to a spreadsheet IF statement.

So, perhaps you want your query to look like this:

Code: Select all

SELECT 
   `Case`, 
   `Defendant`, 
   `Surety name`, 
   `Bond Type`, 
   IIF(`Bond Type` = 'Cash/Surety/10% Cash', ((`recieved amnt` * (0.9)) - `Fees/Costs Due`), (`recieved amnt` - `Fees/Costs Due`)) AS `Refund`,
   `date`, 
   `recieved amnt`, 
   `Fees/Costs Due`, 
   `Refunded` 
FROM `Bonds` 
WHERE `Surety` = FALSE 
  AND `Ready4release` = TRUE 
  AND `Forefieted` = FALSE 
  AND `Escheated` = FALSE 
  AND `Refunded` = FALSE 
ORDER BY `Case` ASC

Explanation:

1. I used the Microsoft IIF function.
2. The database column names are surrounded the `, like ( `Bond Type` ).
3. Text strings are surround by single quotes ' ( like 'Cash/Surety/10% Cash' ) .
4. Care must be used to ensure the parentheses match in the IIF statement.
5. Please review what I wrote in the IIF statement, to make sure the logic is what you want. I tried to just copy what you wrote, and, change the syntax so Access would accept it. You entered the parentheses, and, I think I kept them as you wanted.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[kc8ufv]

Thank you very much, Sliderule. I didn't realize I could still use the Access format in there, let alone being required to use it. That solves the issue. I ended up having to nest an IFF statement, as well, since I forgot it won't perform calculations with a null, and I needed nulls treated as 0 for the purpose of the calculation.