Expression too complex ... ?

[Leslie Isaacs]

Hello All

I have a query that I want to return records according to one or two
user-input parameters. In response to being asked

[View for ot paid today? - enter Y or N - or enter an ot paid date]

the user will enter either "Y", "N", or a specific date, such that:

Entering Y returns the records where 'ot paid date' = todays date
Entering N returns the records where 'ot paid date' is null, and where the
field 'date' (and I know that's a bad choice of name!) is <= the current
date + a number of days ahead also to be specified as a parameter by the
user
Entering a specific date returns the records where 'ot paid date' = that
specific date.

My query so far is

SELECT [all dates].date, [all dates].[ot paid], [View for ot paid today? -
enter Y or N - or enter an ot paid date] AS Expr1
FROM [all dates]
WHERE ((([all dates].date)<=Date()+[How many days ahead?]) AND (([all
dates].[ot paid]) Is Null) AND (([View for ot paid today? - enter Y or N -
or enter an ot paid date])="n")) OR ((([all dates].[ot paid])=Date()) AND
(([View for ot paid today? - enter Y or N - or enter an ot paid date])="y"))
OR ((([all dates].[ot paid])=[View for ot paid today? - enter Y or N - or
enter an ot paid date]));

but there are two problems:
1) (a show stopper) is that when the user enters "Y" or "N" as the parameter
for Expr1 I get the 'Expression too complex...' error, and no results.
2) the user is asked for the [How many days ahead?] parameter even when it
is not required - i.e. when they have entered "Y" or a specified date for
the parameter for Expr1.

I have tried all sorts with this, but cannot get it right. Hope someone can
help.

Many thanks
Leslie Isaacs

 

[Duane Hookom]

Stop using parameter prompts in your queries. Use controls on forms. This
allows you to set values in multiple controls (text boxes, check boxes,
combo boxes,...) so that you query is not too comples.

 

[Leslie Isaacs]

Duane

OK, I'll do that.
I did have some idea it would be 'neater' (at least from a forms point of
view) to get the parameters at the query level, but I can see that in this
case 'neater' = more complex!
Many thanks
Les

Stop using parameter prompts in your queries. Use controls on forms. This
allows you to set values in multiple controls (text boxes, check boxes,
combo boxes,...) so that you query is not too comples.

--
Duane Hookom
MS Access MVP

Hello All

I have a query that I want to return records according to one or two
user-input parameters. In response to being asked

[View for ot paid today? - enter Y or N - or enter an ot paid date]

the user will enter either "Y", "N", or a specific date, such that:

Entering Y returns the records where 'ot paid date' = todays date
Entering N returns the records where 'ot paid date' is null, and where
the field 'date' (and I know that's a bad choice of name!) is <= the
current date + a number of days ahead also to be specified as a parameter
by the user
Entering a specific date returns the records where 'ot paid date' = that
specific date.

My query so far is

SELECT [all dates].date, [all dates].[ot paid], [View for ot paid
today? - enter Y or N - or enter an ot paid date] AS Expr1
FROM [all dates]
WHERE ((([all dates].date)<=Date()+[How many days ahead?]) AND (([all
dates].[ot paid]) Is Null) AND (([View for ot paid today? - enter Y or
N - or enter an ot paid date])="n")) OR ((([all dates].[ot paid])=Date())
AND (([View for ot paid today? - enter Y or N - or enter an ot paid
date])="y")) OR ((([all dates].[ot paid])=[View for ot paid today? -
enter Y or N - or enter an ot paid date]));

but there are two problems:
1) (a show stopper) is that when the user enters "Y" or "N" as the
parameter for Expr1 I get the 'Expression too complex...' error, and no
results.
2) the user is asked for the [How many days ahead?] parameter even when
it is not required - i.e. when they have entered "Y" or a specified date
for the parameter for Expr1.

I have tried all sorts with this, but cannot get it right. Hope someone
can help.

Many thanks
Leslie Isaacs