seeing if a person is a teenager

superrob5 · May 8, 2003

I posted this in the report section by mistake.

I have a table that has name, number, address, dob, and initialvisit.

I want to be able to compare the DOB to the InitialVisit which are both dates. MM/DD/YYYY. In the report I want to only show people who are teenagers >=13 and <=20. I am not sure how to do this. People have showed me how to do it with a current date using the now () function but I want to use the 2 fields.

Rob

Mile-O · May 8, 2003

superrob5 said:
I posted this in the report section by mistake.

Then don't just leave the post there - delete it. If you don't know how to delete a post then: click on edit, check the deltete box and click delete post.

If you just leave it lying around you'll probably have somebody answering on it while the problem has been solved on the other thread - a waste of their time.

To calculate someone's age, use one of the functions on this thread and put it into a calculated query.

Then your criteria on this field can be: Between 13 And 19

The current criteria you have selects people who are 20 as teenagers.

superrob5 · May 8, 2003

is there a way of adding to a query.

Mile-O · May 8, 2003

Have a look at this then and see how it is used in a query.

FoFa · May 8, 2003

You can use the dateadd function to subtract the proper amount of years from NOW() in a between clause in a query.
As an example, someone 19 years old today (05/08/2003 US Format) would be born (05/09/1984 US Format) (If my feeble brain is doing the math correctly) and 13 years would be 1990.
So WHERE DOB BETWEEN DATEADD("yyyy",-19,now()) AND DATEADD("yyyy",-13,now())
which would be about the same as
WHERE DOB BETWEEN #05/09/'1984# AND #05/09/1990#

Hope that helps

superrob5 · May 8, 2003

I see how it works. but what is it comparing the dob to. the current day.??? See the problem I am having is I need to compare it to a date that they come in for a visit. When looking at the sql statement WHERE (((Age([DateOfBirth],Date())) Between 13 And 19))

I am not sure where it compares the date. But I came up with this in the afterupdate
Private Sub DOB_AfterUpdate()
If (Not IsNull(Me!DOB)) And (Not IsNull(Me!Initialvisit)) Then
If Month(Me!Initialvisit) < Month(Me!DOB) Or (Month(Me!Initialvisit) = Month(Me!DOB) And Day(Me!Initialvisit) < Day(Me!DOB)) Then
Age = Year(Me!Initialvisit) - Year(Me!DOB) - 1
Else
Age = Year(Me!Initialvisit) - Year(Me!DOB)
End If
End If
End Sub

I would prefer to have a query that does this. I have attached the a test db maybe you can show me. Thanks

Rob

Mile-O · May 8, 2003

Assuming your field is InitialVisit then the SQL can be changed to:

WHERE (((Age([DateOfBirth],[InitialVisit])) Between 13 And 19))

superrob5 · May 8, 2003

ummm I see how it would work but for me its a no go. I have this in my table (Patien):
Name, DOB, Initialvisit

now with the sql statement I have

SELECT Patien.ID, Patien.Name, Patien.DOB, Patien.Initialvisit
FROM Patien
WHERE (((Age([DOB],[InitialVisit])) Between 13 And 19));

when running the query it says it doesnt recogninze age. Is age a function or a variable??

Jon K · May 9, 2003

>
I would prefer to have a query that does this.
<

I have added the following Age field in your query and set the Record Source of the Form to the query.

Field: Age: DateDiff("yyyy",[DOB],[InitialVisit]) + Int(Format([InitialVisit],"mmdd") < Format([DOB],"mmdd"))

Criteria: Between 13 and 20

(Note. The DB attached is in Access 97 format. To open it in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)

seeing if a person is a teenager

superrob5

Registered User.

Mile-O

Back once again...

superrob5

Registered User.

Mile-O

Back once again...

Attachments

FoFa

Registered User.

superrob5

Registered User.

Attachments

Mile-O

Back once again...

superrob5

Registered User.

Jon K

Registered User.

Attachments

Similar threads

Users who are viewing this thread