query get an error

[azhar2006]

Hello Guys


I have two tables I've worked a query based on them
When you run the query get an error
ــــــــــــــــــــــــــــــــــــــــــــــــــ ــــــــــــــــــــــــ
SELECT DISTINCT tblWeight.Patid, tblWeight.FirstName, tblWeight.LastName, Table1.Weight, Table1.Date, DMax("Date","tblWeight" And "TABLE1","Patid=" & [Patid]) AS LastVisitDate, DLookUp("Weight","tblWeight" And "TABLE1","Patid=" & [Patid] & " and Date = #" & [LastVisitDate] & "#") AS LastVisitWeight, DMax("Date","tblWeight" And "TABLE1","Patid=" & [Patid] & " and Date < #" & [LastVisitDate] & "#") AS PrevVisitDate, IIf(IsNull([PrevVisitDate]),Null,DLookUp("Weight","tblWeight" And "TABLE1","Patid=" & [Patid] & " and Date = #" & [PrevVisitDate] & "#")) AS PrevVisitWeight, ([LastVisitWeight]-[PrevVisitWeight])/[PrevVisitWeight] AS WeightDifference
FROM tblWeight INNER JOIN Table1 ON tblWeight.PatID = Table1.NameID
ORDER BY tblWeight.Patid;

[burrina]

We really can't see the last 2 fields which I believe is where the error is coming from. Check the string for errors.

[azhar2006]

OK burrina
Thank you for your interest burrina
This is DB

[ranman256]

1. dont use DLOOKUP in queries. The query IS the lookup. You would add the lookup table as part of the query.

2. I see nothing wrong in the SQL so the -1 is coming from a either lookup or a return param.

[azhar2006]

Thank you very much
But it has not succeeded

[June7]

Date is a reserved word. Should avoid reserved words as field names.

Domain aggregate cannot reference two tables. This is improper syntax.
PrevVisitDate: DMax("Date","tblWeight" And "TABLE1","Patid=" & [Patid] & " and Date < #" & [LastVisitDate] & "#")

Don't need tblWeight in the expression. Table1 has the data. Use NameID instead of PatID.

PrevVisitDate: DMax("Date", "TABLE1", "NameID=" & [NameID] & " and [Date] < #" & [LastVisitDate] & "#")

[azhar2006]

Thank you very much.
ahoowoowoowo how beautiful and crazy ِAccess