age calculations

[Becky]

I have a form that gets its information from a huge table of information.
With the help of this fantastic group, I have created a query which
automatically figures out the age of each rescued animal in years and months.
Now, I need to add that information on the form, but the form will not allow
me to use the query that gets its information from the table. How can I
overcome this obstacle?

 

[fredg]

I have a form that gets its information from a huge table of information.
With the help of this fantastic group, I have created a query which
automatically figures out the age of each rescued animal in years and months.
Now, I need to add that information on the form, but the form will not allow
me to use the query that gets its information from the table. How can I
overcome this obstacle?

If it was me, I would simply repeat the calculation in an unbound
control on the form using the same expression you used in the query.

Alternatively, you can use a DLookUp to find the age.
In an unbound control on the form:

=DLookUp("[AgeField]","[QueryName]","[AnimalID] = " & [AnimalID])
The above assumes [AnimalID] is the records unique prime key and is a
Number datatype.

 

[Becky]

Neither of these work, and I know it's something I'm doing wrong. In the
Query, I had to create an "Age" field that calculated years only, then I
created a "months" field that calculated months only, then I created a "Years
and Months" field that took its calculation from the Age and Months fields.
Would I need to put all 3 calculations in the one Form field?

As far as the DLookUp went, I used my Years and Months field, my Query name,
and ID in both AnimalID places (that's the name of my ID field) but it said
my syntax was messed up.

Any ideas?

I have a form that gets its information from a huge table of information.
With the help of this fantastic group, I have created a query which
automatically figures out the age of each rescued animal in years and months.
Now, I need to add that information on the form, but the form will not allow
me to use the query that gets its information from the table. How can I
overcome this obstacle?

If it was me, I would simply repeat the calculation in an unbound
control on the form using the same expression you used in the query.

Alternatively, you can use a DLookUp to find the age.
In an unbound control on the form:

=DLookUp("[AgeField]","[QueryName]","[AnimalID] = " & [AnimalID])
The above assumes [AnimalID] is the records unique prime key and is a
Number datatype.

 

[fredg]

Neither of these work, and I know it's something I'm doing wrong. In the
Query, I had to create an "Age" field that calculated years only, then I
created a "months" field that calculated months only, then I created a "Years
and Months" field that took its calculation from the Age and Months fields.
Would I need to put all 3 calculations in the one Form field?

As far as the DLookUp went, I used my Years and Months field, my Query name,
and ID in both AnimalID places (that's the name of my ID field) but it said
my syntax was messed up.

Any ideas?

I have a form that gets its information from a huge table of information.
With the help of this fantastic group, I have created a query which
automatically figures out the age of each rescued animal in years and months.
Now, I need to add that information on the form, but the form will not allow
me to use the query that gets its information from the table. How can I
overcome this obstacle?

If it was me, I would simply repeat the calculation in an unbound
control on the form using the same expression you used in the query.

Alternatively, you can use a DLookUp to find the age.
In an unbound control on the form:

=DLookUp("[AgeField]","[QueryName]","[AnimalID] = " & [AnimalID])
The above assumes [AnimalID] is the records unique prime key and is a
Number datatype.

1) I don't know what expression you are using to get the age in the
query.
Take a look at "A More Complete DateDiff Function"
at
http://www.accessmvp.com/djsteele/Diff2Dates.html

This is probably all you would need to use in one control on the form.
Paste the function into a Module, then call it from your unbound
control on your form.
Your form should include the [DateOfBirth] field.

=Diff2Dates("ymd", [DateOfBirth], Date(), True)


2) I assumed your query Age field contained the full age of the animal
in one field, not just the year in one column and the month in
another.

A DLookUp can only return the data from one field in one record in the
table or query so you would need to use something like this:

=DLookUp("[Year and Month Field]","[QueryName]","[AnimalID] = " &
[AnimalID])

If the unique prime key field is not named "AnimalID", change the
above to your field's name.

If [AnimalID] is a Text datatype then use:

"[AnimalID] = '" & [AnimalID] & "'")

The form must be open and the [AnimalID] field shown on the form.