expression is too complex to be evaluated, I need help!

[authorsami]

I have an almost complete database. Everything was working in test mode with test data. Now that I have put in real data there seems to be a problem with one of my queries/reports. I have to install this on the customer site next Tue so I'm out of my wits right now. I would appreciate anyone who can help.

How to explain: I have a report that lists customers by the office they frequent. This report(rptByLocation) is based on a query (qryByLocation). This query (qryByLocation) is based on a table (tblOffice) and another qry (qryToCalcAge). this age query is based on two tables (tblPatient) and (tblOffice).

As I said it was working when I had test data in the backend. Now that I have the real data I get error that says "this expression is typed incorrectly or is too complex to be evaluated" Any ideas????

 

[DJN]

Post the SQL.

 

[authorsami]

Here is the SQL for the qryPatientToCalcAge:

SELECT 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.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, tblPatient.Sex, tblPatient.[DL#], tblPatient.SSN, tblOffices.OfficeID, tblOffices.Office, tblPatient.AudiologistInititals, tblPatient.GeneralRemarks, 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
FROM tblOffices INNER JOIN tblPatient ON tblOffices.OfficeID = tblPatient.OfficeID;


Here is the SQL for the qryByLocation:

SELECT DISTINCT qryPatientToCalcAge.*, tblOffices.OfficeAddress, tblOffices.OfficeAddress2, tblOffices.OfficeCity, tblOffices.OfficeState, tblOffices.OfficeZip, tblOffices.Phone, tblOffices.Fax, tblOffices.OfficeID
FROM tblOffices, qryPatientToCalcAge
WHERE (((qryPatientToCalcAge.Office)=[forms]![frmPatbyLocation]![Combo5]));

If you want to contact me directly I'm at sharon@comptutor.net

Thank You
Sharon

 

[Brianwarnock]

Hi Sharon

I don't know if DJN will comment on your age calculation whilst looking at your SQL but I feel that I must point out that the age on peoples birthdays will only be correct 1 in 4 times, basically because there are not 365.25 days in a year. Typical module code looks like
Public Function fGetAge(dtDOB As Variant, dtDOT As Integer) As Integer

Dim dtBDay As Date
If Not IsDate(dtDOB) Or Not IsDate(dtDOT) Then Exit Function
dtBDay = DateSerial(Year(dtDOT), Month(dtDOB), Day(dtDOB))
fGetAge = DateDiff("yyyy", dtDOB, dtDOT) + (dtBDay > dtDOT)

End Function

I'm sure that you can do something similar in your SQL.

Brian