Question

I am trying to apply a label field to a query record based upon where it
falls in a second lookup table. The look up table has a start date and an
end date and a description (Tiger, Dog, Monkey etc)
The lookup is using a birthdate from an record of a person.
This is an example:
If John Smith birthdate falls between 4/1/1990 and 3/31/1991 then is a
"Tiger"
If John Smith birthdate falls between 4/1/1991 and 3/31/1992 then is a "Dog"
If John Smith birthdate falls between 4/1/1992 and 3/31/1993 then is a
"Monkey"

I would like to do this in a query. This would allow me to have it be
dynamic in a report. I guess I could add a field to assign in the personal
table but the query would be the preferred way.

Thanks
Tim

Put the following in your design view of query FIELD.

Animal: Iif([BirthDateField] between [LookUpStartDate] and [LookUpEndDate],
[LookUpAnimal], "Unknown")

"JimS" wrote:

I am trying to apply a label field to a query record based upon where it
falls in a second lookup table. The look up table has a start date and an
end date and a description (Tiger, Dog, Monkey etc)
The lookup is using a birthdate from an record of a person.
This is an example:
If John Smith birthdate falls between 4/1/1990 and 3/31/1991 then is a
"Tiger"
If John Smith birthdate falls between 4/1/1991 and 3/31/1992 then is a "Dog"
If John Smith birthdate falls between 4/1/1992 and 3/31/1993 then is a
"Monkey"

I would like to do this in a query. This would allow me to have it be
dynamic in a report. I guess I could add a field to assign in the personal
table but the query would be the preferred way.

Thanks
Tim

Karl this looks like what I need to do. I have a table with commission
earned. Based on how much commission there is a rate I pay the brokers.
When I use unique values I get two lines (one for the default and the other
with the correct rate).

What am I doing wrong. Here is my formula

NewRate: IIf(Abs([commission]) Between [BreakPoints]![LowLevel] And
[BreakPoints]![HighLevel],[rate],99)

"KARL DEWEY" wrote:

Put the following in your design view of query FIELD.

Animal: Iif([BirthDateField] between [LookUpStartDate] and [LookUpEndDate],
[LookUpAnimal], "Unknown")

"JimS" wrote:

I am trying to apply a label field to a query record based upon where it
falls in a second lookup table. The look up table has a start date and an
end date and a description (Tiger, Dog, Monkey etc)
The lookup is using a birthdate from an record of a person.
This is an example:
If John Smith birthdate falls between 4/1/1990 and 3/31/1991 then is a
"Tiger"
If John Smith birthdate falls between 4/1/1991 and 3/31/1992 then is a "Dog"
If John Smith birthdate falls between 4/1/1992 and 3/31/1993 then is a
"Monkey"

I would like to do this in a query. This would allow me to have it be
dynamic in a report. I guess I could add a field to assign in the personal
table but the query would be the preferred way.

Thanks
Tim