Where should I keep a single variable (Mileage Reimbursement)

  • Thread starter Thread starter TraciAnnNeedsHelp
  • Start date Start date
TraciAnn,

Does everything work okay as a single variable?

If so we can make some modifications to implement multiple variables that
are chosen by date.

Jack Cannon
 
Yes. Using the GetMilRate function in the query is calculating the mileage
correctly. The only problem is, I used Format("Fixed") to format the numbers
but for some reason it is left aligning the column, but that's minor.

I'm ready to have it apply the correct rate based on the date.
 
TraciAnn,

Implement these four steps and it should work by date.

1. Make a backup of your current mdb file.
This is because your present version is accomplishing your original goal and
you do not want to lose that in case of a disaster.

2. Load tblMilRates with the following data:

1/1/2006 - 0.445
1/1/2007 - 0.485
1/1/2008 - 0.505
7/1/2008 - 0.585
1/1/2009 - 0.55


3. Replace the function GetMilRate with the following:
(Probably should use a copy and paste)

Public Function GetMilRate(Optional TravelDate As Date) As Currency
On Error GoTo Err_GetMilRate

GetMilRate = DLookup("[MilRate]", "tblMilRates", "[StartDate]=#" & _
DMax("[StartDate]", "tblMilRates", "[StartDate]<=#" & _
IIf(TravelDate = 0, Date, TravelDate) & "#") & "#")

Exit_GetMilRate:
Exit Function

Err_GetMilRate:
MsgBox "Function is GetMilRate" & vbNewLine & Err.Number & vbNewLine &
Err.Description
Resume Exit_GetMilRate

End Function


4. Every place in your queries that you have GetMilRate()
replace it with GetMilRate([TravelDate])
If you do have have [TravelDate] in all your queries,
you can leave it blank and it will use the current date.


Jack Cannon
 
It was working so perfectly until now :(

After doing everything word-for-word (copy and paste for the function) I am
getting a Compile error: Syntax error dialogue box.

The debugger redlines:
Err_GetMilRate:
MsgBox "Function is GetMilRate" & vbNewLine & Err.Number & vbNewLine &

At any point are you referencing the field name in the table? Should it be
in the syntax of "[TableName]![FieldName]"? As mentioned, I copy and pasted
with no edits.


Jack Cannon said:
TraciAnn,

Implement these four steps and it should work by date.

1. Make a backup of your current mdb file.
This is because your present version is accomplishing your original goal and
you do not want to lose that in case of a disaster.

2. Load tblMilRates with the following data:

1/1/2006 - 0.445
1/1/2007 - 0.485
1/1/2008 - 0.505
7/1/2008 - 0.585
1/1/2009 - 0.55


3. Replace the function GetMilRate with the following:
(Probably should use a copy and paste)

Public Function GetMilRate(Optional TravelDate As Date) As Currency
On Error GoTo Err_GetMilRate

GetMilRate = DLookup("[MilRate]", "tblMilRates", "[StartDate]=#" & _
DMax("[StartDate]", "tblMilRates", "[StartDate]<=#" & _
IIf(TravelDate = 0, Date, TravelDate) & "#") & "#")

Exit_GetMilRate:
Exit Function

Err_GetMilRate:
MsgBox "Function is GetMilRate" & vbNewLine & Err.Number & vbNewLine &
Err.Description
Resume Exit_GetMilRate

End Function


4. Every place in your queries that you have GetMilRate()
replace it with GetMilRate([TravelDate])
If you do have have [TravelDate] in all your queries,
you can leave it blank and it will use the current date.


Jack Cannon


TraciAnnNeedsHelp said:
Yes. Using the GetMilRate function in the query is calculating the mileage
correctly. The only problem is, I used Format("Fixed") to format the numbers
but for some reason it is left aligning the column, but that's minor.

I'm ready to have it apply the correct rate based on the date.
Click to expand...
Click to expand...
 
Traci, word wrap got you.

MsgBox "Function is GetMilRate" & vbNewLine & Err.Number & vbNewLine
&
Err.Description

should all be one continuous line


MsgBox "Function is GetMilRate" & vbNewLine & Err.Number _
& vbNewLine & Err.Description

as two separate lines will also work.

--
Clif

TraciAnnNeedsHelp said:
It was working so perfectly until now :(

After doing everything word-for-word (copy and paste for the function)
I am
getting a Compile error: Syntax error dialogue box.

The debugger redlines:
Err_GetMilRate:
MsgBox "Function is GetMilRate" & vbNewLine & Err.Number &
vbNewLine &

At any point are you referencing the field name in the table? Should
it be
in the syntax of "[TableName]![FieldName]"? As mentioned, I copy and
pasted
with no edits.


Jack Cannon said:
TraciAnn,

Implement these four steps and it should work by date.

1. Make a backup of your current mdb file.
This is because your present version is accomplishing your original
goal and
you do not want to lose that in case of a disaster.

2. Load tblMilRates with the following data:

1/1/2006 - 0.445
1/1/2007 - 0.485
1/1/2008 - 0.505
7/1/2008 - 0.585
1/1/2009 - 0.55


3. Replace the function GetMilRate with the following:
(Probably should use a copy and paste)

Public Function GetMilRate(Optional TravelDate As Date) As Currency
On Error GoTo Err_GetMilRate

GetMilRate = DLookup("[MilRate]", "tblMilRates", "[StartDate]=#"
& _
DMax("[StartDate]", "tblMilRates", "[StartDate]<=#" & _
IIf(TravelDate = 0, Date, TravelDate) & "#") & "#")

Exit_GetMilRate:
Exit Function

Err_GetMilRate:
MsgBox "Function is GetMilRate" & vbNewLine & Err.Number &
vbNewLine &
Err.Description
Resume Exit_GetMilRate

End Function


4. Every place in your queries that you have GetMilRate()
replace it with GetMilRate([TravelDate])
If you do have have [TravelDate] in all your queries,
you can leave it blank and it will use the current date.


Jack Cannon


TraciAnnNeedsHelp said:
Yes. Using the GetMilRate function in the query is calculating the
mileage
correctly. The only problem is, I used Format("Fixed") to format
the numbers
but for some reason it is left aligning the column, but that's
minor.

I'm ready to have it apply the correct rate based on the date.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top