2008 date issues

I have several database that have developed the same issue.
All of these databases have reports that are query driven. The queries
have date range criteria. In the date columns of these queries the
criteria reads Between [Start Date:] and [End Date:]
If I try to span between 2007 and 2008 I recieve an error that reads
If I enter a Start Date of 12/24/2007 and an End Date of 01/05/2008
I receive the following error:
======================================================
This expression is typed incorrectly or it is too complex to be
evaluated, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression variable.
======================================================

If I run these queries with a date range of 12/24/2007 and 12/31/2007
or a date range of 01/01/2008 and 01/05/2008 they run fine.

The problem seem to be only when I try to pull data from 2007 and 2008
at the same time.

Any one have any ideas?

Thank you!

Re: 2008 date issues

On 25 jan, 12:32, Del wrote:
> I have several database that have developed the same issue.
> All of these databases have reports that are query driven. The queries
> have date range criteria. In the date columns of these queries the
> criteria reads Between [Start Date:] and [End Date:]
> If I try to span between 2007 and 2008 I recieve an error that reads
> If I enter a Start Date of 12/24/2007 and an End Date of =A001/05/2008
> I receive the following error:
> ==================== =====
==================== =====3D=
=====3D
> This expression is typed incorrectly or it is too complex to be
> evaluated, a numeric expression may contain too many complicated
> elements. Try simplifying the expression by assigning parts of the
> expression variable.
> ==================== =====
==================== =====3D=
=====3D
>
> If I run these queries with a date range of 12/24/2007 and 12/31/2007
> or a date range of 01/01/2008 and 01/05/2008 they run fine.
>
> The problem seem to be only when I try to pull data from 2007 and 2008
> at the same time.
>
> Any one have any ideas?
>
> Thank you!

Hello,

I had been through too much troubles handling dates and as result i
can say you that the best way to use then is in this format: YYYY/MM/
DD that will help you to avoid a lot of headaches.

Re: 2008 date issues

The crucial piece of the puzzle is to ensure that JET is making *date*
comparisons, not string comparisons. If it makes string comparisions, the
date 2/1/2007 comes *after* 1/1/2008, because the first character (the 2)
comes after 1.

So:
1. In table design, check the data type.
This must be a Date/Time field (not a Text field.)

2. In query design, declare the parameters, so Access knows the data type.
Choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Start Date:] Date/Time
[End Date:] Date/Time

Access will now make a date comparison.

BTW, if your parameter refers to an unbound text box on a form, such as:
[Forms].[Form1].[txtStartDate]
as well as declaring the name in the Parameters of the query, open the form
in design view and set the Format property of the text box to General Date.
That way, Access understands the text box as a date as well (and as a bonus
refuses to accept bad dates.)

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

"Del" wrote in message
news:7f042118-fb4d-4736-a85f-7f9068a97736@n20g2000hsh.google groups.com...
>I have several database that have developed the same issue.
> All of these databases have reports that are query driven. The queries
> have date range criteria. In the date columns of these queries the
> criteria reads Between [Start Date:] and [End Date:]
> If I try to span between 2007 and 2008 I recieve an error that reads
> If I enter a Start Date of 12/24/2007 and an End Date of 01/05/2008
> I receive the following error:
> ======================================================
> This expression is typed incorrectly or it is too complex to be
> evaluated, a numeric expression may contain too many complicated
> elements. Try simplifying the expression by assigning parts of the
> expression variable.
> ======================================================
>
> If I run these queries with a date range of 12/24/2007 and 12/31/2007
> or a date range of 01/01/2008 and 01/05/2008 they run fine.
>
> The problem seem to be only when I try to pull data from 2007 and 2008
> at the same time.
>
> Any one have any ideas?
>
> Thank you!

Re: 2008 date issues

Thanks Allen,
Worked perfectly. Figured it would turn out to be something easy.
Thanks again!!!!



