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

Creating tables and queries

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

Postby kc8ufv » Mon Apr 18, 2016 10:15 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
CASE WHEN `Bond Type` = "Cash/Surety/10% Cash" THEN (`recieved amnt` * (0.9)) - `Fees/Costs Due`)
ELSE (`recieved amnt` - `Fees/Costs Due`) AS `Refund`
Last edited by kc8ufv on Wed Apr 20, 2016 2:20 pm, edited 1 time in total.
OpenOffice 4.1.2 on Windows 7 Pro
kc8ufv
 
Posts: 2
Joined: Fri Apr 15, 2016 4:53 pm

Re: Change multiplier in query

Postby Sliderule » Mon Apr 18, 2016 10:55 pm

kc8ufv wrote: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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

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

Postby kc8ufv » Wed Apr 20, 2016 2:42 pm

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.
OpenOffice 4.1.2 on Windows 7 Pro
kc8ufv
 
Posts: 2
Joined: Fri Apr 15, 2016 4:53 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest