Query Does Not Work (4 Viewers)

[access2010]

Could I please have a suggestion to fix this Query.

IIf([Transaction_Type]="Sold_$", [CRA_Settlement_Amount] - [Book_Value], Null) and
[CRA_Settlement_Amount]-[Book_Value]

What we are trying to accomplish is;

If the Transaction Type is "Sold_$" then the Transaction_Total is copied from the Book Value to the "CRA_Settlement_Amount"
and the "CRA_Settlement_Amount" amount MINUS the "Book Value" is copied to the "CRA_Realized_GainLoss"

Your suggestions are appreciated.

Should the generated values be “SAVED” in the Table, Form or Report?
Thank you. Nicole

 

[cheekybuddha]

Could I please have a suggestion to fix this Query.

IIf([Transaction_Type]="Sold_$", [CRA_Settlement_Amount] - [Book_Value], Null) and
[CRA_Settlement_Amount]-[Book_Value]

This is not a query - only an expression. Please post the whole SQL of your failing query.

Should the generated values be “SAVED” in the Table, Form or Report?

Usually not, but it can vary depending on circumstance (for which we will probably need more details about the db and its intention)

 

[cheekybuddha]

Is this what you are after?

SELECT
  Broker,
  Symbol_Stock,
  Transaction_Type,
  CRA_Settlement_Amount,
  Book_Value,
  IIf(
    Transaction_Type = 'Sold_$',
    Transaction_Total,
    CRA_Settlement_Amount
  ) AS CRA_Settlement_Amount_Adjusted,
  IIf(
    Transaction_Type = 'Sold_$',
    CRA_Settlement_Amount - Book_Value,
    CRA_Realized_GainLoss
  ) AS CRA_Realized_GainLoss_Adjusted
FROM Tax_Audit_T;

 

[access2010]

This is not a query - only an expression. Please post the whole SQL of your failing query.


Usually not, but it can vary depending on circumstance (for which we will probably need more details about the db and its intention)

Thanks Cheekybuddha for your comments.
I thought that I was writing a Query to copy and calculate data, but it seems that I was wrong.

A Government Department is requesting more information from us and I was hoping that this Expression, which I thought was a query, would allow us to provide them with the data they need.

The query I was trying to create will be run Once A year to generate a report that is submitted to the Government, so that is why I was wondering where or if the data should be stored.

Could you please edit my Expression and make it into a query?

Thank you, Nicole

 

[arnelgp]

as advice use your query as recordsource of your form.
adjust the query if wrong calculation.

 

[access2010]

Is this what you are after?

SELECT
  Broker,
  Symbol_Stock,
  Transaction_Type,
  CRA_Settlement_Amount,
  Book_Value,
  IIf(
    Transaction_Type = 'Sold_$',
    Transaction_Total,
    CRA_Settlement_Amount
  ) AS CRA_Settlement_Amount_Adjusted,
  IIf(
    Transaction_Type = 'Sold_$',
    CRA_Settlement_Amount - Book_Value,
    CRA_Realized_GainLoss
  ) AS CRA_Realized_GainLoss_Adjusted
FROM Tax_Audit_T;

T.H.A.N.K. Y.O.U. Cheekybuddha
Your suggestion works,
Since you like dogs, I will try to find one to pat this week.
Nicole

 

[access2010]

as advice use your query as recordsource of your form.
adjust the query if wrong calculation.

Thank you Arnelgp, I appreciate your suggestion and advice.

Nicole

 

[arnelgp]

imo, you don't need cra_realized_gainloss and cra_settlement_amount.
based on your form, they are Calculated based on the calculation you showed
on the form, therefore, no need to add extra Calculated field (adjusted).

CRA_Realized_GainLoss:IIF(Transaction_Type = "Sold_$", [Book_Value]-[Transaction_Total], 0)
CRA_Settlement_Amount: IIF(Transaction_Type = "Sold_$", [Transaction_Total], Null)

i may not not know accounting much, but what i do know is when you say "Adjustments"
they are "Entered Manually" to offset an account and not a Calculated value.

 

[cheekybuddha]

