"This expression is typed incorrectly, or it is too complex ..." e

G

Guest

Hi there,

I have encountered the following error message while processing a report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC = Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my system
generates it for me, until on the date 20/04/2007. I managed to generate the
reports on all other dates, including 21/04/2007, after transactions are
available. This proves that the query or report is not altered in anyway that
generated the above said error.

The strange thing is that running the query alone for the [Start Date] and
[End Date] = 20/04/2007, I will be able to generate the result in the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End Date] =
20/04/2007, I will get the above said error. I even took the liberty to try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007 and have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to solve this
problem?
 
A

Allen Browne

The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Hi there,

I have encountered the following error message while processing a report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my system
generates it for me, until on the date 20/04/2007. I managed to generate
the
reports on all other dates, including 21/04/2007, after transactions are
available. This proves that the query or report is not altered in anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start Date] and
[End Date] = 20/04/2007, I will be able to generate the result in the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End Date] =
20/04/2007, I will get the above said error. I even took the liberty to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007 and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to solve
this
problem?
Click to expand...
 
G

Guest

Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after the
modifications, and both of them worked perfectly as before the modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error message for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


Allen Browne said:
The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Hi there,

I have encountered the following error message while processing a report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my system
generates it for me, until on the date 20/04/2007. I managed to generate
the
reports on all other dates, including 21/04/2007, after transactions are
available. This proves that the query or report is not altered in anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start Date] and
[End Date] = 20/04/2007, I will be able to generate the result in the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End Date] =
20/04/2007, I will get the above said error. I even took the liberty to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007 and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to solve
this
problem?
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating fields, or
hitting issues.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


Allen Browne said:
The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

Kelvin Leong said:
Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End
Date] =
20/04/2007, I will get the above said error. I even took the liberty to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007 and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dear Allen,

I have changed the query as follows:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo, t.NetPayment,
d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1, t.Remark2,
t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
FROM [Transaction] AS t INNER JOIN DriverInfo AS d ON t.NRIC = d.NRIC
WHERE (((t.TodaysDate)>=[Start Date] And (t.TodaysDate)<[End Date]+1))
GROUP BY t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment, d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1,
t.Remark2, t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
ORDER BY d.VehicleNo DESC;

I found out that the query itself can return records for the date
20/04/2007. However, when I ran the report that ties to this query, it
generates the above error. Is it possible that the report itself has a
problem and not the query?

Thank you very much.
Kelvin


Allen Browne said:
Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating fields, or
hitting issues.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


Allen Browne said:
The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End
Date] =
20/04/2007, I will get the above said error. I even took the liberty to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007 and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

If you open the Transaction table in design view, what is the data type of
the field named TodaysDate?

Is it a Date/Time field?
Is it a Text field?
Or is it actually a calculated field from another query?

I'm just trying to pin down why JET is not making the match correctly, and a
mismatched type sounds likely.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Dear Allen,

I have changed the query as follows:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment,
d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1, t.Remark2,
t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
FROM [Transaction] AS t INNER JOIN DriverInfo AS d ON t.NRIC = d.NRIC
WHERE (((t.TodaysDate)>=[Start Date] And (t.TodaysDate)<[End Date]+1))
GROUP BY t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment, d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1,
t.Remark2, t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
ORDER BY d.VehicleNo DESC;

I found out that the query itself can return records for the date
20/04/2007. However, when I ran the report that ties to this query, it
generates the above error. Is it possible that the report itself has a
problem and not the query?

Thank you very much.
Kelvin


Allen Browne said:
Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating fields,
or
hitting issues.

Kelvin Leong said:
Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL
query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error
message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


:

The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try
using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

message
Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End
Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start
Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in
the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End
Date] =
20/04/2007, I will get the above said error. I even took the liberty
to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007
and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to
solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen,

The field TodaysDate is of Date / Time data type.

Allen Browne said:
If you open the Transaction table in design view, what is the data type of
the field named TodaysDate?

Is it a Date/Time field?
Is it a Text field?
Or is it actually a calculated field from another query?

I'm just trying to pin down why JET is not making the match correctly, and a
mismatched type sounds likely.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Dear Allen,

I have changed the query as follows:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment,
d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1, t.Remark2,
t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
FROM [Transaction] AS t INNER JOIN DriverInfo AS d ON t.NRIC = d.NRIC
WHERE (((t.TodaysDate)>=[Start Date] And (t.TodaysDate)<[End Date]+1))
GROUP BY t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment, d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1,
t.Remark2, t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
ORDER BY d.VehicleNo DESC;

