Where should I keep a single variable (Mileage Reimbursement)

  • Thread starter Thread starter TraciAnnNeedsHelp
  • Start date Start date
T

TraciAnnNeedsHelp

I have several queries that calculate Mileage Reimbursement. As you know,
this rate only changes about once a year.

Rather than editing each query by changing the calculated amount, where
should I store this variable so I only have to edit it once? What is "best
practice"?

Thank you for all your help!
 
Traci,

It did change twice last year but to respond to your question:

Use a global variable to store the rate (curMilReimb).
Your queries will not recognize a global variable but they will recognize a
function.
Within your queries use a function such as GetMilReimb() everywhere you need
the rate.

Public Function GetMilReimb() As Currency
GetMilReimb = curMilReimb
End Function

I don't know what will qualify and "Best Practice" but this will suffice as
"My Practice".

Jack Cannon
 
"Your Practice" looks "best" to me.

However, I'm a little green on this and need a little more assistance. I
apologize for my lack of experience. My knowledge is limited to building
simple dbases w/ tables and queries with very little expressions. I have yet
to build a "Module" but I've looked at them and understand their logic.

You said to "use a global variable to store the rate (curMilReimb)"

Do I create a new Module and enter your script word for word:
Public Function GetMilReimb() As Currency
GetMilReimb = curMilReimb
End Function

Where do I enter the current rate? (.0585)
How do I "load" a global variable when I start the database so when I use
GetMilReimb() in a query, it recognizes it?

I'm sorry I have so many questions.

Thanks again!
Traci
 
Traci,

Right now let me just address the question, "Where do I enter the current
rate? (.0585)"

That is a good question and it is the starting point. Here are some ideas.

1. Use an InputBox that is displayed every time you open the database.
Once the rate is entered into the window and accepted, use the value to load
the global variable.
This is the easiest to implement but is error prone and very irritating.
It certainly will not work in a Multi-User environment because it depends on
every user typing in the same value.

2. Enter the value on your initial form.
This is very similar to # 1.

3. Put the value in the command line (/cmd).
You can then read the value within your application and load the global
variable.
This ensures that the value is always correct but it does require the icon
on all user machines (not the application) to be updated everytime the value
changes.
The advantage is that you do not have to enter the value everytime you open
the application.

4. Store the value as a "Public Const" instead of a global variable
Public Const curMilReimb = 0.0585
This has the advantage of always being correct for all users.
The disadvantage is that you will have to update the application every time
the value changes.

5. Store the value in a BackEnd table that only the Administrator can update.
In this case you would not load a global variable but instead the queries
can access the value directly.
The Administrator can update the value every time the rate changes.

6. Store the value in a BackEnd table same as # 5 but include a starting
date for the rate.
Every time the rate changes then you would enter a new row that includes the
starting date and new rate.
This has the advantage of allowing the user to go back and recompute
archived records.
It has the disadvantage of being the most complex and since you said you
were "a little green", you might not want to tackle this one until you
develope a little higher skill level.

Pick one methodology and perhaps we can proceed from there.

Jack Cannon
 
(Note: I mis-entered the current rate but it is now corrected whenever it is
referenced)

4. Store the value as a "Public Const" instead of a global variable Public
Const curMilReimb = 0.585 .This has the advantage of always being correct for
all users.

The disadvantage is that you will have to update the application every time
the value changes.

5. Store the value in a BackEnd table that only the Administrator can update.

In this case you would not load a global variable but instead the queries
can access the value directly. The Administrator can update the value every
time the rate changes.

6. Store the value in a BackEnd table same as # 5 but include a starting
date for the rate.

Every time the rate changes then you would enter a new row that includes the
starting date and new rate. This has the advantage of allowing the user to go
back and recompute archived records.

It has the disadvantage of being the most complex and since you said you
were "a little green", you might not want to tackle this one until you
develope a little higher skill level.

Pick one methodology and perhaps we can proceed from there.


You are EXCELLENT at detail. Thank You!!!

Because this is used in a network environment with multi-users the first
three are immediately eliminated. #'s 4 and 5 would both provide the solution
as long as we are not nearing a time when there will be a change. I can see
the chaos when a new rate is to take place in the middle of a reports date
range.
Although I'm "green" I am not "squeamish". I am willing to tackle anything
you are willing to walk me through. I just don't want to be a pest.

If you are game for #6 I'll try it. Otherwise, let's go with #5.

With Sincere Thanks!
TraciAnn
 
TraciAnn,

Be advised that the current rate is 0.55 .

