Criteria Query Error Please Help

I have a query with Criteria Between [Start Date] And [End Date] i t has
been working fine until now. When I use start date 01-02-10 and end date
28-02-10 it returns an error 'this expression is typed incorrectly or is is
to complex to be evaluated....' If I use any other dates i.e 01-01-10 -
31-01-10 it works fine, I can not see anything wrong with the data for the
Feb dates.

Thanks in advance

V

Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
design view or by adding a parameters clause to the query in SQL view:

PARAMETERS [Start Date] DATETIME,
[End Date] DATETIME;
SELECT etc….

Ken Sheridan
Stafford, England

WorkRelated wrote:
I have a query with Criteria Between [Start Date] And [End Date] i t has
been working fine until now. When I use start date 01-02-10 and end date
28-02-10 it returns an error 'this expression is typed incorrectly or is is
to complex to be evaluated....' If I use any other dates i.e 01-01-10 -
31-01-10 it works fine, I can not see anything wrong with the data for the
Feb dates.

Thanks in advance

V

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

See
International Dates in Access at:
http://allenbrowne.com/ser-36.html

You could try to enter the dates in yyyy-mm-dd format. That is a format that
is not ambiguous and should work.

2010-02-01 and 2010-02-28

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

KenSheridan via AccessMonster.com wrote:
Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
design view or by adding a parameters clause to the query in SQL view:

PARAMETERS [Start Date] DATETIME,
[End Date] DATETIME;
SELECT etc….

Ken Sheridan
Stafford, England

WorkRelated wrote:
I have a query with Criteria Between [Start Date] And [End Date] i t has
been working fine until now. When I use start date 01-02-10 and end date
28-02-10 it returns an error 'this expression is typed incorrectly or is is
to complex to be evaluated....' If I use any other dates i.e 01-01-10 -
31-01-10 it works fine, I can not see anything wrong with the data for the
Feb dates.

Thanks in advance

V

It shouldn’t be necessary to enter the parameter values in the ISO format
provided (a) the parameters are declared as DateTime and (b) the values are
entered in a format consistent with the local regional date format. While
I’d always use the ISO format for a date literal in code or SQL I would
prefer not to force users to use a date format with which they are not
familiar in everyday usage. Judging by my own experience the OP shouldn’t
have any problems entering the values in the dd-mm-yy format if the
parameters are declared and their system’s regional short date format is a UK
one.

Ken Sheridan
Stafford, England

John Spencer wrote:
See
International Dates in Access at:
http://allenbrowne.com/ser-36.html

You could try to enter the dates in yyyy-mm-dd format. That is a format that
is not ambiguous and should work.

2010-02-01 and 2010-02-28

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
[quoted text clipped - 17 lines]

V

--
Message posted via http://www.accessmonster.com

I'm sure you are correct. I've never worked with dates in the dd/mm/yyyy form
and so I often get confused on how Access handles the dates. That is why I
referred the poster to Allen Browne's article.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

KenSheridan via AccessMonster.com wrote:
It shouldn’t be necessary to enter the parameter values in the ISO format
provided (a) the parameters are declared as DateTime and (b) the values are
entered in a format consistent with the local regional date format. While
I’d always use the ISO format for a date literal in code or SQL I would
prefer not to force users to use a date format with which they are not
familiar in everyday usage. Judging by my own experience the OP shouldn’t
have any problems entering the values in the dd-mm-yy format if the
parameters are declared and their system’s regional short date format is a UK
one.

Ken Sheridan
Stafford, England

John Spencer wrote:
See
International Dates in Access at:
http://allenbrowne.com/ser-36.html

You could try to enter the dates in yyyy-mm-dd format. That is a format that
is not ambiguous and should work.

2010-02-01 and 2010-02-28

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
[quoted text clipped - 17 lines]
V

Yes the fields are date/time in the original table (is this what you are
asking, i am fairly basic at Acces sorry) I have other queries asking the
same information and they are all working fine??

"KenSheridan via AccessMonster.com" wrote:

Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
design view or by adding a parameters clause to the query in SQL view:

PARAMETERS [Start Date] DATETIME,
[End Date] DATETIME;
SELECT etc….

Ken Sheridan
Stafford, England

WorkRelated wrote:
I have a query with Criteria Between [Start Date] And [End Date] i t has
been working fine until now. When I use start date 01-02-10 and end date
28-02-10 it returns an error 'this expression is typed incorrectly or is is
to complex to be evaluated....' If I use any other dates i.e 01-01-10 -
31-01-10 it works fine, I can not see anything wrong with the data for the
Feb dates.

Thanks in advance

V

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

.

No, what I was asking was whether you’d declared the parameters in the query
or not. While most of the time a value entered as a parameter will be
interpreted correctly without the parameter being declared, there is always
the scope for misinterpretation if not, particularly with a date entered in a
short date format. So declaring parameters in the way I described is
advisable.

In query design view its done by selecting Parameters from the Query menu (or
the equivalent in Access 2007) and entering each parameter and selecting its
data type, Date/Time in this instance, but its really just as simple to add
the PARAMETERS clause in SQL view.

Ken Sheridan
Stafford, England

WorkRelated wrote:
Yes the fields are date/time in the original table (is this what you are
asking, i am fairly basic at Acces sorry) I have other queries asking the
same information and they are all working fine??

Have you declared the parameters As DateTime? If not the values entered
could be misinterpreted as arithmetical expressions. You can declare them in
[quoted text clipped - 17 lines]

V

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a53f951e81461@uwe:

In query design view its done by selecting Parameters from the
Query menu (or the equivalent in Access 2007) and entering each
parameter and selecting its data type, Date/Time in this instance,
but its really just as simple to add the PARAMETERS clause in SQL
view.

Yes, but if you do it in SQL view, you have to know the syntax for
declaring the data types, and that's not as straightforward as you
may think, since the syntax is not really specific to Jet/ACE SQL.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/