Date/Time Filter problem

[Cindy]

I am trying to filter a report using the date/time filter

Between [Start Date] and [End Date]

and the blasted thing works fine for 12/1/2009 - 12/31/2009 but then when I
try to pull the report for 1/1/2010 - 1/31/2010 I get the "This expression is
typed incorrectly or it is too complex to be evaluated" error.

This is in a query (I have copied it below) based on a table that is
populated by an Append Query. I have imported the data from McLeod (a
trucking database) into Excel and linked the Excel file.

Any clues on what could be causing this?

SELECT [Prof Rev4].T0Id, [Prof Rev4].T2Id, [Prof Rev4].T0Customer_Id, [Prof
Rev4].T3City_Name, [Prof Rev4].T3State, [Prof Rev4].T4City_Name, [Prof
Rev4].T4State, [Prof Rev4].T8Id, [Prof Rev4].Departure, [Prof Rev4].Arrival,
DateDiff("n",[Departure],[Arrival]) AS RunTimeMinutes, [Prof
Rev4].T0Total_Charge, IIf([T2Loaded]="E",0,[T0Total_Charge]) AS TotalCharge,
Round([RunTimeMinutes]*([T0Total_Charge]/[TotalBilled]),0) AS
RuntimeProrated, [Prof Rev4].T0Freight_Charge,
IIf([T2Loaded]="E",0,[T0Freight_Charge]) AS FreightCharge, [Prof
Rev4].T0Otherchargetotal, IIf([T2Loaded]="E",0,[T0Otherchargetotal]) AS
OtherCharge, [Prof Rev4].T2Loaded, Customer.[Customer Name],
Customer.[Customer City], Customer.[Customer State]
FROM Customer INNER JOIN ([Prof Rev4] INNER JOIN qryMovementBillings ON
[Prof Rev4].T2Id = qryMovementBillings.MovementNo) ON Customer.[Customer #] =
[Prof Rev4].T0Customer_Id
WHERE ((([Prof Rev4].Arrival) Between [Start Date] And [End Date]));

I have Access 2007 and Vista.

Thanks!!
Cindy

 

[Cindy]

After a bit of testing I have discovered this interesting bit. It will give
me the records from 1/1/2010 through 1/20/2010 but if I make the End Date
anything after 1/20/2010 I get the "This expression typed incorrectly, blah,
blah" error.

Cindy

 

[John W. Vinson]

I am trying to filter a report using the date/time filter

Between [Start Date] and [End Date]

and the blasted thing works fine for 12/1/2009 - 12/31/2009 but then when I
try to pull the report for 1/1/2010 - 1/31/2010 I get the "This expression is
typed incorrectly or it is too complex to be evaluated" error.

This is in a query (I have copied it below) based on a table that is
populated by an Append Query. I have imported the data from McLeod (a
trucking database) into Excel and linked the Excel file.

Any clues on what could be causing this?

Try explicitly defining the parameter as a Date/Time value:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Prof Rev4].T0Id, [Prof Rev4].T2Id, [Prof Rev4].T0Customer_Id, [Prof
Rev4].T3City_Name, [Prof Rev4].T3State, [Prof Rev4].T4City_Name, [Prof
Rev4].T4State, [Prof Rev4].T8Id, [Prof Rev4].Departure, [Prof Rev4].Arrival,
DateDiff("n",[Departure],[Arrival]) AS RunTimeMinutes, [Prof
Rev4].T0Total_Charge, IIf([T2Loaded]="E",0,[T0Total_Charge]) AS TotalCharge,
Round([RunTimeMinutes]*([T0Total_Charge]/[TotalBilled]),0) AS
RuntimeProrated, [Prof Rev4].T0Freight_Charge,
IIf([T2Loaded]="E",0,[T0Freight_Charge]) AS FreightCharge, [Prof
Rev4].T0Otherchargetotal, IIf([T2Loaded]="E",0,[T0Otherchargetotal]) AS
OtherCharge, [Prof Rev4].T2Loaded, Customer.[Customer Name],
Customer.[Customer City], Customer.[Customer State]
FROM Customer INNER JOIN ([Prof Rev4] INNER JOIN qryMovementBillings ON
[Prof Rev4].T2Id = qryMovementBillings.MovementNo) ON Customer.[Customer #] =
[Prof Rev4].T0Customer_Id
WHERE ((([Prof Rev4].Arrival) Between [Start Date] And [End Date]));

You may also want to consider creating a small form, frmCrit, with textboxes
for the start and end time, and instead of popping up the prompt use

= [Forms]![frmCrit]![txtStart] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtEnd]

