Can you put a calculation in a table?

[Ciralia]

I was wondering if it is possible to put a calculation in an Access table. I need ages of people to be calculated, but it has to be done before the filter so that the user can use age as a filtration technique. The two variables I would need are the current year, and the birth year of the person. (i.e. 2003 - birthyear) If I cannot put that kind of calculation in a table, what else could I do?

 

[LonnieJohnson]

Sure it's possible, but highly recommended AGAINST. Calculated values tend not to be accurate at run time for other queries that may hit the table.

Go to advanced search and use key words "CALCULATED FIELD"



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Ciralia]

Where exactly do I find "Advanced Search" I look in all the menus and help index and couldn't find where it was. I am using Access 2000.

 

[LonnieJohnson]

I'm sorry. I was talkng about in this forum. If you go to the top of this web page, you should see it.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[cas20002000]

Maybe something like this can help:

select DateDiff("yyyy", BirthDate, Now) as Age
from Table
where DateDiff("yyyy", BirthDate, Now)>=1
and DateDiff(&quot;yyyy&quot;, BirthDate, Now)<99

How do you filter the records?
Is this for a report? or a form?

Regards,
Carlos.

 

[Ciralia]

I have a filter form that displays several combo boxes that the user can select filter options from. Then once they are done selecting, they hit the OK button and that filters my records to show only those that fit the selected criteria.

I use a global variable to store the selection information, then when I open the results form, I pass that variable along with it. If I were sorting by gender here is what my filter code would look like:

***********************************************************
If Not IsNull(cmbGender.Value) And (cmbGender.Value) <> &quot;Any&quot; Then

If Temp = &quot;&quot; Then
Temp = &quot;Gender=&quot;&quot;&quot; + cmbGender.Value + &quot;&quot;&quot;&quot;

Else
Temp = Temp + &quot;and Gender=&quot;&quot;&quot; + cmbGender.Value + &quot;&quot;&quot;&quot;

End If
End If

DoCmd.Close
DoCmd.OpenForm &quot;frmSearchMain&quot;, acNormal, , Temp
***********************************************************

So, what I want to add to my filter form is a combo box to see a certain age range (i.e. Birth-1yr, 1-2years). The data that will be used for the age calculation will be coming in as a birth DATE (i.e. 03/05/2000) and is stored in my main table called tblModel. So, I need the age to be calculated before the filtering takes place. I mean, is there a way to do it without doing it in the table and having it able to be sorted?

 

[cas20002000]

Do you mean something like this?

select *
from Table
where DateDiff(&quot;yyyy&quot;, BirthDate, Now)+2<= DateDiff(&quot;yyyy&quot;, CmbBirthDate, Now)
and DateDiff(&quot;yyyy&quot;, BirthDate, Now)-2 >= DateDiff(&quot;yyyy&quot;, CmbBirthDate, Now)

 

[Ciralia]

That might work, but where does it go? In my table, in the query, or on the form?

 

[cas20002000]

Try it changing the RecordSource

.RecordSource=&quot;Select * from .....&quot;

Let me know how it works

Regards,
Carlos.

 

[Ciralia]

I figured it out by using a query to do the calculation. Thanks anyhow.