help with calculation based on Date

[Brook]

Good day all,

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:

2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Can anyone help?

Thanks,

Brook

 

[Marshall Barton]

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:

2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Create a table to hold the rates. E.g.

table: Rates
fields: FromDate Date
ToDate Date
PerMile Currency

Then, you can use a subquery to retrieve the applicable
rate:

MileageRate: (SELECT Rates.PerMile FROM Rates WHERE tripdate
Between Rates.FromDate And Rates.ToDate)

Or, if that gets in the way in a report (or?), you can use
the much less efficient:

MileageRate: DLookup("PerMile", "Rates", Format(tripdate,
"\#m\/d\/yyyy\#") & " Between FromDate And ToDate)

 

[Michel Walsh]

Hi,



Get a table, Rates, with the fields


FromDate, UpToDate, Rate ' fields name
1900.1.1 2004.1.1 37.5
2004.1.1 2004.8.1 40.5
2004.8.1 2005.1.1 48.5
2005.1.1 3000.1.1 55.5 'data sample


Then


SELECT a.[date], a.mileage, b.rate, a.mileage*b.rate As cost
FROM myTable As a INNER JOIN rates as b
ON a.[date]>=b.FromDate AND a.[date]<b.UpToDate



would find the "matching" rate.

Since the data is now in a table, rather than in a table, it can "grow" or
be modified without having to touch your code. Easier to maintain, isn't it?

Hoping it may help,
Vanderghast, Access MVP

 

[Brook]

Hello Marshal,

Thank you very much that worked great and will be much easier to update.

The only thing is that it is rounding up to two decimals, I tried
Round(Select... ), but that isn't working..

any suggestions?

Thanks,

Brook

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:

2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Create a table to hold the rates. E.g.

table: Rates
fields: FromDate Date
ToDate Date
PerMile Currency

Then, you can use a subquery to retrieve the applicable
rate:

MileageRate: (SELECT Rates.PerMile FROM Rates WHERE tripdate
Between Rates.FromDate And Rates.ToDate)

Or, if that gets in the way in a report (or?), you can use
the much less efficient:

MileageRate: DLookup("PerMile", "Rates", Format(tripdate,
"\#m\/d\/yyyy\#") & " Between FromDate And ToDate)

 

[Marshall Barton]

Actually, I like Michel's Join approach better than the two
ways I posted.

Regardless of how you get the value of MileageRate, the
rounding you are seeing is just an artifact of the display
you are looking at. The value of the field should be
unaffected by the query. Try setting the field's format to
display 4 places.

BTW, Access uses "Bankers Rounding", which rounds to the
nearest even number (i.e. up half the time and down half the
time). For example:

Value Rounded
1.355 1.36
1.365 1.36
1.375 1.38
--
Marsh
MVP [MS Access]

Thank you very much that worked great and will be much easier to update.

The only thing is that it is rounding up to two decimals, I tried
Round(Select... ), but that isn't working..

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:
2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Create a table to hold the rates. E.g.

table: Rates
fields: FromDate Date
ToDate Date
PerMile Currency

Then, you can use a subquery to retrieve the applicable
rate:

MileageRate: (SELECT Rates.PerMile FROM Rates WHERE tripdate
Between Rates.FromDate And Rates.ToDate)

Or, if that gets in the way in a report (or?), you can use
the much less efficient:

MileageRate: DLookup("PerMile", "Rates", Format(tripdate,
"\#m\/d\/yyyy\#") & " Between FromDate And ToDate)

 

[Brook]

Thanks Marshal,

I was looking at Michaels approach but was a little confused that is why I
tried yours.

The code from Michael:

Do I need to have a MileageRate: before the select clause?
Do I include the A.? B.?

Thans,

Brook
SELECT a.[date], a.mileage, b.rate, a.mileage*b.rate As cost
FROM myTable As a INNER JOIN rates as b

Actually, I like Michel's Join approach better than the two
ways I posted.

Regardless of how you get the value of MileageRate, the
rounding you are seeing is just an artifact of the display
you are looking at. The value of the field should be
unaffected by the query. Try setting the field's format to
display 4 places.

BTW, Access uses "Bankers Rounding", which rounds to the
nearest even number (i.e. up half the time and down half the
time). For example:

Value Rounded
1.355 1.36
1.365 1.36
1.375 1.38
--
Marsh
MVP [MS Access]

Thank you very much that worked great and will be much easier to update.

The only thing is that it is rounding up to two decimals, I tried
Round(Select... ), but that isn't working..

Brook wrote:
I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:
2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Create a table to hold the rates. E.g.

table: Rates
fields: FromDate Date
ToDate Date
PerMile Currency

Then, you can use a subquery to retrieve the applicable
rate:

MileageRate: (SELECT Rates.PerMile FROM Rates WHERE tripdate
Between Rates.FromDate And Rates.ToDate)

Or, if that gets in the way in a report (or?), you can use
the much less efficient:

MileageRate: DLookup("PerMile", "Rates", Format(tripdate,
"\#m\/d\/yyyy\#") & " Between FromDate And ToDate)

 

[Marshall Barton]

Michel's query is the entire query, not just a calculated
field. Create a new query, but don't bother with the
table/fields. Instead, immediately switch to SQL view,
select whatever's already there and Paste his SQL statement.
Then change the table and field names to the ones you have.

The A and B are alias names for the table names. In this
case, they just make the query easier to write and you can
leave them the way they are.

Michel did not use the name MileageRate, his query used the
name Rate instead.

There's nothing tricky going on here, so, after you get the
table and field names corrected, you can switch back to
query design view to see how you could have done it from
scratch without going to SQL view.
--
Marsh
MVP [MS Access]

I was looking at Michaels approach but was a little confused that is why I
tried yours.

The code from Michael:
Do I need to have a MileageRate: before the select clause?
Do I include the A.? B.?


Brook
SELECT a.[date], a.mileage, b.rate, a.mileage*b.rate As cost
FROM myTable As a INNER JOIN rates as b

Actually, I like Michel's Join approach better than the two
ways I posted.

Regardless of how you get the value of MileageRate, the
rounding you are seeing is just an artifact of the display
you are looking at. The value of the field should be
unaffected by the query. Try setting the field's format to
display 4 places.

BTW, Access uses "Bankers Rounding", which rounds to the
nearest even number (i.e. up half the time and down half the
time). For example:

Value Rounded
1.355 1.36
1.365 1.36
1.375 1.38

Thank you very much that worked great and will be much easier to update.

The only thing is that it is rounding up to two decimals, I tried
Round(Select... ), but that isn't working..


Brook wrote:
I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:
2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

 

[Brook]

Good Day Michel,

I am following marshals thoughts that your SQL code would be a better, but
I'm having trouble understand your code.

What fields/names should I change to match my fields? I have tblrates,
with fields frmdate, uptodate, permile.

Thanks,

Brook

Hi,



Get a table, Rates, with the fields


FromDate, UpToDate, Rate ' fields name
1900.1.1 2004.1.1 37.5
2004.1.1 2004.8.1 40.5
2004.8.1 2005.1.1 48.5
2005.1.1 3000.1.1 55.5 'data sample


Then


SELECT a.[date], a.mileage, b.rate, a.mileage*b.rate As cost
FROM myTable As a INNER JOIN rates as b
ON a.[date]>=b.FromDate AND a.[date]<b.UpToDate



would find the "matching" rate.

Since the data is now in a table, rather than in a table, it can "grow" or
be modified without having to touch your code. Easier to maintain, isn't it?

Hoping it may help,
Vanderghast, Access MVP

Good day all,

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:

2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between" dates
calculation?

Can anyone help?

Thanks,

Brook

 

[Michel Walsh]

Hi,


Should be:


SELECT a.[date], a.mileage, b.permile, a.mileage*b.permile As cost
FROM myTable As a INNER JOIN tblrates as b
ON a.[date]>=b.frmdate AND a.[date]<b.uptodate



Hoping it may help,
Vanderghast, Access MVP

Good Day Michel,

I am following marshals thoughts that your SQL code would be a better,
but
I'm having trouble understand your code.

What fields/names should I change to match my fields? I have tblrates,
with fields frmdate, uptodate, permile.

Thanks,

Brook

Hi,



Get a table, Rates, with the fields


FromDate, UpToDate, Rate ' fields name
1900.1.1 2004.1.1 37.5
2004.1.1 2004.8.1 40.5
2004.8.1 2005.1.1 48.5
2005.1.1 3000.1.1 55.5 'data sample


Then


SELECT a.[date], a.mileage, b.rate, a.mileage*b.rate As cost
FROM myTable As a INNER JOIN rates as b
ON a.[date]>=b.FromDate AND a.[date]<b.UpToDate



would find the "matching" rate.

Since the data is now in a table, rather than in a table, it can "grow"
or
be modified without having to touch your code. Easier to maintain, isn't
it?

Hoping it may help,
Vanderghast, Access MVP

Good day all,

I have a qrymilege that I use for tracking my company travel.

I have date, milege, milealgerate, & total mileage.

Here are the rates:

2004 - 37.5 cents per mile
Jan 1 - Aug 31st - 40.5 cents per mile
Sept 1 - Dec 31st - 48.5 cents per mile

I have not updated my mileage rate calcuation from what I had prior to
August 31st.

Here is my present calculation:

MileageRate: IIf(Year([date])=2004,0.375,0.405)

I am unsure how to set it up for a single year date and a "between"
dates
calculation?

Can anyone help?

Thanks,

Brook