On Jan 25, 9:42=A0pm, "Allen Browne" wrote:
> The crucial piece of the puzzle is to ensure that JET is making *date*
> comparisons, not string comparisons. If it makes string comparisions, the
> date 2/1/2007 comes *after* 1/1/2008, because the first character (the 2)
> comes after 1.
>
> So:
> 1. In table design, check the data type.
> This must be a Date/Time field (not a Text field.)
>
> 2. In query design, declare the parameters, so Access knows the data type.=

> Choose Parameters on the Query menu.
> Enter 2 rows in the dialog:
> =A0 =A0 [Start Date:] =A0 =A0 =A0 =A0Date/Time
> =A0 =A0 [End Date:] =A0 =A0 =A0 =A0 Date/Time
>
> Access will now make a date comparison.
>
> BTW, if your parameter refers to an unbound text box on a form, such as:
> =A0 =A0 [Forms].[Form1].[txtStartDate]
> as well as declaring the name in the Parameters of the query, open the for=
m
> in design view and set the Format property of the text box to General Date=
..
> That way, Access understands the text box as a date as well (and as a bonu=
s
> refuses to accept bad dates.)
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Del" wrote in message
>
> news:7f042118-fb4d-4736-a85f-7f9068a97736@n20g2000hsh.google groups.com...
>
>
>
> >I have several database that have developed the same issue.
> > All of these databases have reports that are query driven. The queries
> > have date range criteria. In the date columns of these queries the
> > criteria reads Between [Start Date:] and [End Date:]
> > If I try to span between 2007 and 2008 I recieve an error that reads
> > If I enter a Start Date of 12/24/2007 and an End Date of =A001/05/2008
> > I receive the following error:
> > ==================== =====
==================== =====3D=
=====3D
> > This expression is typed incorrectly or it is too complex to be
> > evaluated, a numeric expression may contain too many complicated
> > elements. Try simplifying the expression by assigning parts of the
> > expression variable.
> > ==================== =====
==================== =====3D=
=====3D
>
> > If I run these queries with a date range of 12/24/2007 and 12/31/2007
> > or a date range of 01/01/2008 and 01/05/2008 they run fine.
>
> > The problem seem to be only when I try to pull data from 2007 and 2008
> > at the same time.
>
> > Any one have any ideas?
>
> > Thank you!- Hide quoted text -
>
> - Show quoted text -

Re: 2008 date issues

One more question.
I have a report that is tied to a parameter query. The query uses
between [Start Date:] and [End Date:]
I've set the parameters of the query as Allen described in his post.
The query runs fine. But when I run the report I recieve the same
error as I did with the query


==================== =====3D=
==================== =====3D=
====
This expression is typed incorrectly or it is too complex to be
evaluated, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression variable.
==================== =====3D=
==================== =====3D=
====

I've gone into the design mode of the report and set the parameters of
all the Date Fields to General Date. But I still get the error when I
try to run the report.




On Jan 27, 5:58=A0pm, Del wrote:
> Thanks Allen,
> Worked perfectly. Figured it would turn out to be something easy.
> Thanks again!!!!
>
> On Jan 25, 9:42=A0pm, "Allen Browne" wrote:
>
>
>
> > The crucial piece of the puzzle is to ensure that JET is making *date*
> > comparisons, not string comparisons. If it makes string comparisions, th=
e
> > date 2/1/2007 comes *after* 1/1/2008, because the first character (the 2=
)
> > comes after 1.
>
> > So:
> > 1. In table design, check the data type.
> > This must be a Date/Time field (not a Text field.)
>
> > 2. In query design, declare the parameters, so Access knows the data typ=
e.
> > Choose Parameters on the Query menu.
> > Enter 2 rows in the dialog:
> > =A0 =A0 [Start Date:] =A0 =A0 =A0 =A0Date/Time
> > =A0 =A0 [End Date:] =A0 =A0 =A0 =A0 Date/Time
>
> > Access will now make a date comparison.
>
> > BTW, if your parameter refers to an unbound text box on a form, such as:=

