Between Dates Error 3071

[Brigitte P]

I have the following expression in a query:Between [Forms]![frmLookupNameBlue
Notes]![StartDate] And [Forms]![frmLookupNameBlue Notes]![EndDate]
which worked for years. Today my users get error 3071, expression to
complex. I did try anything I know, including setting the parameters. There
are several other fields that take values from the above form, but the date
seems to be the offending field.
I'm working in Access XP in a Windows 2000 network environment. As I said,
it worked for years, and in fact, early this morning and then we got the
error. Any ideas. \
Brigitte P.

 

[Allen Browne]

The "too complex" error message means Access cannot interpret the query.
That can happen if it misunderstands the data type of the parameters or the
fields, if it parses the phrases or clauses or brackets incorrectly, if you
are using reserved words in your fields, if the
delimiters/brackets/punctuation are incorrect, or if it is actually too
complex (e.g. 100 AND clauses).

Declaring the parameters in the query is a good move.

If the text boxes on the form are unbound, set their Format property to
Short Date. This helps Access interpret the data correctly, and prevents the
user entering invalid dates.

If that does not solve the problem, post the actual SQL statement from the
query. If it is complex, please format it one field/clause per line to make
it a bit more readable.

 

[Ken Snell [MVP]]

To add to Allen's info, I also get this error if I type in an invalid date
expression (which often happens when I'm typing too quickly), for example I
type
/23/2005

or I type
3//2005

or other similar typos.

--

Ken Snell
<MS ACCESS MVP>

The "too complex" error message means Access cannot interpret the query.
That can happen if it misunderstands the data type of the parameters or
the fields, if it parses the phrases or clauses or brackets incorrectly,
if you are using reserved words in your fields, if the
delimiters/brackets/punctuation are incorrect, or if it is actually too
complex (e.g. 100 AND clauses).

Declaring the parameters in the query is a good move.

If the text boxes on the form are unbound, set their Format property to
Short Date. This helps Access interpret the data correctly, and prevents
the user entering invalid dates.

If that does not solve the problem, post the actual SQL statement from the
query. If it is complex, please format it one field/clause per line to
make it a bit more readable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the following expression in a query:Between
[Forms]![frmLookupNameBlue
Notes]![StartDate] And [Forms]![frmLookupNameBlue Notes]![EndDate]
which worked for years. Today my users get error 3071, expression to
complex. I did try anything I know, including setting the parameters.
There
are several other fields that take values from the above form, but the
date
seems to be the offending field.
I'm working in Access XP in a Windows 2000 network environment. As I
said,
it worked for years, and in fact, early this morning and then we got the
error. Any ideas. \
Brigitte P.

 

[Brigitte P]

Thanks Allen. By setting the unbound field to Short Date, it gave me the
error message that the value wasn't valid, which pointed me to the users'
wrong data entry of 2/29/05 (which, of course doesn't exist). So user error,
but still a good idea to set the unbound fields to the appropriate value. We
pay for taking shortcuts, don't we. THANKS; issue resolved.
Brigitte P.

The "too complex" error message means Access cannot interpret the query.
That can happen if it misunderstands the data type of the parameters or the
fields, if it parses the phrases or clauses or brackets incorrectly, if you
are using reserved words in your fields, if the
delimiters/brackets/punctuation are incorrect, or if it is actually too
complex (e.g. 100 AND clauses).

Declaring the parameters in the query is a good move.

If the text boxes on the form are unbound, set their Format property to
Short Date. This helps Access interpret the data correctly, and prevents the
user entering invalid dates.

If that does not solve the problem, post the actual SQL statement from the
query. If it is complex, please format it one field/clause per line to make
it a bit more readable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the following expression in a query:Between
[Forms]![frmLookupNameBlue
Notes]![StartDate] And [Forms]![frmLookupNameBlue Notes]![EndDate]
which worked for years. Today my users get error 3071, expression to
complex. I did try anything I know, including setting the parameters.
There
are several other fields that take values from the above form, but the
date
seems to be the offending field.
I'm working in Access XP in a Windows 2000 network environment. As I said,
it worked for years, and in fact, early this morning and then we got the
error. Any ideas. \
Brigitte P.