display only the last record

[zxmax]

Greeting,
I have a subform of a main form, that records "bloodPressure" of a patient, the subform, has 2 feilds, "Date" and "BloodPressure" ,,

Now when i print a report, i have only one field for the "bloodPressure" where i want to show only the last record, i don't want to show all the previous record,

how would i tell the report to print only the last entry ?

Thanks

 

[cuok]

hi

First i hope you are note using "Date" as name of date- field in your table.

Seconed: 2 fields?? No IdPatient????
maybe i dont understand you.

build a query:
SELECT TOP 1 YourTbl.bloodPressure, YourTbl.DateCheck, FROM YourTbl
ORDER BY YourTbl.DateCheck DESC;

this will give you the date u want.

you can use it with DlookUp Function in Format event.

CUOK

 

[Steve101]

You have a few options here:

(a) Add a subreport to the main report; then use an aggregate query as the recordsource for the subreport, with the Parent / Child link fields set to PatientId; eg.
[tt]
SELECT PatientId, LAST(BloodPressureDate)
FROM tblBloodPressure
[/tt]
This option probably an overkill for this requirement, but a good exercise if you're not familiar with the power and use of subreports.

(b) Use the DLAST function to retrieve the last blood pressure date associated with the patient. You could either get this value by applying the function call as the controlsource property to an unbound control on the report; eg.
[tt]
=DLast("BloodPressureDate","tblPatientBloodPressure","PatientId=" & Me!PatientId)
[/tt]

(c) ... or you could incorporate the above DLast invocation to the recordsource of the report, and then use a bound control on the report; eg.
[tt]
SELECT PatientId,
DLast("BloodPressureDate", "tblPatientBloodPressure", "PatientId=" & PatientId) AS LastBloodPressureDate
FROM tblPatient
[/tt]

There are other options as well, but this ought to be enough to get you up and running.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zxmax]

Thanks Cuok for the reply,
Well i did had date feild as "date" is that bad ? i could change that,
And yes i have an IdPatient

my question is, where do i put this query , in the report property ?

 

[Golom]

zxmax

Yes ... Date as a field name is bad because there is a VB function called Date. A statement like
[tt]
Select Date As theDate From tbl
[/tt]
is ambiguous ... will it give you the field named "Date" or will it give you the current date returned by the Date Function?

 

[cuok]

Hi Steve101!

u r geting the NotNull "BloodPressure" regardingless the date, what if the patient has no "BloodPressure" for the LAST RECORD as zxmax wrote in his thread?

pls. correct me if i'm missing somthing.

CUOK

 

[Steve101]

Hi CUOK

(a) If the patient has no blood pressure record, the DLast function will return Null, as no blood pressure entries exist. This is a correct response.

(b) I've re-read the thread carefully, and do not see anything about

patient has no "BloodPressure" for the LAST RECORD as zxmax wrote in his thread?

Perhaps one of us is misinterpreting the post.
Regards,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zxmax]

thanks for the replys, i'll try these options, and let you know how it went

 

[cuok]

Hi steve101!

i got you about the Dlast , you right!

here is a quote from zxmax post:

"Now when i print a report, i have only one field for the "bloodPressure" where i want to show only the last record[\b], i don't want to show all the previous record,
"
maybe i didnt undrestood corectly the post.

thank you
CUOK