> > =A0 =A0 [Forms].[Form1].[txtStartDate]
> > as well as declaring the name in the Parameters of the query, open the f=
orm
> > in design view and set the Format property of the text box to General Da=
te.
> > That way, Access understands the text box as a date as well (and as a bo=
nus
> > refuses to accept bad dates.)
>
> > --
> > Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> > Tips for Access users -http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
>
> > "Del" wrote in message
>
> >news:7f042118-fb4d-4736-a85f-7f9068a97736@n20g2000hsh.googl egroups.com...=

>
> > >I have several database that have developed the same issue.
> > > All of these databases have reports that are query driven. The queries=

> > > have date range criteria. In the date columns of these queries the
> > > criteria reads Between [Start Date:] and [End Date:]
> > > If I try to span between 2007 and 2008 I recieve an error that reads
> > > If I enter a Start Date of 12/24/2007 and an End Date of =A001/05/2008=

> > > I receive the following error:
> > > ==================== ===3D=
==================== =====3D=
======
> > > This expression is typed incorrectly or it is too complex to be
> > > evaluated, a numeric expression may contain too many complicated
> > > elements. Try simplifying the expression by assigning parts of the
> > > expression variable.
> > > ==================== ===3D=
==================== =====3D=
======
>
> > > If I run these queries with a date range of 12/24/2007 and 12/31/2007
> > > or a date range of 01/01/2008 and 01/05/2008 they run fine.
>
> > > The problem seem to be only when I try to pull data from 2007 and 2008=

> > > at the same time.
>
> > > Any one have any ideas?
>
> > > Thank you!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: 2008 date issues

Open the query that feeds the report, and switch it to SQL View (View menu,
in query design.)

Post the SQL statement here.

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

"Del" wrote in message
news:04d4de75-aada-4183-b4f1-06967235c1fe@i12g2000prf.google groups.com...
One more question.
I have a report that is tied to a parameter query. The query uses
between [Start Date:] and [End Date:]
I've set the parameters of the query as Allen described in his post.
The query runs fine. But when I run the report I recieve the same
error as I did with the query


======================================================
This expression is typed incorrectly or it is too complex to be
evaluated, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression variable.
======================================================

I've gone into the design mode of the report and set the parameters of
all the Date Fields to General Date. But I still get the error when I
try to run the report.

Re: 2008 date issues

Here is my query:
The query runs fine by itself. But when I run the report I get the
error.

== Query =========3D

PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
SELECT dbo_Shipping_Request.Shipping_Request_Number,
dbo_Shipping_Request.Date, dbo_Shipping_Request.Ship_Code,
dbo_Shipping_Request.Freight_Charge_Acct_No,
dbo_Shipping_Request.Shipment_Requested_By,
dbo_Shipping_Request.Department_Number, dbo_Shipping_Request.Carrier,
dbo_Shipping_Request.Freight_Amount,
dbo_Shipping_Request.Date_Shipped,
dbo_Shipping_Request.Number_Of_Cartons,
dbo_Shipping_Request.Tracking_Number, dbo_Shipping_Request.Weight,
dbo_Shipping_Request.Shipped_By,
dbo_Shipping_Request.Description_Comments_1,
dbo_Shipping_Request.Description_Comments_2,
dbo_Shipping_Request.Description_Comments_3,
dbo_Shipping_Request.Description_Comments_4,
dbo_Shipping_Request.Description_Comments_5,
dbo_Shipping_Request.Part_Number_1,
dbo_Shipping_Request.Part_Number_2,
dbo_Shipping_Request.Part_Number_3,
dbo_Shipping_Request.Part_Number_4, dbo_Shipping_Request.Part_Number_5
FROM dbo_Shipping_Request
WHERE (((dbo_Shipping_Request.Date) Between [Start Date:] And [End
Date:]));

==================== =====3D=
==========



