Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-28-2010, 06:47 AM   #1
foxy
Newly Registered User
 
Join Date: Feb 2009
Posts: 64
Thanks: 2
Thanked 0 Times in 0 Posts
foxy is on a distinguished road
Query works, report doesnt

Hi all,

I have a report based on a UNION ALL query. One of the criteria in the query is as follows:

Code:
(tblClosure.close_date>=[Date From (dd/mm/yyyy)] And tblClosure.close_date<=[Date To (dd/mm/yyyy)])
This query works fine if I run it in its own right, the user is pormpted to enter a date range, and the data returned is filtered correctly based on the date range entered.

However, when I build bar chart on a report form the query, I get the following error message:

" The Microsoft Office Access database engine does not recognise '[Date From (dd/mm/yyyy)]' as a valid field name or expression. "

I dont understand this as the query works fine. Does the report not just launch the query and use the results?

Thanks

Foxy

foxy is offline   Reply With Quote
Old 04-28-2010, 07:51 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query works, report doesnt

You should use a FORM for the user input. Then referring to the form should work. But you may have to explicitly provide Parameter information for the Query (going to the toolbar when in design view of the query and to the MENU > QUERY > PARAMETERS and inputting something like: [Forms]![YourFormName]![YourControlName] and then the type - Date)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 04-28-2010, 11:35 PM   #3
foxy
Newly Registered User
 
Join Date: Feb 2009
Posts: 64
Thanks: 2
Thanked 0 Times in 0 Posts
foxy is on a distinguished road
Re: Query works, report doesnt

Hi Bob,

As the query is a UNION ALL query, taking the form:

Code:
SELECT *
FROM Table1
WHERE etc
UNION ALL
SELECT * 
FROM Table2
WHERE etc
UNION ALL
SELECT......
Access 2007 will not let me use the parameters function. The parameters button on the toolbar is greyed out. It will also not let me use the 'design view' of the query, only the 'SQL view'. Which is why i have had to actually type out the SQL for the whole query as below.

Code:
WHERE (tblClosure.close_date>=[Date From (dd/mm/yyyy)] And tblClosure.close_date<=[Date To (dd/mm/yyyy)])
I have used exactly the same where clause in other standard select querys without using parameters and it works fine, which is why I'm a bit confused.

foxy is offline   Reply With Quote
Old 04-29-2010, 02:06 AM   #4
Rich
Newly Registered User
 
Rich's Avatar
 
Join Date: Aug 2008
Location: searching the truth
Posts: 2,898
Thanks: 3
Thanked 2 Times in 2 Posts
Rich is on a distinguished road
Re: Query works, report doesnt

The Parameter button is greyedout because it's no longer a single query and would have to be typed in to the SQL view.
The easiest way is to cheat create a single query where you can reference the Forms parameters, switch that query to SQL view, then copy the Where statement from that back into the Sql view of your original SQL,remember that you'll have to paste the where clause twice in your Union SQL
__________________
Amateurs...built the ark.Professionals...built the Titanic
Rich is offline   Reply With Quote
Old 04-29-2010, 02:28 AM   #5
foxy
Newly Registered User
 
Join Date: Feb 2009
Posts: 64
Thanks: 2
Thanked 0 Times in 0 Posts
foxy is on a distinguished road
Re: Query works, report doesnt

Hi Rich,

Thats exactly what I did in order to get the correct syntax for the where clause. I have included the were clause in every select statement in my union query. As I said in my first post, the query runs and returns data fine, I only get the error message when I create a report from the query.
foxy is offline   Reply With Quote
Old 04-29-2010, 03:03 AM   #6
Rich
Newly Registered User
 
Rich's Avatar
 
Join Date: Aug 2008
Location: searching the truth
Posts: 2,898
Thanks: 3
Thanked 2 Times in 2 Posts
Rich is on a distinguished road
Re: Query works, report doesnt

Quote:
Originally Posted by foxy View Post
Hi Rich,

Thats exactly what I did in order to get the correct syntax for the where clause. I have included the were clause in every select statement in my union query. As I said in my first post, the query runs and returns data fine, I only get the error message when I create a report from the query.
You'll have to create a form to enter the dates you want initially and then pass those Parameters to the query.
You then have to include the Forms reference as Bob said initially. You will also have to keep the form open while the Report is opening
HTH
__________________
Amateurs...built the ark.Professionals...built the Titanic
Rich is offline   Reply With Quote
Old 04-29-2010, 05:31 AM   #7
foxy
Newly Registered User
 
Join Date: Feb 2009
Posts: 64
Thanks: 2
Thanked 0 Times in 0 Posts
foxy is on a distinguished road
Re: Query works, report doesnt

I have tried exactly that approach as well. I enter the dates on the form, click a run command button. If I code the command button to open the query its fine, but if I create a report from the query and code the button to open the report, I then get the db engine error message.

foxy is offline   Reply With Quote
Old 04-29-2010, 08:46 AM   #8
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query works, report doesnt

Have you explicitly added the parameters to the query like Rich said. For something like this, Access is particularly picky and requires the information that parameters are expected so you have to add that into the query.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Crosstab query report JoeCruse Reports 5 09-09-2012 08:35 AM
Crosstab query graph in report BCote689 Reports 0 01-05-2009 08:47 PM
Query Runs. Report based on query crashes Access john471 General 0 02-22-2006 10:35 PM
passing value from report to query from form to display in report.. ahvc Queries 3 05-26-2003 08:05 PM
Parameter Query Report not working - why? Nino Reports 5 05-01-2000 06:47 PM




All times are GMT -8. The time now is 05:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World