Parametrised Subquery and Report 2

[takeover]

Hi,

I have a report which runs against a query with parametrised subquery. The report will prompt the user for two input dates. The query looks like this

select table1.field1, table2.filed2 from table1 inner join table2 on table1.field3 = table2.field3 where table1.field4 in (select field4 from table1 where field5 = 'value1' and field6 >= start_date and field6 <= end_date) and table1.field6 >= start_date and table1.field6 <= end_date;

The user is getting prompted now with start_date and end_date. Is it possible to make this report run propting the user to enter the value at the same time using a form instead of prompting him twice (since the query is driving the prompting now)

takeover

 

[RoyVidar]

Hi!

Yes - I assume you are calling the report with a button from a form. If you'r not, then do. On the form, add two unbound controls, name them for instance txtStartDate and txtEndDate (her you may also perform validation checks, which are much better than allowing typos in query criterias)

In the query criteria field, refer to the controls with:

[tt]>=forms!frmYourForm!txtStartDate[/tt]
[tt]<=forms!frmYourForm!txtEndDate[/tt]

HTH Roy-Vidar

 

[takeover]

Hi,

Thanks for the quick response. I need one more functionality. If I leave the StartDate empty, I need all the records less than the EndDate and if I leave EndDate empty, I need all the data greater than the StartDate and if both are empty, I need all data. Is it possible to have this functionality. Do I need to have separate query for these?

takeover

 

[dhookom]

Modifying Roy's suggestion, try:
>=Nz(forms!frmYourForm!txtStartDate,#1/1/1900#)
<=Nz(forms!frmYourForm!txtEndDate,#12/31/2300#)


Duane
MS Access MVP

 

[takeover]

Hi Roy and Duane,

Thanks a lot.

takeover