By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Query in report

P: 2
I have a small Access app I've slowly developed to generate reports from a MS SQL database.

One of these reports populates a temp table (tbl_VisitTemp) from a query that is then used for a report. The query looks for visits that occurred within a date range entered on a form.

The data in the temp table is

Cust_ID
First_name
Last_name
Visit_ID
Visit_date

On the report we wish to also have two textboxes that will show us if there was a visit exactly 30 days and/or 60 days before the Visit_Date queried into the temp table. I don't need any data of the past visits, just a yes/no. (I already calculated the 30 and 60 day dates with a DateAdd function in the report)

To match the visit date with a customer we need to query two seperate tables - so I wrote a query (qry_GetPastVisitDate) that will find a visit if Cust_ID and Visit_date match. I was hoping to do a dcount/dlookup on that query to test if a visit existed.

BUT I can't seem to get it to the query to use criteria from report textboxes. Query works fine when data is entered manually but setting the 'criteria' field for the Visit_date with a criteria like "Reports![rpt_3060Visits]![txt_VisitMinus30]" doesn't work.

Or is there a simpler way to do this?

My Access/VBA skills what I've learned from browsing newsgroups and thru trial and error.... TIA
Jan 13 '08 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have a small Access app I've slowly developed to generate reports from a MS SQL database.

One of these reports populates a temp table (tbl_VisitTemp) from a query that is then used for a report. The query looks for visits that occurred within a date range entered on a form.

The data in the temp table is

Cust_ID
First_name
Last_name
Visit_ID
Visit_date

On the report we wish to also have two textboxes that will show us if there was a visit exactly 30 days and/or 60 days before the Visit_Date queried into the temp table. I don't need any data of the past visits, just a yes/no. (I already calculated the 30 and 60 day dates with a DateAdd function in the report)

To match the visit date with a customer we need to query two seperate tables - so I wrote a query (qry_GetPastVisitDate) that will find a visit if Cust_ID and Visit_date match. I was hoping to do a dcount/dlookup on that query to test if a visit existed.

BUT I can't seem to get it to the query to use criteria from report textboxes. Query works fine when data is entered manually but setting the 'criteria' field for the Visit_date with a criteria like "Reports![rpt_3060Visits]![txt_VisitMinus30]" doesn't work.

Or is there a simpler way to do this?

My Access/VBA skills what I've learned from browsing newsgroups and thru trial and error.... TIA

The only way that I can think of that you could try to see if you can use report criteria like "Reports![rpt_3060Visits]![txt_VisitMinus30]" to set the criteria in a query behind a form, is to try passing the report criteria to a public function in a standard module and then reference the function in the query criteria for the form.

However, the easiest and most common method used to pass the same parameters between a form query and a report query is to utilize a parameter form that contains the desired parameters in textboxes. Once the parameter form is in place, all you have to do is update the criteria row of the field to which the parameter applies in each query. the reference in the criteria row of each query is: Forms!yourParameterFormName!YourControlName. The parameter form must remain open until the query is excecuted.
Jan 14 '08 #2

P: 2
However, the easiest and most common method used to pass the same parameters between a form query and a report query is to utilize a parameter form that contains the desired parameters in textboxes.
That worked _perfectly_

Thank you, very, very much.
Jan 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.