OpenReport But All Records Show Up

[SMay]

Hey guys,
What could be the problem with my Macro.

In a Form I have a Subform that has more specific details including a TimeStamp Date() which i am using to open the report to a specific date which is usually NOW.

I want to use a macro for OpenReport for the Current Patient from the MainForm along with the information in the Subform for that Patient. I only want to see a Report of the most recent record.

the Query is done, the Report is done, however, Regardless of what i do i keep getting all of the records relating to that patient in the report.

This is my Macro WhereCondition
[Patient_Last_Name]=[Forms]![NewPatientFormTabbed]![Patient_Last_Name]

I get the Report for the Patient who is the subject of the Form, however I get all of the records associated with the SubForm which comes out to 400+ pages.

What could i do diferently?

Thanks in advance.

 

[CameronM]

You need to add a date criteria to your WHERE clause. At the moment it is only filtering based on Patient_Last_Name, which is why you get all the records.

If you want to filter also by the date you should add something like

AND [TimeStampDate]=[Forms]![NewPatientFormTabbed]![Subform].Form![FieldOnSubForm]

replace [Subform] with the name of the subform control on the main formd
replace [FieldOnSubForm] with the name of the field that contains the data you need

 

[Mike375]

You also need to be careful with Now() as a Date/time stamp because there are 86400 of 'em per day and so 6 records that all got Now() at "the same time" could be different, depending on the time gap to create records.

TimeStamp Date() which i am using to open the report to a specific date which is usually NOW

If you use Now() for such purpoese the best way is to have Now() inserted into textbox and then the value of the texbox is used to mark records or files.

 

[SMay]

You need to add a date criteria to your WHERE clause. At the moment it is only filtering based on Patient_Last_Name, which is why you get all the records.

If you want to filter also by the date you should add something like

AND [TimeStampDate]=[Forms]![NewPatientFormTabbed]![Subform].Form![FieldOnSubForm]

replace [Subform] with the name of the subform control on the main formd
replace [FieldOnSubForm] with the name of the field that contains the data you need

I am doing your suggestion however i still keep getting 100+ records.

[Patient_Last_Name]=[Forms]![NewPatientForm]![Patient_Last_Name] And [StartDate]=[Forms]![NewPatientForm]![NIASubForm].[Form]![StartDate]

I'm not seeing what I am probably missing.

thanks again for your help.

 

[CameronM]

How many records have the same date as [StartDate]=[Forms]![NewPatientForm]![NIASubForm].[Form]![StartDate] ?

Try and create a query first, using the Build expression (right mouse on the criteria line for StartDate) to point to the subform and check how many results come back.

Also, as per the post from Mike375, what is the data type of StartDate - is it just storing the date?

 

[SMay]

How many records have the same date as [StartDate]=[Forms]![NewPatientForm]![NIASubForm].[Form]![StartDate] ?

Typically Each Patient would have One Subform Completed on a given day. But we might have 50 separate Patients per day completing the subform.

Try and create a query first, using the Build expression (right mouse on the criteria line for StartDate) to point to the subform and check how many results come back.

Working on It Now

Also, as per the post from Mike375, what is the data type of StartDate - is it just storing the date?

In the Table it is Date/Time (Short Date) with a default value of Date() because I am time stamping the completion of this form. The particular field in the form and on the table is uneditable. I hope that answers your question.

 

[SMay]

An interesting thing has happened. I made an adjustment in the Expression builder for the Where Condition of the Macro.

I selected In the Middle Column <Field List> and in the Right Column i chose the StartTime.

This time I have the same record from the Form and Subform apear on the report 5 times. Now instead of having over 100 pages i only have 5 pages (of the same information).

 

[CameronM]

The query behind the report is the same as the one behind the form?

I would also try and build a query that returns the rows you expect, even if you place a patientID and date directly in the query criteria, just to make sure there are no strange joins that are adding duplicate rows.

 

[SMay]

The query behind the report is the same as the one behind the form?

I would also try and build a query that returns the rows you expect, even if you place a patientID and date directly in the query criteria, just to make sure there are no strange joins that are adding duplicate rows.

I did your suggestions and I just realized that i had a 3rd Subform that has multiple imputs that was making the Report appear to report duplicates. I didn't notice it because it was so subtle.

Your assistance has been fantastic.

 

[CameronM]

Glad I could help.