Query works, report doesnt (1 Viewer)

foxy · Apr 28, 2010

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

boblarson · Apr 28, 2010

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)

foxy · Apr 29, 2010

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.

Rich · Apr 29, 2010

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

foxy · Apr 29, 2010

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.

Rich · Apr 29, 2010

foxy said:
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

foxy · Apr 29, 2010

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.

boblarson · Apr 29, 2010

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.

Query works, report doesnt (1 Viewer)

foxy

Registered User.

boblarson

Smeghead

foxy

Registered User.

Rich

Registered User.

foxy

Registered User.

Rich

Registered User.

foxy

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread