Solved Display date on the report (1 Viewer)

[bilakos93]

Hi all

I am not yet fully familiar with MS access so this may be an easy one for some of you
I have a table with some patients information and then another one with their hospital admissions. All of them have an admission date but only some of them have a discharge date (cause they are still in the hospital).
I want to create a report returning the names of those in hospital on a specific date. Thus I have created a query where it prompts the user to enter the reportdate and then it returns the records that ((admissiondate<=reportdate AND dischargedate>reportdate) OR (admissiondate<=reportdate AND dischargedate is null)).
I then created a report based on that query.
Is there a way to display the reportdate on the report header? I have tried creating a textbook with [reportdate] but it prompts me to enter the reportdate twice and it uses the first one for the calculation and then the second for the text box.
Any smart ideas?
Thank you

 

[CJ_London]

Either include the report date in your query in a column or reference the form the user is using to set a report date

forms!formname!txtreportdate

you can use the builder to navigate to it

 

[Ranman256]

in a form, fDateRng, put in a unbound field: txtDate

the query for the report uses it as criteria:
where ((admissiondate<=forms!fDateRng!txtDate AND dischargedate>forms!fDateRng!txtDate) OR (dischargedate is null)).

add to the query as its own field:
RunDate: forms!fDateRng!txtDate

then add RUNDATE to your report.

 

[bilakos93]

Either include the report date in your query in a column or reference the form the user is using to set a report date

forms!formname!txtreportdate

you can use the builder to navigate to it

thank you very much!
I somehow managed to do it for one of my reports but not for the others.
I'm not actually sure how I did it. I didn't create any new forms or queries
Do you mind being more descriptive? As I said I'm still too amateur and can only follow simple instructions (I'm sorry!!)

If it helps , this is the query code for the one that's ok (covid19 is a yes/no field)
SELECT patients.lastname, hospital.COVID19, patients.firstname, patients.ID, hospital.[admissiondate], hospital.diagnosis
FROM patients INNER JOIN hospital ON patients.ID = hospital.[patient]
WHERE (((hospital.[admissiondate])<=[reportdate1]) AND ((hospital.[dischargedate])>[reportdate1])) OR ((((hospital.[admissiondate])<=[reportdate1]) and hospital.[dischargedate]) Is Null)
ORDER BY hospital.COVID19;

and this is the one that I can't fix (note that on this one the patients actually get one ore more diagnosis from a drop down list for a specific amount of days)
SELECT patients.lastname, patients.firstname, patients.ID, diagnoses.diagnosis.Value, diagnoses.startdate, diagnoses.[enddate]
FROM patients INNER JOIN diagnoses ON patients.[ID] = diagnoses.[patient], patients AS patients_1 INNER JOIN diagnoses AS diagnoses_1 ON patients_1.ID = diagnoses_1.[patient]
WHERE (((diagnoses.startdate)<=reportdate]) AND ((diagnoses.[enddate])>[reportdate]));

 

[CJ_London]

modify this

SELECT patients.lastname, hospital.COVID19, patients.firstname, patients.ID, hospital.[admissiondate], hospital.diagnosis

to

SELECT patients.lastname, hospital.COVID19, patients.firstname, patients.ID, hospital.[admissiondate], hospital.diagnosis, [reportdate1] AS rptDate1

Then in your report header bind rptDate1 to a textbox

Can't help with the form option beyond what has been suggested as you have not provided any info on that if you are using one

 

[bilakos93]

modify this

SELECT patients.lastname, hospital.COVID19, patients.firstname, patients.ID, hospital.[admissiondate], hospital.diagnosis

to

SELECT patients.lastname, hospital.COVID19, patients.firstname, patients.ID, hospital.[admissiondate], hospital.diagnosis, [reportdate1] AS rptDate1

Then in your report header bind rptDate1 to a textbox

Can't help with the form option beyond what has been suggested as you have not provided any info on that if you are using one

thank you! that actually did the job!