I found out that the query itself can return records for the date
20/04/2007. However, when I ran the report that ties to this query, it
generates the above error. Is it possible that the report itself has a
problem and not the query?

Thank you very much.
Kelvin


Allen Browne said:
Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating fields,
or
hitting issues.

Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL
query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error
message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


:

The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try
using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven, DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

message
Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo, Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay, DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End
Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail, my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start
Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in
the
datasheet view.

However, if I run it as a report, having the [Start Date] and [End
Date] =
20/04/2007, I will get the above said error. I even took the liberty
to
try
out the range [Start Date] = 19/04/2007 and [End Date] = 21/04/2007
and
have
the same error.

I suspect that any result that have anything to do with the date =
20/04/2007 will generate this error. Can anyone help me on how to
solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, the query is fine, so it's reasonable to concentrate efforts on the
report.

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair

Next, you might check for expressions in the report that could get it wrong,
e.g. in the Control Source of controls, in the Sorting'n'Grouping dialog, or
in the Filter or Order By properties of the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Hi Allen,

The field TodaysDate is of Date / Time data type.

Allen Browne said:
If you open the Transaction table in design view, what is the data type
of
the field named TodaysDate?

Is it a Date/Time field?
Is it a Text field?
Or is it actually a calculated field from another query?

I'm just trying to pin down why JET is not making the match correctly,
and a
mismatched type sounds likely.

Kelvin Leong said:
Dear Allen,

I have changed the query as follows:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment,
d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1, t.Remark2,
t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
FROM [Transaction] AS t INNER JOIN DriverInfo AS d ON t.NRIC = d.NRIC
WHERE (((t.TodaysDate)>=[Start Date] And (t.TodaysDate)<[End Date]+1))
GROUP BY t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment, d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1,
t.Remark2, t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
ORDER BY d.VehicleNo DESC;

I found out that the query itself can return records for the date
20/04/2007. However, when I ran the report that ties to this query, it
generates the above error. Is it possible that the report itself has a
problem and not the query?

Thank you very much.
Kelvin


:

Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a
name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet
at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating
fields,
or
hitting issues.

message
Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL
query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after
the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error
message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


:

The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET
can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try
using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

message
Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End
Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail,
my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after
transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start
Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in
the
datasheet view.

However, if I run it as a report, having the [Start Date] and
[End
Date] =
20/04/2007, I will get the above said error. I even took the
liberty
to
try
out the range [Start Date] = 19/04/2007 and [End Date] =
21/04/2007
and
have
the same error.

I suspect that any result that have anything to do with the date
=
20/04/2007 will generate this error. Can anyone help me on how to
solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen,

I have found the problem. It's not the report or the query that is giving
the problem. It is due to the fact that one of the calculated fields is
causing the problem.

The data was in such a way that this calculated field is trying to divide by
zero. Once, I have removed this record, the problem is solved.

Again, thank you very much for your time in trying to solve my problem.

Allen Browne said:
Okay, the query is fine, so it's reasonable to concentrate efforts on the
report.

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair

Next, you might check for expressions in the report that could get it wrong,
e.g. in the Control Source of controls, in the Sorting'n'Grouping dialog, or
in the Filter or Order By properties of the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelvin Leong said:
Hi Allen,

The field TodaysDate is of Date / Time data type.

Allen Browne said:
If you open the Transaction table in design view, what is the data type
of
the field named TodaysDate?

Is it a Date/Time field?
Is it a Text field?
Or is it actually a calculated field from another query?

I'm just trying to pin down why JET is not making the match correctly,
and a
mismatched type sounds likely.

Dear Allen,

I have changed the query as follows:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment,
d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1, t.Remark2,
t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
FROM [Transaction] AS t INNER JOIN DriverInfo AS d ON t.NRIC = d.NRIC
WHERE (((t.TodaysDate)>=[Start Date] And (t.TodaysDate)<[End Date]+1))
GROUP BY t.TodaysDate, t.PaymentFrom, t.PaymentTo, d.VehicleNo,
t.NetPayment, d.ManagementCo, t.auto, t.Date, t.DiscountRM, t.Remark1,
t.Remark2, t.Remark3, t.DiscountGiven, d.DailyRate, d.Radio, d.MiscPay,
d.AgreementStartDate, d.AgreementEndDate, d.AgreementStatus
ORDER BY d.VehicleNo DESC;

