Query Does Not Work

[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.

 

[GPGeorge]

You can't have too many backups.

 

[Isaac]

In a really ideal situation, (IMHO), you're at a company where you don't have to worry much about backups as an Access dev. Because? Your back end data is stored in either Access, or something like SQL Server - both of which are someone else's responsibility to back up and they usually do it a lot better than we could ever hope to with our one-off scripts to do this or that with copies.

In almost every company I have worked, there have been effective backups on the network shares, such that restoring yesterdays back-end Access file would be a snap - and even 10x as sophisticated when it comes to SQL Server, which the DBA's will be backing up like there's no tomorrow, hopefully.

Even the small businesses I have consulted have usually used an IT service that came with quite a bit of backing up and restore options.

That said, as Doc once said I'm a belt+suspenders kind of guy, and I still wrote scripts to export backup files of data in some scenarios. Until I got used to the fact that it was someone else's whole job..

Plus, everything I said was aimed at the BACK END data.
For the front-end dev iterations, I do store copies of backups but only because it is easier for me to decide what point-in-time to back up and restoring the last version is a simpler affair.

 

[Pat Hartman]

You can't have too many backups.

But in this case you can unless you get rid of a previous "final" year end backup. Which is the final, final? Remember, every time you open an Access database, Access changes the date of it whether you update anything or not.

The "final" you produced Jan 10, will include 10 days of January data but the "final" produced Jan 15 will have 15 days. If you don't delete the Jan 10 final because it was superseded by the Jan 15 "final", you could end up with a problem down the road.