but what i do know is when you say "Adjustments"

This is (or is nearly what) I made up for an alias ('_adjusted') - it could be anything, even 'jellybean' - but it's necessary because Access doesn't allow to use an alias where the same name of the field is used in its expression.

 

[arnelgp]

If you have looked at the db the OP has posted, there are only 3 fields involved there:

Transaction_Type and Book_Value the other 2 are Calculated field (in the table they are real fields).
As with the the current records on the table the Calculated fields are calculated as in post #8.

so in my opinion, there is no need for IIF() expression on the query/.

 

[cheekybuddha]

so in my opinion, there is no need for IIF() expression on the query/.

Fair enough, but

What we are trying to accomplish is;

If the Transaction Type is "Sold_$" then the Transaction_Total is copied from the Book Value to the "CRA_Settlement_Amount"
and the "CRA_Settlement_Amount" amount MINUS the "Book Value" is copied to the "CRA_Realized_GainLoss"

The OP was asking for a query to display a different value in two columns for a specific situation (Transaction_Type = 'Sold_$') in a query for a different report.

The IIf() satisfies this requirement.

 

[Mark_]

Should the generated values be “SAVED” in the Table, Form or Report?
Thank you. Nicole

You cannot "save" data in a form or report, only in a table.
The real question is "What is the regulatory requirement". Do you need to print and save the report for a period of time? Can you keep an electronic copy for a period of time? Or do your regulators require that the system generate the exact same report numbers when run for the same period?

If the later, you need to make sure there are steps in place that prevent data from being changed after it is posted to the report OR at any time. This means you would then have to have a method for tracking "Adjustments" that affect data that has already been reported to show they should have been in effect previously, but are effective as of the current reporting period.

I'd check with accountant to see exactly what needs to happen to avoid audits.

 

[access2010]

imo, you don't need cra_realized_gainloss and cra_settlement_amount.
based on your form, they are Calculated based on the calculation you showed
on the form, therefore, no need to add extra Calculated field (adjusted).

CRA_Realized_GainLoss:IIF(Transaction_Type = "Sold_$", [Book_Value]-[Transaction_Total], 0)
CRA_Settlement_Amount: IIF(Transaction_Type = "Sold_$", [Transaction_Total], Null)

i may not not know accounting much, but what i do know is when you say "Adjustments"
they are "Entered Manually" to offset an account and not a Calculated value.

Thank you Arnelgp for your code and comments, which I discussed with our Accountant. She agrees with your word “Adjustments” Accounting adjustments are modifications made to financial statements at the end of an accounting period to ensure accuracy and compliance with accounting principles.

 

[access2010]

Fair enough, but


The OP was asking for a query to display a different value in two columns for a specific situation (Transaction_Type = 'Sold_$') in a query for a different report.

The IIf() satisfies this requirement.

Thank you for your follow up information.

 

[access2010]

You cannot "save" data in a form or report, only in a table.
The real question is "What is the regulatory requirement". Do you need to print and save the report for a period of time? Can you keep an electronic copy for a period of time? Or do your regulators require that the system generate the exact same report numbers when run for the same period?

If the later, you need to make sure there are steps in place that prevent data from being changed after it is posted to the report OR at any time. This means you would then have to have a method for tracking "Adjustments" that affect data that has already been reported to show they should have been in effect previously, but are effective as of the current reporting period.

I'd check with accountant to see exactly what needs to happen to avoid audits.

Our accountant requests that we save the data to a Pdf file and Zip the Database to a DVD after all of the Yearly Documents have been printed.

 

[Pat Hartman]

Our accountant requests that we save the data to a Pdf file and Zip the Database to a DVD after all of the Yearly Documents have been printed.

Every application needs a rational backup plan. Daily, weekly, monthly, annually. Then you decide how long to keep each version. You might keep your daily backups for 60 days and your annual backups for 7 years. The way you do backups for Access BE's is different than how you would do a backup for a SQL Server BE.

Be clear and consistent in how you name your backups. Keep in mind that your 2024 year end backup will always have some number of days worth of 2025 data in it depending on how long it takes you to close out the year.