http://www.irs.gov/govt/fslg/article/0,,id=200748,00.html

The 0.585 rate was effective only during the period July 1, 2008 through Dec
31, 2008 .

Let's start with # 5. It is a prerequisite to # 6 anyway.

1. Make a backup of your present application should a disaster occur.

2. Construct BackEnd table (tblMilRates) as follows:

MilRateID as an AutoNumber
StartDate as Date/Time Format it to "Short Date"
MilRate as Currency
(Note: You can leave the "Decimal Places" in MilRate to "Auto" if you like.
However if you like to see all the digits then set the "Decimal Places to 3
or 4.)

3. Load one row only with either:

7/1/2008, 0.585
or
1/1/2009, 0.55
depending on which period satisfies your objective.
At this time we will not be using the StartDate.

4. Link to it through your FrontEnd application.

5. Create a module named "Public Variables"
In that module insert the statement:

Public curMilRate as Currency

6. Create a second module or use any existing module
(I like to keep my Public Variables separate from everything else because it
makes them much easier to find.)
In that module insert the following procudure:

Public Function GetMilRate() As Currency
GetMilRate = curMilRate
End Function

7. Within the Form_Open event of your initial form insert the following
statement anywhere:

curMilRate = DLookup("[MilRate]", "tblMilRates")

8. Within all your queries change all instances of 0.585 (or whatever) to
GetMilRate() .
It will probably look something like Reimb: [Number of Miles] * GetMilRate()

9. Execute the appropriate function and then report to us the text of the
inevitable Error Message.

Jack Cannon
 
Again, You're AWESOME!

I have a couple challenges with executing all of the tasks below. A little
background is necessary.

I am using Access to write queries that accesses mostly SQL tables and
views. The current Access database has two tables but I am fine building as
many as necessary. So far I am working with queries that I have inherited and
I'm trying to fix/improve them before moving on to adding a user interface
with forms, menus and reports.

Nobody is using forms or reports. The only object we currently use is
queries, a couple tables and mostly SQL tables and views (which we cannot
touch).

That said, I have completed all your details except where noted below:

1. Done

2. Done

3. Done (1/1/2009, 0.55)

4. Link to it through your FrontEnd application.

Since I need to call the value in a query, I'm not sure how to do this. I
assume you mean that I should place the ID in another table as a foreign key,
but I am unable to access any of the SQL tables from where the rest of the
data is coming.

5. Done

6. Done: Saved as basCurMilReimb

7. Within the Form_Open event of your initial form insert the following
statement anywhere:

curMilRate = DLookup("[MilRate]", "tblMilRates")

Is there another place I can put this for now? I do want to build a
friendlier user interface but for right now everyone just opens the database
and runs their specific query from the Queries List in the right navigation
pane.

8. Within all your queries change all instances of 0.585 (or whatever) to
GetMilRate() .

It will probably look something like Reimb: [Number of Miles] * GetMilRate()

I assume this wont do me any good until I have the curMilRate loading from
an event.

9. Execute the appropriate function and then report to us the text of the
inevitable Error Message.

HehHeh...I hope to surprise you ;)

Gratefully!
TraciAnn
 
TraciAnn,

If I understand you correctly, your FrontEnd is Access and the BackEnd is SQL.
I further understand that you cannot touch the SQL stuff.
If this is so then I am confused as to how you accomplished item 2.

Is tblMilRates in the BackEnd or the FrontEnd? If it is in the BackEnd then
you had to touch SQL and you must link to it in the same manner that you
presently link to the SQL tables.

If tblMilRates is in the FrontEnd then you do not need to link to it but you
will have to redistribute the FrontEnd application whenever you make any
changes to the data in tblMilRates or any other table that you have in the
FrontEnd.

It is also possible to have two BackEnds - One SQL and the other Access. We
can address that point later if you so desire.

Let me continue under the assumption that tblMilRates is in the FrontEnd.
If so, then Item 4 is Done.

"Since I need to call the value in a query, I'm not sure how to do this. I
assume you mean that I should place the ID in another table as a foreign key,
but I am unable to access any of the SQL tables from where the rest of the
data is coming."

No. You do not place the ID in another table.
In fact the ID is not being used at this time.

In the absence of an initial form you can accomplish Item 7 by implementing
an Autoexec Macro. I will take you through the steps.

A. In the module that you created in the previous Item 6 (basCurMilReimb),
Insert the following function:

Public Function LdCurMilRate()
curMilRate = DLookup("[MilRate]", "tblMilRates")
End Function

