Age Exclusion: Help With Query Criteria

[floridaboy2004]

Hello,

I am in desperate need of some help in writing the Query Criteria to eliminate all customers in a table that are under the age of 18 years old on the day that they made a purchase (due to legal reasons).

How could I write a Query Criteria with the following 2 fields in my table/form?

1) DOB (Date of Birth)
2) Date of Purchase

I need to somehow write a criteria to calculate age and exclude all customers UNDER 18 years old.

Thank-you in advance for your help.

 

[Joe4]

You can create a user defined function to calculate the age. Here is one that I wrote:

Function Age(Date1 As Date, Date2 As Date)
'   Calculates age between Date1 and Date2, where Date2 is the more recent date

    Dim TempAge As Long
    TempAge = Year(Date2) - Year(Date1)
    Select Case Month(Date2) - Month(Date1)
        Case Is < 0
            TempAge = TempAge - 1
        Case Is = 0
            If Day(Date2) < Day(Date1) Then TempAge = TempAge - 1
    End Select
    
    Age = TempAge

End Function

Create a new module, and paste the code above in that module. Then you can use that function in your query calculation.

So in your query, now create a new calculated field that uses this function. Something like:
CalcAge: Age([MyTable]![DOB],[MyTable]![Date of Purchase])

This should return the age. Then simply put criteria on this age to exclude anyone under 18, i.e.
>=18

 

[floridaboy2004]

Thanks, jmiskey...you are the absolute BEST for saving the day!

Thanks again...