On Jan 27, 9:22=A0pm, "Allen Browne" wrote:
> Open the query that feeds the report, and switch it to SQL View (View menu=
,
> in query design.)
>
> Post the SQL statement here.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Del" wrote in message
>
> news:04d4de75-aada-4183-b4f1-06967235c1fe@i12g2000prf.google groups.com...
> One more question.
> I have a report that is tied to a parameter query. The query uses
> between [Start Date:] and [End Date:]
> I've set the parameters of the query as Allen described in his post.
> The query runs fine. But when I run the report I recieve the same
> error as I did with the query
>
> ==================== =====
==================== =====3D=
=====3D
> This expression is typed incorrectly or it is too complex to be
> evaluated, a numeric expression may contain too many complicated
> elements. Try simplifying the expression by assigning parts of the
> expression variable.
> ==================== =====
==================== =====3D=
=====3D
>
> I've gone into the design mode of the report and set the parameters of
> all the Date Fields to General Date. But I still get the error when I
> try to run the report.

Re: 2008 date issues

The only obvious problem with the query is that there is a field named Date,
which is a reserved word. You could try enclosing it in square brackets,
i.e.:
WHERE (((dbo_Shipping_Request.[Date]) Between ...

The error message stating that the query is "too complex" can be triggered
by the presence of a reserved word in the query where JET does not expect
it. However, I don't see any logical reason why you would get the "too
complex" error message only if the [Start Date:] parameter is a 2007 date
and the [End Date:] parameter is a 2008 date.

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

"Del" wrote in message
news:f17bbb99-eb42-4142-8eae-382aab4ae7bc@i7g2000prf.googleg roups.com...
Here is my query:
The query runs fine by itself. But when I run the report I get the
error.

== Query =========

PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
SELECT dbo_Shipping_Request.Shipping_Request_Number,
dbo_Shipping_Request.Date, dbo_Shipping_Request.Ship_Code,
dbo_Shipping_Request.Freight_Charge_Acct_No,
dbo_Shipping_Request.Shipment_Requested_By,
dbo_Shipping_Request.Department_Number, dbo_Shipping_Request.Carrier,
dbo_Shipping_Request.Freight_Amount,
dbo_Shipping_Request.Date_Shipped,
dbo_Shipping_Request.Number_Of_Cartons,
dbo_Shipping_Request.Tracking_Number, dbo_Shipping_Request.Weight,
dbo_Shipping_Request.Shipped_By,
dbo_Shipping_Request.Description_Comments_1,
dbo_Shipping_Request.Description_Comments_2,
dbo_Shipping_Request.Description_Comments_3,
dbo_Shipping_Request.Description_Comments_4,
dbo_Shipping_Request.Description_Comments_5,
dbo_Shipping_Request.Part_Number_1,
dbo_Shipping_Request.Part_Number_2,
dbo_Shipping_Request.Part_Number_3,
dbo_Shipping_Request.Part_Number_4, dbo_Shipping_Request.Part_Number_5
FROM dbo_Shipping_Request
WHERE (((dbo_Shipping_Request.Date) Between [Start Date:] And [End
Date:]));

===================================



On Jan 27, 9:22 pm, "Allen Browne" wrote:
> Open the query that feeds the report, and switch it to SQL View (View
> menu,
> in query design.)
>
> Post the SQL statement here.
>
> "Del" wrote in message
>
> news:04d4de75-aada-4183-b4f1-06967235c1fe@i12g2000prf.google groups.com...
> One more question.
> I have a report that is tied to a parameter query. The query uses
> between [Start Date:] and [End Date:]
> I've set the parameters of the query as Allen described in his post.
> The query runs fine. But when I run the report I recieve the same
> error as I did with the query
>
> ======================================================
> This expression is typed incorrectly or it is too complex to be
> evaluated, a numeric expression may contain too many complicated
> elements. Try simplifying the expression by assigning parts of the
> expression variable.
> ======================================================
>
> I've gone into the design mode of the report and set the parameters of
> all the Date Fields to General Date. But I still get the error when I
> try to run the report.

Re: 2008 date issues

I'll give that a try. But this database has been in use for 4 or 5
years and we just started having this issue after Jan 1 2008.
If I run the report for any date prior to 2008 the report runs fine.
If I run the report spanning 2007 and 2008 or spanning any date range
in 2008 I get the error. Nothing has been changed in the table design,
query or report.
Thanks,


On Jan 29, 5:46=A0am, "Allen Browne" wrote:
> The only obvious problem with the query is that there is a field named Dat=
e,
> which is a reserved word. You could try enclosing it in square brackets,
> i.e.:
> =A0 =A0 WHERE (((dbo_Shipping_Request.[Date]) Between ...
>
> The error message stating that the query is "too complex" can be triggered=

> by the presence of a reserved word in the query where JET does not expect
> it. However, I don't see any logical reason why you would get the "too
> complex" error message only if the [Start Date:] parameter is a 2007 date
> and the [End Date:] parameter is a 2008 date.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Del" wrote in message
>
> news:f17bbb99-eb42-4142-8eae-382aab4ae7bc@i7g2000prf.googleg roups.com...
> Here is my query:
> The query runs fine by itself. But when I run the report I get the
> error.
>
> == Query =========3D
>
> PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
> SELECT dbo_Shipping_Request.Shipping_Request_Number,
> dbo_Shipping_Request.Date, dbo_Shipping_Request.Ship_Code,
> dbo_Shipping_Request.Freight_Charge_Acct_No,
> dbo_Shipping_Request.Shipment_Requested_By,
> dbo_Shipping_Request.Department_Number, dbo_Shipping_Request.Carrier,
> dbo_Shipping_Request.Freight_Amount,
> dbo_Shipping_Request.Date_Shipped,
> dbo_Shipping_Request.Number_Of_Cartons,
> dbo_Shipping_Request.Tracking_Number, dbo_Shipping_Request.Weight,
> dbo_Shipping_Request.Shipped_By,
> dbo_Shipping_Request.Description_Comments_1,
> dbo_Shipping_Request.Description_Comments_2,
> dbo_Shipping_Request.Description_Comments_3,
> dbo_Shipping_Request.Description_Comments_4,
> dbo_Shipping_Request.Description_Comments_5,
> dbo_Shipping_Request.Part_Number_1,
> dbo_Shipping_Request.Part_Number_2,
> dbo_Shipping_Request.Part_Number_3,
> dbo_Shipping_Request.Part_Number_4, dbo_Shipping_Request.Part_Number_5
> FROM dbo_Shipping_Request
> WHERE (((dbo_Shipping_Request.Date) Between [Start Date:] And [End
> Date:]));
>
> ==================== =====
===========3D
>
> On Jan 27, 9:22 pm, "Allen Browne" wrote:
>
>
>
> > Open the query that feeds the report, and switch it to SQL View (View
> > menu,
> > in query design.)
>
> > Post the SQL statement here.
>
> > "Del" wrote in message
>
> >news:04d4de75-aada-4183-b4f1-06967235c1fe@i12g2000prf.googl egroups.com...=

> > One more question.
> > I have a report that is tied to a parameter query. The query uses
> > between [Start Date:] and [End Date:]
> > I've set the parameters of the query as Allen described in his post.
> > The query runs fine. But when I run the report I recieve the same
> > error as I did with the query

>
> > ==================== =====
==================== =====3D=
=====3D
> > This expression is typed incorrectly or it is too complex to be
> > evaluated, a numeric expression may contain too many complicated
> > elements. Try simplifying the expression by assigning parts of the
> > expression variable.
> > ==================== =====
==================== =====3D=
=====3D
>
> > I've gone into the design mode of the report and set the parameters of
> > all the Date Fields to General Date. But I still get the error when I
> > try to run the report.- Hide quoted text -
>
> - Show quoted text -