CrossTab Query 1

[dbadmin]

I Have a query (query1) which is created from a cross tab query (query2) which itself is created from another query (query3), query3 has some date values. Is it possible for me to open a report from query1 by passing a date value and report name from a form to query3?

dbadmin

 

[hneal98]

You can not open a report from a query. That would be like trying to open a report from a table. You can, on your form create a button to run the report and pass a report name and date to it from there.

 

[dbadmin]

Hi,

May be you misunderstood me. I am opening the report from the form itself, like you suggested but the problem is, The report's fields are from query1, and how I will pass the form's date textbox's value to query3, since query3 only has the date values in it.

dbadmin.

 

[hneal98]

In each query that you may need, you can reference fields from the form. So you can store the ReportName (although, I am not sure how you would use that in the query,) and you can have whatever date(s) you need for the query and in a field on the form and reference that field in the query.

 

[dhookom]

You can add criteria referencing form controls in the query where the date field exists. You must set the query parameters in the crosstab. Select Query|Parameters and enter:
Forms!frmYourForm!txtStart Date/time
Forms!frmYourForm!txtEnd Date/time

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dbadmin]

Thankyou all for the replies. For query2 (Crosstab) and query3, the parameter menu is greyed out. For query1 on which the report is based on, when I put the values as you suggested and saved, a square bracket is created like this
[Forms!frmYourForm!txtStart]. and when I execute the query, it gives me an error saying "Invalid bracketing of name"

Any clue?

dbadmin

 

[dhookom]

I have experienced Access "re-bracketing" my parameters in the past and don't know why it happens. I go to the SQL view and change the []s to how they should be.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

Hi,

I am able to run the queries separately now with success, but when I try to open the report from the form, I am getting an error saying Microsoft Jet database Engine does not recognize " as a valid field or expression. What does this mean?

takeover

 

[dhookom]

Are you attempting to open the report specifying a where clause in DoCmd.OpenReport? What happens if you leave the form open and ready, then open the report from the database window?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

Hi Duane,

Yes, I am using DoCmd and having a where clause in that. That where clause is being used for two other output fields. (The text boxes in the form is actually supplying the parameters for the where clause in the DoCmd.OpenReport and two other parameters in the query itself). When I keep the form open, the report opens from the database window but without any criteria, even if I put dates in the text boxes.

takeover

 

[dhookom]

What is your line of code that opens your crosstab report? ie:
DoCmd.OpenReport....

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

DoCmd.OpenReport "rptNumVisPerDiscpl", acViewPreview, , "CERT_FROM_DATE >= #" & strWhere & "# AND CERT_TO_DATE <= #" & strWhere1 & "# "

 

[dhookom]

I assume strWhere and strWhere1 are date values. Does the record source of the report contain the fields/columns CERT_FROM_DATE and CERT_TO_DATE?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

Yes, those are two fields in the record source. As I said earlier, these dates are also parameters to the queries too.

 

[dhookom]

So, you are suggesting that when you have the report open in design view, the field list contains these two fields.

I don't quite understand why you would set a criteria in your query(s) and in the Where clause of DoCmd.OpenReport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

For your first question:Yes, you are right. The dates in the DoCmd.OpenReport command are the only date fields available in the record source (fields in the design view)

I have set the criteria in the query because in the first query only I am refering to the dates which I need to filter out. The query which the report based is a crosstab query whcih is based on the first query. These dates are not in the record source of the report.

 

[dhookom]

Try hard coding values
DoCmd.OpenReport "rptNumVisPerDiscpl", acViewPreview, , "CERT_FROM_DATE >= #7/1/2004# AND CERT_TO_DATE <= #7/31/2004#"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

Thank you somuch for all your help.

I identified the problem but no clue how to fix it now. This is the problem. My query which the report is based on is a crosstab query. The output of the base query is like this

Id REC_NUM Type Count
100 00 RN 10
100 00 PT 5
100 00 HHA 4
100 00 Other 3
100 01 RN 5
100 01 PT 6


The output of Crosstab is like this

ID REC_NUM RN PT HHA Other
100 00 10 5 4 3
100 01 5 6 0 0

Now the problem comes, when I filter out using the date parameter in the query sometimes RN, HHA, PT, or Other may not exist in the output, but my report expects those
fields as output. I don't know how to make the report run even if some fields are not returned.

Any ideas?

 

[dhookom]

Add all possible column headings to the Column Headings property of the crosstab query.

BTW: I don't see the field Cert_From_Date and Cert_To_Date in the query output.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[takeover]

Hi Duane,

You are so nice and good. Thanks for your patience. I have done it at last with your help. Keep it up.

takeover