Formula in a query refering to a table of reference

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

Céline Brien

Hi everybody !
Instead of using this formula in my query :
CostKm: Iff([Date]<38620;0,35;0,4)
I would like my formula to refer to a table of reference where user
would type the date of new cost and the cost.
1 2004-01-01 0,40
2 2005-09-25 0,35
3 ...
Many thanks for your help,
Céline
 
Hi Céline,

Just type in the criteria date column [Please enter the date:] and the
criteria cost column [Please enter the cost:] .

"Céline Brien" a écrit :
 
Hi Madeleine,
Thanks for your answer, but it is not what I need.
This query is feeding a report where I have to calculate the total cost
of Km by month.
Céline

MadeleineP said:
Hi Céline,

Just type in the criteria date column [Please enter the date:] and the
criteria cost column [Please enter the cost:] .

"Céline Brien" a écrit :
Hi everybody !
Instead of using this formula in my query :
CostKm: Iff([Date]<38620;0,35;0,4)
I would like my formula to refer to a table of reference where user
would type the date of new cost and the cost.
1 2004-01-01 0,40
2 2005-09-25 0,35
3 ...
Many thanks for your help,
Céline
Click to expand...
Click to expand...
 
Céline

There are a few different ways of getting the applicable rates from a Table
"tblRates".

* The easiet way (but probably not the most efficient) is to use the
DLookUp() function to get the applicable rate. Check Access VB Help on the
DLookUp() function.

* The more efficient way is to construct a Query that involves SubQueries to
select the appropriate rate. However, you will need to know a fair bit of
JET SQL to be able to construct Query / SubQueries.

* I have recently involved in a discussion on similar topic which has a
sample database download. See if you can work your way through the download
provided by Doug from:

http://tinyurl.com/aa24k
 
Hi Van,
Thank you so much for your answer.
I worked out my way through the download. It was not easy but I got it.
I am going to try to adapt this solution to my database and come back
later to tell you if it worked.
I am quite confident that it will.
It is a great + for me.
Thanks again and have a good day,
Céline (alias DameDuNord)
 
You're welcome ...

Doug's Query / SQL is probably the most efficient out of all the different
methods. For small Tables this is probably not important but for Tables
with large number of Records, the processing speed difference is quite
noticeable.
 
Hi Van,
It worked ! A great improvement to our database.
I was aware that with large number of Records, the processing speed
difference is quite noticeable.
Another + with this solution, I can create a form with the tbRates and
add it to the menu to let the users add a new Rate and EffDate.
Thanks again,
Céline
 
Back
Top