Problem using Query Criteria with an Expression

G

Guest

I have an expression (Dateadd) that returns a "Due Date" in a query. I also
added to the expression field a BETWEEN criteria that is asking for Start and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range of
dates for which database entries are due.

I get the following error when I run the query: This expression is typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
 
M

Michel Walsh

Hi,


Can you post the SQL statement that produces the error ( switch in SQL view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Here's the SQL view:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER]
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start Date]
And [Enter End Date]));

If I remove the Between Start and End date criteria, the query calculates
the expression fine.
Thanks,
Carol


Michel Walsh said:
Hi,


Can you post the SQL statement that produces the error ( switch in SQL view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP


Carol said:
I have an expression (Dateadd) that returns a "Due Date" in a query. I
also
added to the expression field a BETWEEN criteria that is asking for Start
and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range of
dates for which database entries are due.

I get the following error when I run the query: This expression is typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,

I would try to force the parameters to date_time value with a CDate:


WHERE ( MAIN.DEPT Like [Enter Section] )
AND
(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST COMPLETED DATE])
Between CDate( [Enter Start Date] ) And CDate( [Enter
End Date] )) ;



or, more efficient, I assume:


PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime ;
SELECT MAIN.ID, MAIN.DEPT ...

WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start Date]
And [Enter End Date]));





Vanderghast, Access MVP


Carol said:
Here's the SQL view:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER]
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start Date]
And [Enter End Date]));

If I remove the Between Start and End date criteria, the query calculates
the expression fine.
Thanks,
Carol


Michel Walsh said:
Hi,


Can you post the SQL statement that produces the error ( switch in SQL
view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP


Carol said:
I have an expression (Dateadd) that returns a "Due Date" in a query. I
also
added to the expression field a BETWEEN criteria that is asking for
Start
and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range of
dates for which database entries are due.

I get the following error when I run the query: This expression is
typed
incorrectly or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try simplifying
the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks that worked. I have another one where I'm stuck. I've got another
query that has the following SQL:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER], DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],[Expr1]) AS Expr2
FROM MAIN
WHERE (((([MAIN].[DEPT]) Like [Enter Section]))<Date());

I want to copy this query but not use the Like [Enter Section] criteria.
How do I get rid of it? It's only listed on the SQL view not in design view.

Thanks,
Carol


Michel Walsh said:
Hi,

I would try to force the parameters to date_time value with a CDate:


WHERE ( MAIN.DEPT Like [Enter Section] )
AND
(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST COMPLETED DATE])
Between CDate( [Enter Start Date] ) And CDate( [Enter
End Date] )) ;



or, more efficient, I assume:


PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime ;
SELECT MAIN.ID, MAIN.DEPT ...

WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start Date]
And [Enter End Date]));





Vanderghast, Access MVP


Carol said:
Here's the SQL view:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER]
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start Date]
And [Enter End Date]));

If I remove the Between Start and End date criteria, the query calculates
the expression fine.
Thanks,
Carol


Michel Walsh said:
Hi,


Can you post the SQL statement that produces the error ( switch in SQL
view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP


I have an expression (Dateadd) that returns a "Due Date" in a query. I
also
added to the expression field a BETWEEN criteria that is asking for
Start
and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range of
dates for which database entries are due.

I get the following error when I run the query: This expression is
typed
incorrectly or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try simplifying
the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


To erase the criteria, in SQL view, boldly remove the stuff starting, and
including, the WHERE word, up to the semi-colon. The criteria is actually
not very useful: it uses the operator LIKE (without wildcard ? ) which
returns a BOOLEAN value (in JET), which is either a 0 either a -1, and test
that Boolean value against the actual date, which is a float value around
38524. The whole test is thus always true.



Hoping it may help,
Vanderghast, Access MVP


Carol said:
Thanks that worked. I have another one where I'm stuck. I've got another
query that has the following SQL:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],[Expr1]) AS Expr2
FROM MAIN
WHERE (((([MAIN].[DEPT]) Like [Enter Section]))<Date());

