Subreport not filtering in Access 2000

[cdck]

I have a report which lists all corrective action reports in the system, filtered by the year which the user selects when they open the report. Since the parameter requested by the report is "Year?", I apply an Iif sequence to the input in order to process it to a useable string for the report query.

[tt][CARyear] Like IIf(IsNull([Year?])=True,Year(Date()),IIf(Len([Year?])=2,"*" & [Year?],[Year?]))[/tt]

Thus the user can just hit enter to see the current year, or input the year in either 4- or 2-digit format. One of the fields pulled in the report query is the CARyear field, which is the 4-digit year of the report entry.

I have a subreport which I originally loaded in the report header, which pulls from a query which counts categories of CAR in each year in the data. This query also includes a 4-digit CARyear field, and this field is the one used to link the subreport to the main report. (I would think that this would mean that the above Iif statement would have no effect on the issue at hand, but I cannot see what the problem is, so I include the information just in case.)

In Access 2003, this works just fantastic. Unfortunately, my primary user for this report has a legacy system with Access 2000. In 2000, the subreport loads, but it shows the counts for EVERY year in the data. I tried creating a header for the CARyear sort group and displaying the subreport within that header, with no effect.

Can anyone tell me what is different in Access 2000 which leads to this issue? Is there a work-around?

Cheryl dc Kern

 

[MajP]

Did you double check the link Master Fields and Link Child Fields properties of the subreport control? My guess is the link properties got cleared out somehow. The iif would have no impact. If the main query is returning the correct dates, then the issue is with the subreport control. Post these properties.

FYI, the actual query has no impact only the returned value. To show this you can make a subform and link it to an unbound textbox in a mainform. If you type a value in the text box the subform control will filter to the correct records.

 

[cdck]

I've double-checked the Mast/Child link fields again, and they are correctly set and have not been cleared. (Also, if they had been cleared, that would have affected Access 2003, but the report displays correctly in 2003.)

I realized that the output is what matters, I'm wondering if that output is somehow formatted differently in the eyes of Access 2000, however, because of the Iif treatment. I haven't been able to come up with any other reason so far for that version to display all records instead of only the 4 from the correct year.

Cheryl dc Kern

 

[cdck]

I do not know _why_ this fixed it, but I appear to have fixed it.

On the subreport, the field used as the link to the main report was a text box with the same name as the data field displayed in it. (The default Access behavior.) In tinkering, I altered the name of the text box, and when I ran the report in Access 2000 again, it displayed correctly.

Thanks for you efforts, MajP.

Cheryl dc Kern