Query to create report but need to use one field over another based on a toggle

[pbDudley]

Hello
I have a form where users must enter parameters. Some of the parameters are stored in our in-house tables as well and aren’t editable from my side.

After this I have a create report button that is tied to a query that uses the saved parameters and the un-edible stored in-house records and makes a report.

I have an issue with one field that I can’t edit (stored in-house oracle tables).
When we initially store it its correct, but it’s supposed to not update, but I see that it does; it’s for a company name. If the data was stored 5 years ago and Company A owned it, it should remain Company A forever in the tables. Well, if Company A is bought by Company B, it’s changing this record to Company B. (This needs to be changed in-house which I can’t do)

I am trying to do a temporary work around for now:

Have the company name that is stored in house displayed:
Company A
Then the user will have to investigate if the company name is still Company A, if it’s not, I have a combo box with a list of the companies to choose from that would store the company name as initial company (INI_COMP)

If I do this, how to I make the report know to use the new record INI_COMP over the current field of just COMP?

I thought I could add a toggle button, then some sort of if/then statement, if toggled use INI_COMP.

But I do not have enough knowledge on how to do this in Access.

[Welshgasman]

You would need to show us the report code?
Or the query that is the source for the report?

[Micron]

Not sure I understand (terms like "in-house" don't mean anything to me/us). If the source table does not have a fixed numeric record ID (e.g. autonumber) for CompanyA, it should. If it does, you should be using that as the key value, not the company name. If it does not, it seems to me that you need a table for the values which can change, and that table would have a fixed numeric ID that you use as they key value. Then it should not matter when the name changes. This is basic normalization 101. Not sure how you are becoming aware that the name changes?

[pbDudley]

Ok, I made it sound too complicated with the background info. I can't upload the database. I have one query (that is used query design) that is tied to a report. Then I make a report with these fields.

I need the report to know to use one record over another somehow. I thought the toggle option on my form, I could somehow use this in the reports design view, on event somehow to choose one field over another

[Micron]

Can you post a couple of records that illustrate the difference and identify the cases when one or the other should be chosen?
If you know ahead of time (e.g. because you're using a form to create/open the report) then you might be able to make use of the Open Args property of the report in report code, but it should be simpler than that. Please use a table (either use the Go Advance option or copy/paste from Excel) for these records.

[Gicu]

In your query add a calculated field COMP_NAME:NZ([INI_COMP],[COMP]) and use that on the report (as the control source for the textbox that now uses COMP).

Cheers,

[pbDudley]

I can’t load any data, but I’ll try to be clearer and then I’ll research more online.

My query has these 2 fields for company:

1. COMPANY
   
   1. This field is usually correct. When I made my report there is only one field for company based on this field in my query. I only need one company field on my report. I can’t override this field.

But I noticed that COMPANY is not always correct. For this reason, I created a 2^nd new field called:

1. INITIAL_COMPANY

If COMPANY is not correct, a user will research and pic the correct company from a combo box and store it in INITIAL_COMPANY.

Now we have the report, with only COMPANY to populate, but I have 2 options:

Use field COMPANY if field INITIAL_COMPANY is blank.

If INITIAL_FIELD is Populated and COMPANY is populated, choose INITIAL_COMPANY

But only one field called company on my report.
I don’t know how to make a report to do this; I’m sure its possible, I just don’t know how.

[pbDudley]

There you go, let me try this. thank you. I have a hard time explaining my issues sometimes

[pbDudley]

This worked:

In your query add a calculated field COMP_NAME:NZ([INI_COMP],[COMP]) and use that on the report (as the control source for the textbox that now uses COMP)

Thank you all very much. I need to get more familiar with these calculated fields

[Gicu]

Glad to hear it works!