Crosstab query Jet error recognizing criteria from form

[Kate]

Hi, I can't solve this and am tearing my hair out.

I have a table with a field that is a long integer representing the
year. This is not a date field.

I have based a crosstab query on this table, with the year field as the
column heading. The criteria for this field is this:

"Between (Year(Now())-9) And Year(Now())"

This works just fine. But sometimes I cannot prepare the necessary
reports before the New Year comes and goes, and then the criteria are a
year ahead of what is needed. This wouldn't be too much of a problem if
there were only one query such as this, but there are at least 30. It's
a real pain in the b..t to have to change each query individually.

What I'm TRYING to do is simply set up the criteria in a form, and then
have each of the many queries draw its criteria from the form. But I'm
running into this Jet error when I try to view the query output:


"The Microsoft Jet Database Engine does not recognize
'[Forms]![GetDateRanges]![EndYr1]' as a valid field name or expression."

Before concluding that it's because of the Between...And expression, let
me state that I have tested this by just having it refer to one of the
range points (i.e., the lower end which has a default value of
year(now())-9), which has the field name shown in the error message.

I have read that with a crosstab query sometimes it's necesary to
specify the parameter type, and I have done that (long integer).
However, I cannot specify the column headings, obviously, as that would
defeat the point of having a roll-over year criterion.

I have Access 2003 build 6355 and Jet to 4.0 ADO ver 2.8. OS XP SP2.

Thank you whoever can help me!!

 

[John Viescas]

You should always explicitly declare parameters in a query - but most of the
time you can get away without doing it. Crosstab queries are the one case
where you MUST declare your parameters. Open the query in Design View and
choose Parameters from the Query menu. Enter
[Forms]![GetDateRanges]![EndYr1] as the parameter name and choose Long
Integer as the data type.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Kate]

John, thank you so much! I would never have though to enter the form
field's name as the parameter. It was tricky, as the parameter dialog
automatically adds beginning and ending brackets to whatever is entered,
so if

[Forms]![GetDateRanges]![EndYr1]

is entered, it is changed to

[[Forms]![GetDateRanges]![EndYr1]]

and causes an error!!

So I've learned to remove the beginning and ending brackets from the
expression. Thanks again for your help! -Kate