Problem calculating Age

[authorsami]

I have a form that is based on a query. I use a query so I can calculate a person's age.

It works ok, except that when I first look at the form where I have demographic info about the customer the age shows up fine. I then go to a second form based on the customer that I'm looking at. After looking at the second form I close that form and the previous form is now visavle again, but with one small problem. The age field now has #error#..... What can I do to resolve this problem, any ideas?

Sharon

 

[Nowell]

Hi,

Please submit your query.

 

[authorsami]

This is the SQL code for that query. It works until I go to the second form and then return to the first form.
===
SELECT tblPatient.Salutation, tblPatient.FirstName, tblPatient.LastName, tblPatient.Address1, tblPatient.Address2, tblPatient.Address3, tblPatient.City, tblPatient.State, tblPatient.Zip, tblPatient.HomePhone, tblPatient.WorkPhone, tblPatient.CellPhone, tblPatient.OtherPhone, tblPatient.NoMail, tblPatient.DOB, IIf(DatePart("m",tblPatient!DOB)=DatePart("m",Date()) And DatePart("d",tblPatient!DOB)=DatePart("d",Date()),CInt((DateDiff('d',tblPatient!DOB,Date())/365.25)),CInt((DateDiff('d',tblPatient!DOB,Date())/365.25)-0.5)) AS Age, tblPatient.Sex, tblPatient.[DL#], tblPatient.SSN, tblPatient.GeneralRemarks, tblPatient.AudiologistInititals, tblPatient.HAPatient, tblPatient.ReferalSource, tblPatient.Insurance, tblPatient.AltAddr1, tblPatient.AltAddr2, tblPatient.AtlCity, tblPatient.AltSt, tblPatient.AltZip, tblPatient.BillingAddress, tblPatient.BillingCity, tblPatient.BillingSt, tblPatient.BillingZip, tblPatient.Active, tblPatient.OfficeID, tblPatient.LastContact, tblPatient.ContactReason, tblPatient.SixMoFollowUp, tblPatient.qryRightSales_RetestDate, tblPatient.qryLeftSales_RetestDate, tblPatient.LastAudioTest
FROM tblPatient
ORDER BY tblPatient.LastName;
====
thank you for looking at it.

 

[PaulBricker]

Once the focus shifts from the first form to the second form, you probably are not actually setting the focus back to the first form. You will probably have to requery the form and set it to the record you want so the query runs again. So in the Close event for the second form you can have something like
Forms!FormName.Requery
DoCmd.GoToRecord yada, yada, yada

Post back with specific problems

Paul

 

[OhioSteve]

The other poster probably diagnosed your problem accurately. However, you might consider simplifying your method for calculating the person's age. Let's say that you have a date field called "birthday" in your table. Then this sql should calculate their age in years quite accurately:

customerAge:Int((Date()-[birthday])/365.25)

You don't have make any special preparations before you subtract dates. Access will understand what you want. It will return the difference in days.

 

[authorsami]

Paul: How would I tell the program to show the previous record. e.g. What would Yada, Yado, Yado be? I'm new to this so I need a bit more info.. I keep getting things confused. Or I do things the long way as you can see by the way I calculated the age.

OhioSteve: I'm going to try your way in a minute and see if it will work for me.

Thank you both.

Sharon

 

[PaulBricker]

We can actually try it a couple ways and see what happens.
In the Close event for Form2, put this code

Forms!Form1Name.SetFocus
Forms!Form1Name.Refresh

The refresh command does not set the pointer back to the first record and may be all we need.

Try it and post back with results.

Paul

 

[authorsami]

Paul: It does not refresh, or at least it still gives me the error message.

hmmmmmmm

 

[PaulBricker]

OK, lets try this. We will have to expand it to get the form back to the original record but in the Close event put

Forms!Form1Name.SetFocus
Forms!Form1Name.Requery

See if that takes care of the problem calculated textbox problem.

Paul

 

[OhioSteve]

I think that you should be able to work out all of these issues. At least you are using a query to set things up. You have the right basic approach.

I think that in this case, "yada yada yada" means "use the GotoRecordAction".

More tomorrow.

 

[authorsami]

I've never heard of the GoToRecordAction. I'll have to read up on that one.

 

[PaulBricker]

If you have a primary key that is an AutoNumber then the GoToRecord will work well. Otherwise, we will have to code it in a little more. What is the key field on your Form1.

Paul

 

[MichaelRed]

The easy part shoould be the age calculation. It has been discused numerous times in these fora, and well documented PROCEDURES are included in many of the posts. Replace the query age calculation in your query with your choice of procedure as a FORM control calculation based on the DOB (Date of Birth) field from the query. Should solve the entire issue as well as 'simplifying' the query.

If you want to retain the query based calculation, you need to assure that the calculated fields appear in the correct order. Each calculated field used must be preceeded by ALL of the fields / values which it references.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[authorsami]

Paul key field is the SSN of the customer

 

[PaulBricker]

In the Close event put this code. Adjust form and control names where necessary.

Dim myVal as String
myVal = Forms!Form1!SSNField
Forms!Form1.SetFocus
Forms!Form1.Requery
Forms!Form1!SSN.SetFocus
DoCmd.FindRecord myVal

This should do it.

Paul