Multiple queries in 1 report (1 Viewer)

[lilpumpkin]

Hi

I have a table with the following fields
Site ID
Location
Design Scheduled Date
Design Actual Date
Transmission Scheduled Date
Transmission Actual Date
Decommission Scheduled Date
Decommission Actual Date

I want to create a report that shows all activities where the scheduled date is within a Beginning Date and an End Date taken from a user inputted form, but excluding all records where that activity is already completed ie only show records Actual dates are null for that activity.

Is there a way I can create this report without having to go down the path of sub queries and sub reports. There are a lot more activities than the ones listed above and would prefer not to do that.


Thanks
Natalie

 

[Keith Nichols]

Hi,

It sounds like you should be able to get everything you need from a single query and base your report on this.

I would start by designing a query from scratch and working it until you are getting the results you need. Once this works, you can link it to a form for the date parameters.

 

[johncallaghan]

I load a form on startup that is hidden
e.g. DoCmd.OpenForm "frm_VariableStore", , , , , acHidden

Have a text box for storing variables and refer to them by

forms!frm_VaraiableStore!txt_Var1 = ### etc

then in the query place
> forms!frm_VaraiableStore!var1 and < forms!frm_VariableStore!var2 in the criteria row that you want.

You can them store thsee variable from user input.
e.g forms!frm_VariableStore!var1 = txt_var1 (say)


you will get an error if you try to open these queries in VBV, (expected two paramaters) but they work fine if attached to combobox or a form or a report.

Cheers

John

 

[lilpumpkin]

Hi

Thanks for your replies

I tried to get a single query but the query needs to be something like this ..

Where

[Transmission Sched Date is Between Beginning Date and End Date AND Transmission Actual Date is Null] OR [Design Sched Date is Between Beginning Date and End Date AND Design Actual Date is Null] OR etc etc

Is it possible to group queries that way? If I can't group it this way it will exclude rows where, for example, the Design Date is not Null when it needs to be included because the Transmission Date is Null.

Does this make sense?

Thanks again
Natalie

 

[RainX]

Hi

Thanks for your replies

I tried to get a single query but the query needs to be something like this ..

Where

[Transmission Sched Date is Between Beginning Date and End Date AND Transmission Actual Date is Null] OR [Design Sched Date is Between Beginning Date and End Date AND Design Actual Date is Null] OR etc etc

I'm pretty sure that can be done.

WHERE (([Transmission Sched Date] Between [Beginning Date] AND [End Date]) AND [Transmission Actual Date] is NULL)
OR
(([Design Sched Date] BETWEEN [Beginning Date] AND [End Date]) AND [Design Actual Date] IS NULL)
...