Criteria Query Error Please Help

  • Thread starter Thread starter WorkRelated
  • Start date Start date
W

WorkRelated

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
 
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
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
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
Click to expand...
Click to expand...
 
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
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 said:
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
Click to expand...
[quoted text clipped - 17 lines]
V
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
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
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top