Could I create a Mod to edit calculated fields in a database?

[Rosadocc]

Hi All, I am not very experience in VBA or creating mods in Access but I hope I can get some opinions and advice on an idea I have.



I have a database with several (approx 30) saved queries. Most of these queries are used weekly to pull data based on date parameters. So they are pretty much automated queries that prompt me to enter a start and end date to run the query. Pretty Simple.

But in these 30 or so queries, most of them have the same calculated fields in each query. For example one calculated field to calculate a "Store Name" to be "Store A" if its location (city) is "D.C." and the fiscal year is greater than "FY12" would read like:
Store Name: IIf(([City]="Washington DC") And ([FY]>"12"),"Store A"

But sometimes this calculated field may need to change, such as FY>13, or the city may change and the IIF function will now need to read like:
IIf(([City]="Chicago") And ([FY]>"13"),"Store A"

So my question is, instead of going into each individual saved query to change this calculated field in design view, would it be possible to just create a mod that i can go into once and make the change this calculated field for all saved queries within the db?

[June7]

That would be VBA using QueryDefs to edit the query design. Don't recommend this.

I think instead of the calculated field, you need to apply filter criteria or build report that groups the data. But really need to know more about data structure and business requirements to offer specific guidance.

Should work with forms and reports and not directly with tables and queries for dynamic output.

[Rosadocc]

The database is pretty much a collection of sales. So it has sell price, cost price, product number, sales date, and which store made the sale, the state the store is located, etc. So a simple automated weekly query I have saved is Sales for all stores in California for last week.

So for something like this, it would be easy, just pull down the fields needed from the dbo (sell price, store name, state, and sale date) with the criteria of date for last week and for criteria of "California" for the State field.

where it gets frustrating is that due to reasons out of my control, sometimes certain parameters need to be set in capturing the data. For example, in the query mentioned above, management now decides that any sale that goes to store A will actually go to Store C. And any sale that is actually in Store B will now be Store D. And this is how it will be for the next few months.

So what I have been doing is going in and creating a calculated field for each query that has a Store Field with an IIF function so the sales can go to the appropriate store (store A to store C and store b to store D).

So instead of having to go in every few month to each query and edit this IF expression, i was hoping there is something easier I can do and just worry about making the change once and have it apply to all queries.

Sorry for the long post, just hope I can provide how the data is structured.

[June7]

How many cities?

Better options:

1. build a table that has this 'alias' info and include that table in the report RecordSource query

2. build a VBA function to do this 'alias' conversion and call the function from query or report textbox

[Rosadocc]

How many cities?

Better options:

1. build a table that has this 'alias' info and include that table in the report RecordSource query

2. build a VBA function to do this 'alias' conversion and call the function from query or report textbox

Thanks! I think option 2 is kind of what I was aiming for. Only thing is getting started on a VBA function. Pretty new to this. But glad to know that it can be done.

[Rosadocc]

This is the real life IIF expression I use to help define the Store field in my query. I use it in the field cell:


Store: IIf(([dbo_CSC]![AAC]="FB2039") And ([dbo_CSC]![FY]>"12"),"Tinker",IIf(([dbo_CSC]![AAC]="N65886") And ([dbo_CSC]![LIIDSTORE]="AG"),"Jax Mega",IIf(([dbo_CSC]![AAC]="N65886") And ([dbo_CSC]![LIIDSTORE]="MN"),"Jax Mega",IIf(([dbo_CSC]![AAC]="N65886") And ([dbo_CSC]![LIIDSTORE]="EF"),"Jax Mega",IIf(([dbo_CSC]![AAC]="FB2065") And ([dbo_CSC]![LIIDSTORE]="TK"),"WARNER ROBINS",IIf([dbo_CSC]![INPUTSOURCE]="MCS","Virtual Store",[Store_Name]))))))


This pretty much if an If function saying that if its a certain AAC (customer number) and the LIIDSTORE (store code), it belongs to the defined store (Tinker, Jax Mega, etc).


But sometimes I need to change the AAC to another number in the expression. This means I have to go into every query that has this IIF expression and change the AAC numbers.


But if there is a way to create a user defined function or mod or anything where I would only have to change this function once, and not have to change it in every query?

[June7]

So all 5 ACC parameters would have to be changed and each would be different. Ugh! Options:

1. table with this alias info and include table in query

2. 5 controls on a form for input of the parameters and the query references those controls: [dbo_CSC]![AAC]=Forms!formname!tbxACC1