Calculated Control - Can't make it work!! 1

[Diplomat]

I created a <b>query</b> to calculate the age of my customers. I ran the query and it worked. I want to add to my data entry form a textbox with an <b>expression</b> based on that query to get the age calculated and displayed when the date of birth is entered. I added the expression using the epression builder and pointed to the query and then to the desired field (Age).<br>Any help is appreciated!<br><br>Thanks!!!

 

[DougP]

Well lets see you code<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Diplomat]

Hi Doug,<br><br>In the <b>textbox</b> I have <font color=red><br>= [AgeQuery1]![Age] </font><br><br>In the <b>query</b> I have:<font color=red><br>Age: Int((Date()-([CustomerID]![DOB]))/365.25)</font><br><br>CustomerID is a table containing a field for DOB.<br><br>Thanks!!!

 

[famousb]

do you have any link criteria set up so the form knows which record to select from the query?&nbsp;&nbsp;that is where your error is most likely coming from.&nbsp;&nbsp;you need to have it set in the query (or in the form) so that the customersID in the query has the criteria of the customerID on the form<br><br>ie.. in the criteria of [CustomerID] in AgeQuery1<br><b>=[Forms]![CustomerForm]![CustomerID]</b><br><br>you can do this through the build menu also.<br><br>this will create the necessary link for the form to work. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Diplomat]

Sorry bfamaous, I am not following you. Let me clarify the structure of my Query. Perhaps this will make it easier to explain to me what I need to do:<br><br>AgeQuery1 consists of three fields:<br><br>Field #1 is <font color=red>CIC</font> (or Customer Identification Code) comes from the table called CustomerID<br><br>Field #2 is <font color=red>DOB</font> (or Date of Birth) also comes from the table called CustomerID<br><br>Field #3 is <font color=red>Age</font> containing the expression <font color=red> Age: Int((Date()-([CustomerID]![DOB]))/365.25) </font><br><br>Thanks!!

 

[famousb]

I'm assuming that the form that you are trying to get to work has the field [CIC] on it.<br><br>In your query, in the criteria for [CIC], put in <br><b>[Forms]![YourFormName]![CIC]</b><br><br>or, why don't you just put the formula<br>Age: Int((Date()-([CustomerID]![DOB]))/365.25) <br>directly in the unbound cell on the form?<br><br>it would serve the same purpose.<br>or, to be more efficient, you could use the DateDiff function.<br>ie.<br><b>DateDiff(yyyy, Date(),[CustomerID]![DOB])</b><br>this gives you their age in years.<br>same purpose<br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Diplomat]

I tried them all including the DateDiff expression in a textbox, but nothing worked. I'll let you know what I was doing wrong if I ever figure out.&nbsp;&nbsp;Thanks