as your criteria; this will cover records where the Arrival is sometime during
the last day of the range. As it is the BETWEEN will only pick up records
until midnight on the start of that day.

 

[Cindy]

Ken & John,

Well, I have done what you both said, explicitly defining the parameter.
Didn't help. I made a form and that didn't help.

So I removed the filter completely and tried to run the report, it gave me
an error that simply states "Overflow". As a test I removed the January
data, leaving only Dec data and it will run. When I try to run January Only
data I again get the "Overflow" error.

I researched the "Overflow" error and then checked all my fields and they
are big enough for all the data, I have looked at the source data and there
are no errors or anomalies. I have repulled the data from McLeod and
imported that into my database with the same result. Since the report will
run for 1 Jan thru 20 Jan I looked at data dated after 20 January but cannot
see anything different.

I am at a total loss and could use any help. I was supposed to present all
of this tomorrow but they will only get Dec now unless a miracle happens.

Thanks!

Cindy

I am trying to filter a report using the date/time filter

Between [Start Date] and [End Date]

and the blasted thing works fine for 12/1/2009 - 12/31/2009 but then when I
try to pull the report for 1/1/2010 - 1/31/2010 I get the "This expression is
typed incorrectly or it is too complex to be evaluated" error.

This is in a query (I have copied it below) based on a table that is
populated by an Append Query. I have imported the data from McLeod (a
trucking database) into Excel and linked the Excel file.

Any clues on what could be causing this?

Try explicitly defining the parameter as a Date/Time value:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Prof Rev4].T0Id, [Prof Rev4].T2Id, [Prof Rev4].T0Customer_Id, [Prof
Rev4].T3City_Name, [Prof Rev4].T3State, [Prof Rev4].T4City_Name, [Prof
Rev4].T4State, [Prof Rev4].T8Id, [Prof Rev4].Departure, [Prof Rev4].Arrival,
DateDiff("n",[Departure],[Arrival]) AS RunTimeMinutes, [Prof
Rev4].T0Total_Charge, IIf([T2Loaded]="E",0,[T0Total_Charge]) AS TotalCharge,
Round([RunTimeMinutes]*([T0Total_Charge]/[TotalBilled]),0) AS
RuntimeProrated, [Prof Rev4].T0Freight_Charge,
IIf([T2Loaded]="E",0,[T0Freight_Charge]) AS FreightCharge, [Prof
Rev4].T0Otherchargetotal, IIf([T2Loaded]="E",0,[T0Otherchargetotal]) AS
OtherCharge, [Prof Rev4].T2Loaded, Customer.[Customer Name],
Customer.[Customer City], Customer.[Customer State]
FROM Customer INNER JOIN ([Prof Rev4] INNER JOIN qryMovementBillings ON
[Prof Rev4].T2Id = qryMovementBillings.MovementNo) ON Customer.[Customer #] =
[Prof Rev4].T0Customer_Id
WHERE ((([Prof Rev4].Arrival) Between [Start Date] And [End Date]));

You may also want to consider creating a small form, frmCrit, with textboxes
for the start and end time, and instead of popping up the prompt use

= [Forms]![frmCrit]![txtStart] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtEnd]

as your criteria; this will cover records where the Arrival is sometime during
the last day of the range. As it is the BETWEEN will only pick up records
until midnight on the start of that day.

 

[Cindy]