I found out that the query itself can return records for the date
20/04/2007. However, when I ran the report that ties to this query, it
generates the above error. Is it possible that the report itself has a
problem and not the query?

Thank you very much.
Kelvin


:

Okay, there must be something else JET is not understanding.

You seem to have a table named Transaction. That's a reserved word, so
perhaps that's the problem. Try adding square brackets around the name
everywhere it appears in the query, e.g.:
WHERE (([Transaction].TodaysDate >= [Start Date])

Alternatively, select the Transaction table in the upper pane of query
design, and enter a different name beside the Alias property. Try a
name
such as TransTable.

It could be other mismatches, but the reserved word is the obvious bet
at
this stage.

There's a list of the names that can cause you problems here:
http://allenbrowne.com/AppIssueBadWord.html
There's quite a few of them, but worth referring to when creating
fields,
or
hitting issues.

message
Dear Allen,

Thank you for your quick reply. I have made modifications on the SQL
query
as advised but I have the same issue with the date 20/04/2007.

I have even tried other dates like 19/04/2007 and 21/04/2007, after
the
modifications, and both of them worked perfectly as before the
modifications.

I have tried different possible formats as at
http://allenbrowne.com/ser-36.html but I still get the same error
message
for
the date 20/04/2007.

I hope there is another solution to this.

Thank you very much.
Kelvin


:

The message just means Access failed to understand something.
It was probably the non-US dates.
You can solve that by declaring the parameters.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time

This is one of the 3 issues with your d/m/y date formats where JET
can
get
it wrong. For the others, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If TodaysDate has a time component as well as a day, you might try
using
this kind of thing:

Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction]
ON DriverInfo.NRIC = Transaction.NRIC
WHERE ((Transaction.TodaysDate >= [Start Date])
AND (Transaction.TodaysDate < [End Date] + 1))
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
ORDER BY DriverInfo.VehicleNo DESC;

message
Hi there,

I have encountered the following error message while processing a
report:
"This expression is typed incorrectly, or it is too complex to be
evaluated...."

The query of my report is as follows:
SELECT Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
FROM DriverInfo INNER JOIN [Transaction] ON DriverInfo.NRIC =
Transaction.NRIC
GROUP BY Transaction.TodaysDate, Transaction.PaymentFrom,
Transaction.PaymentTo, DriverInfo.VehicleNo,
Transaction.NetPayment,
DriverInfo.ManagementCo, Transaction.auto, Transaction.Date,
Transaction.DiscountRM, Transaction.Remark1, Transaction.Remark2,
Transaction.Remark3, Transaction.DiscountGiven,
DriverInfo.DailyRate,
DriverInfo.Radio, DriverInfo.MiscPay,
DriverInfo.AgreementStartDate,
DriverInfo.AgreementEndDate, DriverInfo.AgreementStatus
HAVING (((Transaction.TodaysDate) Between [Start Date] And [End
Date]))
ORDER BY DriverInfo.VehicleNo DESC;

The problem:
I have been using this report on a daily basis and without fail,
my
system
generates it for me, until on the date 20/04/2007. I managed to
generate
the
reports on all other dates, including 21/04/2007, after
transactions
are
available. This proves that the query or report is not altered in
anyway
that
generated the above said error.

The strange thing is that running the query alone for the [Start
Date]
and
[End Date] = 20/04/2007, I will be able to generate the result in
the
datasheet view.

However, if I run it as a report, having the [Start Date] and
[End
Date] =
20/04/2007, I will get the above said error. I even took the
liberty
to
try
out the range [Start Date] = 19/04/2007 and [End Date] =
21/04/2007
and
have
the same error.

I suspect that any result that have anything to do with the date
=
20/04/2007 will generate this error. Can anyone help me on how to
solve
this
problem?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Great. And thanks for posting the solution. It helps if someone else is
searching for an answer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Expression is typed incorrectly or is too complex..." 2
This expression is typed incorrectly, or it is too complex to be e 6
This expression is typed incorrectly, or is too complex 2
This Expression is typed incorrectly, or it is too complex to be evaluated 2
This expression is typed incorrectly, or it is to complex to be ev 1

Top