Creating Mod to Change Constantly Used Calculated Fied

[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 the user to enter a start and end date to run the query.

In these 30 or so queries, most of them have the same calculated field 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 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.

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

 

[ranman256]

you only need 1 query. This query runs off a form, the form has the parameters needed for the query.
On the form, frmRpts, is a combo box cboState, that has all the states in it.
The query would read this in the criteria:
select * from table where [state] = forms!frmRpts!cboState

works for dates too,
select * from table where [Date] between forms!frmRpts!txtStartDate and forms!frmRpts!txtEndDate

this way users can customize their data they need.

 

[Rosadocc]

you only need 1 query. This query runs off a form, the form has the parameters needed for the query.
On the form, frmRpts, is a combo box cboState, that has all the states in it.
The query would read this in the criteria:
select * from table where [state] = forms!frmRpts!cboState

works for dates too,
select * from table where [Date] between forms!frmRpts!txtStartDate and forms!frmRpts!txtEndDate

this way users can customize their data they need.

Thanks for the reply. Im not sure if thats what I am trying to get at. Or maybe im reading your response wrong.

These automated queries are pretty simple. All I have to do is usually run them. But sometimes there is a certain parameter that needs to be changed like with the example above, my Store field (which is a calculated IIF function) will now need to be changed for all 30 queries that have that Store field.

Changes like these happen a few times a year but the way I've been doing it is go into each individual saved query in design view, select the field that needs to be changed, and make the change. I was wondering if through a mod, I could just re-do the If statement once to re-define the store field for all queries within the database?

 

[Micron]

One could write a routine that would open each query object in the database container, find a given text string and replace it with another value. Not impossible, but not safe either. You would have to do this again every time a change is necessary, and there is the danger that some wrong segment of the sql would get replaced. What I think you should do is store all the changeable value(s) in a new table with fields for the needed values (e.g. FiscVal) for F12. The next field might be Location for DC, and so on. This table would likely only contain 1 row. You retrieve the value using DLookup. How you pass it to the query I can't say without knowing more of how you're running them. You can call a function in a calculated field or use DLookup. I believe you could call the function from the criteria row in a query as well, but I can't recall ever doing that myself. Again, can't say which approach you should take, but I do believe the table is the way you should go.

 

[Rosadocc]

One could write a routine that would open each query object in the database container, find a given text string and replace it with another value. Not impossible, but not safe either. You would have to do this again every time a change is necessary, and there is the danger that some wrong segment of the sql would get replaced. What I think you should do is store all the changeable value(s) in a new table with fields for the needed values (e.g. FiscVal) for F12. The next field might be Location for DC, and so on. This table would likely only contain 1 row. You retrieve the value using DLookup. How you pass it to the query I can't say without knowing more of how you're running them. You can call a function in a calculated field or use DLookup. I believe you could call the function from the criteria row in a query as well, but I can't recall ever doing that myself. Again, can't say which approach you should take, but I do believe the table is the way you should go.

I guess one way I was thinking is can I create user defined function through a module that defines "store" through an IF function? If so, any help on how to go about doing that? Please forgive me not knowing about this.

 

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

 

[Micron]

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?

Yes, but whether or not this would be the best approach depends on *:

dbo_CSC]![AAC]=

*What are these references, form control names or table/field names, or what?
*If they are control names, where do they get there data from?
*Surely these 30+ queries do not use the same expression?

 

[HiTechCoach]

Thanks for the reply. Im not sure if thats what I am trying to get at. Or maybe im reading your response wrong.

These automated queries are pretty simple. All I have to do is usually run them. But sometimes there is a certain parameter that needs to be changed like with the example above, my Store field (which is a calculated IIF function) will now need to be changed for all 30 queries that have that Store field.

Changes like these happen a few times a year but the way I've been doing it is go into each individual saved query in design view, select the field that needs to be changed, and make the change. I was wondering if through a mod, I could just re-do the If statement once to re-define the store field for all queries within the database?

I urge you to avoid hard coding values that should be looked up.

You would be best serve by using a lookup table.

EXample:

Table fields:
[City]
[FY]
[StoreName]

Example record:

"Washington DC", "12", "Store A"

I would expect hat you already have a city lookup table. Is so, this new table would be a child/sub table.

Depending on your doing/schema, It may be as simple as adding this new lookup table to your query and join on City and FY.

Hopefully you can see that power of Relational data. Spreadsheets just are not good at being a relational databases. Most of the way you do database type things in Excel are not very good ways of doing it in a relational database like Access.