Ken. I found it!!!! I finally printed the datasheet from the query and
went over Jan line by line (only 2500 lines but I'm persistent) and found 1
record where the Total Charge was $0.00 and the filter code was L. Of
course, it wanted a currency amount. That error is also in the McLeod
database (where I imported the data from) and I wonder if it was ever
invoiced. Anyway, I changed that in my database and Walla!!! it worked.
Now I have everything for my presentation tomorrow and I'm a happy girl.

Thanks for hanging in there with me.

Cindy

Cindy:

Its very hard to say at this distance just where the problem might lie. Your
query includes a bunch of computed columns using expressions. I'd suggest
taking all of these out of the query, but leaving the date parameters in and
then open it and include the post 20 January dates in the range. If it opens
successfully put each computed column back in one by one until it falls over.
You'll then know where to start looking for the root cause of the problem.
If it still fails without any of the computed columns you'll need to dig
deeper. As the query draws on another query its possible there could be a
problem in that, or it could be in the base data in the tables or in the
Excel worksheet. Have you tried importing the data from Excel rather than
linking to it?

Ken Sheridan
Stafford, England

Ken & John,

Well, I have done what you both said, explicitly defining the parameter.
Didn't help. I made a form and that didn't help.

So I removed the filter completely and tried to run the report, it gave me
an error that simply states "Overflow". As a test I removed the January
data, leaving only Dec data and it will run. When I try to run January Only
data I again get the "Overflow" error.

I researched the "Overflow" error and then checked all my fields and they
are big enough for all the data, I have looked at the source data and there
are no errors or anomalies. I have repulled the data from McLeod and
imported that into my database with the same result. Since the report will
run for 1 Jan thru 20 Jan I looked at data dated after 20 January but cannot
see anything different.

I am at a total loss and could use any help. I was supposed to present all
of this tomorrow but they will only get Dec now unless a miracle happens.

Thanks!

Cindy

I am trying to filter a report using the date/time filter

[quoted text clipped - 37 lines]

the last day of the range. As it is the BETWEEN will only pick up records
until midnight on the start of that day.

--



.

 

[Guest]

Ken & John,

Well, I have done what you both said, explicitly defining the parameter.
Didn't help. I made a form and that didn't help.

So I removed the filter completely and tried to run the report, it gave me
an error that simply states "Overflow". As a test I removed the January
data, leaving only Dec data and it will run. When I try to run January
Only
data I again get the "Overflow" error.

I researched the "Overflow" error and then checked all my fields and they
are big enough for all the data, I have looked at the source data and
there
are no errors or anomalies. I have repulled the data from McLeod and
imported that into my database with the same result. Since the report
will
run for 1 Jan thru 20 Jan I looked at data dated after 20 January but
cannot
see anything different.

I am at a total loss and could use any help. I was supposed to present
all
of this tomorrow but they will only get Dec now unless a miracle happens.

Thanks!

Cindy

I am trying to filter a report using the date/time filter

Between [Start Date] and [End Date]

and the blasted thing works fine for 12/1/2009 - 12/31/2009 but then
when I
try to pull the report for 1/1/2010 - 1/31/2010 I get the "This
expression is
typed incorrectly or it is too complex to be evaluated" error.

This is in a query (I have copied it below) based on a table that is
populated by an Append Query. I have imported the data from McLeod (a
trucking database) into Excel and linked the Excel file.

Any clues on what could be causing this?

Try explicitly defining the parameter as a Date/Time value:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Prof Rev4].T0Id, [Prof Rev4].T2Id, [Prof Rev4].T0Customer_Id,
[Prof
Rev4].T3City_Name, [Prof Rev4].T3State, [Prof Rev4].T4City_Name, [Prof
Rev4].T4State, [Prof Rev4].T8Id, [Prof Rev4].Departure, [Prof
Rev4].Arrival,
DateDiff("n",[Departure],[Arrival]) AS RunTimeMinutes, [Prof
Rev4].T0Total_Charge, IIf([T2Loaded]="E",0,[T0Total_Charge]) AS
TotalCharge,
Round([RunTimeMinutes]*([T0Total_Charge]/[TotalBilled]),0) AS
RuntimeProrated, [Prof Rev4].T0Freight_Charge,
IIf([T2Loaded]="E",0,[T0Freight_Charge]) AS FreightCharge, [Prof
Rev4].T0Otherchargetotal, IIf([T2Loaded]="E",0,[T0Otherchargetotal]) AS
OtherCharge, [Prof Rev4].T2Loaded, Customer.[Customer Name],
Customer.[Customer City], Customer.[Customer State]
FROM Customer INNER JOIN ([Prof Rev4] INNER JOIN qryMovementBillings ON
[Prof Rev4].T2Id = qryMovementBillings.MovementNo) ON Customer.[Customer
#] =
[Prof Rev4].T0Customer_Id
WHERE ((([Prof Rev4].Arrival) Between [Start Date] And [End Date]));

You may also want to consider creating a small form, frmCrit, with
textboxes
for the start and end time, and instead of popping up the prompt use

= [Forms]![frmCrit]![txtStart] AND < DateAdd("d", 1,
[Forms]![frmCrit]![txtEnd]

as your criteria; this will cover records where the Arrival is sometime
during
the last day of the range. As it is the BETWEEN will only pick up records
until midnight on the start of that day.