Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

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")
 
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.
Click to expand...

A "Non Equi Join" query can do this. Create a three-field table (let's
call it Signs) with fields DateFrom, DateTo, and Animal; put the lunar
dates in the first two fields, and the Chinese astrological sign in
the third.

If your table has a DOB field, you can then create a Query

SELECT yourtable.<whatever>, Signs.Animal
FROM yourtable
INNER JOIN Signs
ON yourtable.DOB >= [DateFrom] AND yourtable.DOB <= [DateTo];

You will need to be sure that there are no gaps or overlaps in the
dates (and bear in mind, the actual Chinese astrological signs change
on the day of the lunar new year, sometime in February, not April 1,
if that's what you're doing!)

John W. Vinson[MVP]
 
Back
Top