DateAdd Parameter Issue

[Rick Oberlee via AccessMonster.com]

Greetings. I am setting up a query where a user will enter a date and a
recordset will return based on a dateadd function.

The parameter is Between DateAdd("m",[Cycle]*-1,[Date]) AND DateAdd("m",(
[Cycle]-1)*-1,[Date])
Date is the user-entered parameter
Cycle is the number of months stored in a table.
The field being selected is a Date type.

When run, the query returns a message givng me a data type mismatch error.
I have tried the CDate function to no avail. If I put the components of
the criteria in a query field, they display/work perfectly.

Any and all suggestions are MOST appreciated!!
Best,
Rick

 

[Michel Walsh]

Hi,


A parameter cannot be an expression or part of an expression.

A parameter cannot be

FieldName BETWEEN 4 AND 6


neither can it be

+ 5

(incomplete), neither


BETWEEN 4 AND 7

(also incomplete)


A filter *CAN* be a complete expression, but must be a COMPLETE expression,
NOT just what you type in the Criteria line, in the query grid. The query
grid knows how to build the complete expression, from the various parts you
supplied in the grid, but a filter does not have that "intelligence".


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,
Thanks for the tip but I am still stumped.. In an effort to work around, I
set up the two parameters as fields in the query. It looks like

[Calculated field Name] and several variations none of which worked. The

query runs and thinks the calculated field is another prompt/parmeter value.
This query is so close to greatness, IF I could just get the parameter to
work!!

Hi,


A parameter cannot be an expression or part of an expression.

A parameter cannot be

FieldName BETWEEN 4 AND 6


neither can it be

+ 5

(incomplete), neither


BETWEEN 4 AND 7

(also incomplete)


A filter *CAN* be a complete expression, but must be a COMPLETE expression,
NOT just what you type in the Criteria line, in the query grid. The query
grid knows how to build the complete expression, from the various parts you
supplied in the grid, but a filter does not have that "intelligence".


Hoping it may help,
Vanderghast, Access MVP

Greetings. I am setting up a query where a user will enter a date and a
recordset will return based on a dateadd function.

The parameter is Between DateAdd("m",[Cycle]*-1,[Date]) AND DateAdd("m",(
[Cycle]-1)*-1,[Date])
Date is the user-entered parameter
Cycle is the number of months stored in a table.
The field being selected is a Date type.

When run, the query returns a message givng me a data type mismatch error.
I have tried the CDate function to no avail. If I put the components of
the criteria in a query field, they display/work perfectly.

Any and all suggestions are MOST appreciated!!
Best,
Rick

 

[Michel Walsh]

Hi,

Note that a field name cannot be a parameter. A parameter can only be
for a value. You can delegate to a field from a value, such as in:

SELECT CHOOSE( parameter, field1, field2, field3, field4) FROM
myTable

but that is unusual, and basically, here, the parameter only indirectly
specify the field name.

Instead of HOW you try to do it, what is WHAT you try to do?


Vanderghast, Access MVP

Michel,
Thanks for the tip but I am still stumped.. In an effort to work around,
I
set up the two parameters as fields in the query. It looks like

[Calculated field Name] and several variations none of which worked. The

query runs and thinks the calculated field is another prompt/parmeter
value.
This query is so close to greatness, IF I could just get the parameter to
work!!

Hi,


A parameter cannot be an expression or part of an expression.

A parameter cannot be

FieldName BETWEEN 4 AND 6


neither can it be

+ 5

(incomplete), neither


BETWEEN 4 AND 7

(also incomplete)


A filter *CAN* be a complete expression, but must be a COMPLETE
expression,
NOT just what you type in the Criteria line, in the query grid. The query
grid knows how to build the complete expression, from the various parts
you
supplied in the grid, but a filter does not have that "intelligence".


Hoping it may help,
Vanderghast, Access MVP

Greetings. I am setting up a query where a user will enter a date and
a
recordset will return based on a dateadd function.

The parameter is Between DateAdd("m",[Cycle]*-1,[Date]) AND
DateAdd("m",(
[Cycle]-1)*-1,[Date])
Date is the user-entered parameter
Cycle is the number of months stored in a table.
The field being selected is a Date type.

When run, the query returns a message givng me a data type mismatch
error.
I have tried the CDate function to no avail. If I put the components
of
the criteria in a query field, they display/work perfectly.

Any and all suggestions are MOST appreciated!!
Best,
Rick