Re: Between Dates Error 3071

[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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brigitte P" <Brig...@discussions.microsoft.com> wrote in message
news:ED44AC32-015F-4D38...@microsoft.com...
>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.

[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>

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:ueG5UDHN...@TK2MSFTNGP12.phx.gbl...

[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.