I want to copy this query but not use the Like [Enter Section] criteria.
How do I get rid of it? It's only listed on the SQL view not in design
view.

Thanks,
Carol


Michel Walsh said:
Hi,

I would try to force the parameters to date_time value with a CDate:


WHERE ( MAIN.DEPT Like [Enter Section] )
AND
(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST COMPLETED DATE])
Between CDate( [Enter Start Date] ) And CDate(
[Enter
End Date] )) ;



or, more efficient, I assume:


PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime ;
SELECT MAIN.ID, MAIN.DEPT ...

WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start
Date]
And [Enter End Date]));





Vanderghast, Access MVP


Carol said:
Here's the SQL view:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER]
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start
Date]
And [Enter End Date]));

If I remove the Between Start and End date criteria, the query
calculates
the expression fine.
Thanks,
Carol


:

Hi,


Can you post the SQL statement that produces the error ( switch in SQL
view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP


I have an expression (Dateadd) that returns a "Due Date" in a query.
I
also
added to the expression field a BETWEEN criteria that is asking for
Start
and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range
of
dates for which database entries are due.

I get the following error when I run the query: This expression is
typed
incorrectly or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try
simplifying
the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the
Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks! All is working! :blush:)
Carol

Michel Walsh said:
Hi,


To erase the criteria, in SQL view, boldly remove the stuff starting, and
including, the WHERE word, up to the semi-colon. The criteria is actually
not very useful: it uses the operator LIKE (without wildcard ? ) which
returns a BOOLEAN value (in JET), which is either a 0 either a -1, and test
that Boolean value against the actual date, which is a float value around
38524. The whole test is thus always true.



Hoping it may help,
Vanderghast, Access MVP


Carol said:
Thanks that worked. I have another one where I'm stuck. I've got another
query that has the following SQL:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],[Expr1]) AS Expr2
FROM MAIN
WHERE (((([MAIN].[DEPT]) Like [Enter Section]))<Date());

I want to copy this query but not use the Like [Enter Section] criteria.
How do I get rid of it? It's only listed on the SQL view not in design
view.

Thanks,
Carol


Michel Walsh said:
Hi,

I would try to force the parameters to date_time value with a CDate:


WHERE ( MAIN.DEPT Like [Enter Section] )
AND
(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST COMPLETED DATE])
Between CDate( [Enter Start Date] ) And CDate(
[Enter
End Date] )) ;



or, more efficient, I assume:


PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime ;
SELECT MAIN.ID, MAIN.DEPT ...

WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start
Date]
And [Enter End Date]));





Vanderghast, Access MVP


Here's the SQL view:
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER]
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]) AND ((DateAdd([FREQ
INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) Between [Enter Start
Date]
And [Enter End Date]));

If I remove the Between Start and End date criteria, the query
calculates
the expression fine.
Thanks,
Carol


:

Hi,


Can you post the SQL statement that produces the error ( switch in SQL
view,
cut and paste the code here)?


Hoping it may help,
Vanderghast, Access MVP


I have an expression (Dateadd) that returns a "Due Date" in a query.
I
also
added to the expression field a BETWEEN criteria that is asking for
Start
and
End dates (Between [Enter Start Date] and [Enter End Date].

The reason for the between criteria is for the user to enter a range
of
dates for which database entries are due.

I get the following error when I run the query: This expression is
typed
incorrectly or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try
simplifying
the
expression by assigning parts of the expression to variables.

I know that the expression is working correctly. I removed the
Between
criteria and the Due Dates calculated correctly.

How do I assign criteria to an expression field?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use In() Expression as Query criteria with guts from Function 1
Create a Query that Prompts for Date and Time 4
formula in query criteria 2
Need Help with Totals Query 24
UNION SELECT "(ALL)" Query Not Working 6
date / text criteria for query 7
What is wrong with this formula? Help! 2
ACCESS query expression that works on one computer but not another 4

Top