Crosstab query dates

[sdawson]

I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-

>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]

Simple and it works fine.

I now need to base a second report on a Crosstab query based on Query1. Follow me?

Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."

How do I get round this or can I?

The bottom like is I want to fire off printing the report from dates input to a form.

Help please.

 

[Smart]

design your Cross tab query and on the menu bar select query and then parameters
Enter your parameter
>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]

 

[sdawson]

Thanks for reply Smart.

Getting closer here.
I've never used Query, Parameters before so need more guidance.
I've put in the code as your reply.
Immediately [ and ] are added to the start and end of the code viz.
[>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]]
When run I get an error message about invalid bracketting!

Also which Data Type should be used. Default is Text.

 

[Smart]

try

between[Forms]![DTdates]![StartDate] And [Forms]![DTdates]![EndDate]]

 

[sdawson]

No.
Still invalid bracketting message.

 

[Fear Naught]

You don't need the last closing square bracket. The expression should be like this:

between[Forms]![DTdates]![StartDate] And [Forms]![DTdates]![EndDate]

In actual fact you don't need to enter any of the square brackets becasue none of your controls have either spaces or irregular characters in their names. So you could just put:

between Forms!DTdates!StartDate And Forms!DTdates!EndDate

Access will put the brackets in if it feels you need them

 

[sdawson]

No.
Still not working either way.

 

[Fear Naught]

Steve,

Send me (or post on the forum) your MDB (zipped) and I will have alook at it for you.

Keivn

 

[sdawson]

Got it.

In Query, Parameters the parameter for Query1 must be declared separately with brackets viz. [Forms]![DTdates]![StartDate] and [Forms]![DTdates]![EndDate] (Date/time Data type).
In the same Query1 the criteria is declared viz. >=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate].

This works fine now.

Also in Crosstab_Query1 you must set the Column Heading field, Property, Column Headings with the column names as the appear when Crosstab_Query1 is run.

Job’s a good un.

Not sure if this is useful to anyone. Just finishes off the thread.