B. Go to the database window, select Macros and click on New. You will see
an "Action" column with a dropdown menu.

C. Select "RunCode" under the dropdown menu. A "Function Name" box will
appear near the bottom of the column.

D. Type the following text into the box:

LdCurMilRate()

E. Save the Macro under the name "Autoexec"

The Autoexec Macro will execute whenever your application is opened unless
you bypass it by holding the shift key down while you open the application.

You are now ready to proceed with Items 8 and 9

Jack Cannon


TraciAnnNeedsHelp said:
Again, You're AWESOME!

I have a couple challenges with executing all of the tasks below. A little
background is necessary.

I am using Access to write queries that accesses mostly SQL tables and
views. The current Access database has two tables but I am fine building as
many as necessary. So far I am working with queries that I have inherited and
I'm trying to fix/improve them before moving on to adding a user interface
with forms, menus and reports.

Nobody is using forms or reports. The only object we currently use is
queries, a couple tables and mostly SQL tables and views (which we cannot
touch).

That said, I have completed all your details except where noted below:

1. Done

2. Done

3. Done (1/1/2009, 0.55)

4. Link to it through your FrontEnd application.

Since I need to call the value in a query, I'm not sure how to do this. I
assume you mean that I should place the ID in another table as a foreign key,
but I am unable to access any of the SQL tables from where the rest of the
data is coming.

5. Done

6. Done: Saved as basCurMilReimb

7. Within the Form_Open event of your initial form insert the following
statement anywhere:

curMilRate = DLookup("[MilRate]", "tblMilRates")

Is there another place I can put this for now? I do want to build a
friendlier user interface but for right now everyone just opens the database
and runs their specific query from the Queries List in the right navigation
pane.

8. Within all your queries change all instances of 0.585 (or whatever) to
GetMilRate() .

It will probably look something like Reimb: [Number of Miles] * GetMilRate()

I assume this wont do me any good until I have the curMilRate loading from
an event.

9. Execute the appropriate function and then report to us the text of the
inevitable Error Message.

HehHeh...I hope to surprise you ;)

Gratefully!
TraciAnn
Click to expand...
 
Since you're dealing with queries, I'd create table that contains the rate
in order to pull it into your queries.

You can either have only a single row in that table, so that no join is
required, or you can have an Effective Date and Expiry Date and join based
on the current date.

Note that this would allow you to have different amounts for different
departments, should that be a requirement.
 
YES!!!! (Dancing throughout the office) !!!!

Jack, it worked! The "inevitable error" was #2250 which I believe is the
security thing, but after I told Access to allow the content, the query
worked wonderfully.

I know we aren't through yet but I want to be sure we also answer the
question of, "How do I make sure the users are allowing the macro to run when
they start Access? Right now they are used to answering "No" each time they
get the message.

Ok. I'm ready for the next step.

TraciAnn
 
I have several queries that calculate Mileage Reimbursement. As you know,
this rate only changes about once a year.

Rather than editing each query by changing the calculated amount, where
should I store this variable so I only have to edit it once? What is "best
practice"?

Thank you for all your help!
Click to expand...

Bear in mind that even though this is single value, you are entering
records that are reflecting that value as it was at the time.

If the rate changes twice (or even once a year), do you when you go
back and review historical records want the calculation to reflect the
current value or the value in effect at the time the other data was
created?

Let me say it a different way.

Assume the rate is 5c in 2008 and 7c in 2009. You have records showing
reimbursemnt of 1,000 miles in 2008 and 1,000 miles in 2009. When you
review the records, do you want the records to show that you
reimbursed $50 in 2008 and $70 in 2009 or is is acceptable to have a
report showing that mileage reimbursement in both years were
equivalnet?
 
TraciAnn,

There is something wrong. I am not sure what it is.

The Macro should execute automatically without asking the user to allow it.

I do not have a library of error messages so I am unable to look up #2250.
Can you post the text of the error message?

Jack Cannon
 
My fault...#2950

Macro Name: AutoExec
Condition: True
Action Name: RunCode
Arguments: LdCurMilRate()
Error Number: 2950
 
TraciAnn

The following URL addresses the problem you are encountering if you are
using AC2007.

http://support.microsoft.com/kb/931407

If you are using AC2007 and you are able to correct the problem then great.
However if that is not the case we will probably have to eliminate the
Autoexec Macro and accomplish your goal by a different method (which is not a
problem).

Jack Cannon
 
I dev in '07 but most users use '03. I save all work in '03 format.

As previously mentioned, the function works great (on my machine) when I
trust the content for "this session". Because the db is on a network drive I
don't want to trust the drive.

So, we should probably use option 2.

TraciAnn
 
Traci:

The rate, like all data, must be a value in a row in a table. So take
Doug's advice; he's given you by far the best solution, which involves
no variables or functions. Create a table MileageRates with columns
DateFrom, DateTo and Rate. As the rate changes enter a new row in the
table with the date the new rate comes into effect and the rate. At
the same time enter the date of the previous day as the DateTo value
in the previous record. In your query pull in the rate for the date
of the trip, e.g.

SELECT EmployeeID, TripDate,
EndMileage – StartMileage AS Mileage,
(EndMileage – StartMileage) * Rate AS Reimbursement
FROM EmployeeTrips, MileageRates
WHERE EmployeeTrips.TripDate BETWEEN
MileageRates.DateFrom AND NZ(MileageRates.DateTo,DATE());

The way this query works is that it subtracts the start mileage per
trip from its end mileage to give the mileage for the trip (if your
table stores the mileage itself you don't need to do this of course).
It then multiplies this by the rate from the relevant row in the
MileageRates table to give the reimbursement amount, getting the rate
by restricting the row returned to that where the trip date is between
the 'from' and 'to' dates in the MileageRates table. If it’s the
current rate and there is no value for the 'to' date then the Nz
function is used to return the current date (supplied by the built in
Date() function) in place of the Null.

This won't match your scenario precisely of course, but hopefully
illustrates the principles involved, and will allow you to build your
own query. Post back if you need any more clarification.

Ken Sheridan
Stafford, England
 
Okay then let's try this.

1. Delete the Autoexec Macro in its entirety.

2. Delete the LdCurMilRate function in its entirety:

Public Function LdCurMilRate()
curMilRate = DLookup("[MilRate]", "tblMilRates")
End Function

3. Change the Function GetMilRate to the following:

Public Function GetMilRate() As Currency
GetMilRate = DLookup("[MilRate]", "tblMilRates")
End Function

Jack Cannon
 
Ken, Thank you for the encouragement and the detail in your post. I would
like to try this as an option but the query part is confusing me.

To test your solution I created a table as you suggested (tblMileageRates)
with a total of 4 columns (RateID, StartDate, EndDate and Rate).

Since some records are still being entered for travel in 2008 I have entered
2 records:
1/1/08 - 12/31/08 .585
1/1/09 .55

I have an existing query that is using tables from a backend SQL db. The
current query has a field "Mileage Total" which has the following expression:
"Mileage Total: Round(([Mileage]*0.585),2)"

"Mileage" is a field with a value equalling total miles traveled.
"TravelDate" is the field with the date of travel, but of course, it isn't
currently used in the expression.

If I understand you correctly, your solution is to replace the above
expression with your logic below. I'm sorry for my ignorance, but I need it a
little more clear than that.

Would you please provide the expression in the required syntax so I can give
it a try?

Thank you again for your suggestion.
TraciAnn
 
Ken,

I realize that you might not be able to recognize it at this stage but my
objective is to return exactly the same information that you, Doug, and Mike
have suggested so I am having trouble understanding why you are using the
terms "by far the best solution".

The table that I asked TraciAnn to construct is the same as what you asked
her except that I did not ask for a [DateTo] field. The reason why is that a
[DateTo] field is redundant information and is not needed unless the query is
constructed exactly as you did. It can also lead to confusion as to whether
the end point is or is not included in the period.

You accurately stated, "This won't match your scenario precisely of course".
That is why I am trying not to redesign her application. But instead to use
the existing queries as she requested.

Yes. I did start with only one single rate in the table. That was the
original request. If you go back and read my second posting I offered the
same alternative (Item 6) that you, Doug and Mike have also suggested. Traci
has also asked for that same solution. That is the next step. I will change
the existing function slightly. Then Traci will be able to add [TravelDate]
to her function calls GetMilRate([TravelDate]) and she will have returned the
exact rate for the date of travel.

I certainly realize that it is often tempting and frequently proper to
redesign an existing application. I do not feel that is the case in this
instance. After all her existing application is working and Traci has stated
the solutions that I have provided are working.

Jack Cannon
 
Good morning Jack! I'm back in the office and ready to tackle this. Thank you
again for all your help.

1. Delete the Autoexec Macro in its entirety.
Done

2. Delete the LdCurMilRate function in its entirety:
Done

3. Change the Function GetMilRate to the following:
Done

Gratefully!
TraciAnn
 
Back
Top