DLookup

[Guest]

I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on the age

 

[Rick B]

The preferred way to calculate age (in a query) is...

Age:
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

 

[Guest]

Thank you for your help, however, what I am trying to to is to get is the
appropriate rate based on the age of the employee, for example, I have 2
tables, one that includes the age of the employee and another that has the
age and the rates, I need to get the rate of an employee based on his/her
age. Thank you for you help

The preferred way to calculate age (in a query) is...

Age:
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

--
Rick B

I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on the age

 

[Rick B]

You should not have a table that contains the age. That is poor database
design. Age is not a set value. It changes. You should store the
birthdate in a table and CALCULATE the age as indicates.

You can then use a DLOOKUP to find the corresponding value in your rate
table.

Fix your data structure and then work on your lookup. Use the help files or
serch for previous posts if you need help building the lookup.

--
Rick B

Thank you for your help, however, what I am trying to to is to get is the
appropriate rate based on the age of the employee, for example, I have 2
tables, one that includes the age of the employee and another that has the
age and the rates, I need to get the rate of an employee based on his/her
age. Thank you for you help

The preferred way to calculate age (in a query) is...

Age:
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

--
Rick B

I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on the

age

 

[Guest]

No, it is not a set value, I have a module that calculates the age based on
the B-date that is in my employee table, then the value is automatecaly
transfer to the field Age in my employee table

You should not have a table that contains the age. That is poor database
design. Age is not a set value. It changes. You should store the
birthdate in a table and CALCULATE the age as indicates.

You can then use a DLOOKUP to find the corresponding value in your rate
table.

Fix your data structure and then work on your lookup. Use the help files or
serch for previous posts if you need help building the lookup.

--
Rick B

Thank you for your help, however, what I am trying to to is to get is the
appropriate rate based on the age of the employee, for example, I have 2
tables, one that includes the age of the employee and another that has the
age and the rates, I need to get the rate of an employee based on his/her
age. Thank you for you help

The preferred way to calculate age (in a query) is...

Age:
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

--
Rick B



I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on the age

 

[fredg]

Thank you for your help, however, what I am trying to to is to get is the
appropriate rate based on the age of the employee, for example, I have 2
tables, one that includes the age of the employee and another that has the
age and the rates, I need to get the rate of an employee based on his/her
age. Thank you for you help

The preferred way to calculate age (in a query) is...

Age:
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))

--
Rick B

I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on the age

You do realize, I hope, that if you have the employee's age stored in
a table, it will be wrong at least once a year. Far better to store
the employee's birth date, and compute the age, whenever needed, from
there. Rick gave you the correct expression to do so.

That being said....
Where are you doing this? In a Form, Query, or Report?
You can do this using a User Defined function

Something like this, substituting your actual field and table names:
(Air Code)

Public Function FindRate(ID as Long)
Dim intAge as integer
dim sngRate as single
intAge = DLookUp("[Age]","EmployeeTable","[EmployeeID] = " & ID)
sngRate = DLookUp("[Rate]","RateTable","[YourAgeField] = " & intAge)
FindRate = sngRate
End Function

Call it from an unbound control on a form or report.
=FindRate([EmployeeID])

 

[Rick B]

You don't store calculated values.

--
Rick B

No, it is not a set value, I have a module that calculates the age based on
the B-date that is in my employee table, then the value is automatecaly
transfer to the field Age in my employee table

You should not have a table that contains the age. That is poor database
design. Age is not a set value. It changes. You should store the
birthdate in a table and CALCULATE the age as indicates.

You can then use a DLOOKUP to find the corresponding value in your rate
table.

Fix your data structure and then work on your lookup. Use the help files or
serch for previous posts if you need help building the lookup.

--
Rick B

Thank you for your help, however, what I am trying to to is to get is the
appropriate rate based on the age of the employee, for example, I have 2
tables, one that includes the age of the employee and another that has the
age and the rates, I need to get the rate of an employee based on his/her
age. Thank you for you help
:

The preferred way to calculate age (in a query) is...

Age:

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(

),"mmdd"))

--
Rick B



I am trying to create an expression that will find the age of an employee
from one table into another table, and assigned the rate based on

the
age