Expression is too complex....

[Guest]

I have a form that is used to obtain parameters that are used to run a report
that works fine for one set of values, but when I simply change one of the
values (from one employee id to another employee id) I then get the foolowing
error message:

"This expressions is typed incorrectly, or it is too complex to be
evaluated....."

WHY ??? It seems to work just fine for all other employee ids I have
tested...why not for one particular id ? I have already done the
repair/compact routine....no help.

Any ideas ?

Thanks !

 

[Guest]

Is the employee ID straight numbers, letters, or some combination to include
symbols like single quote?
Other symbols might be your problem. What is the ID that does not work and
what is an example of one that does?

 

[Guest]

Employee ID is a long integer...in reality all id numbers are 999999 or less.

992992 works, 993131 does not. I have found several others that do not work
as well, and it doesn't seem to have anything to do with the date range I
select either, as I have tried multiple ranges. For an ID with no records in
the selected date range, the report runs with blank results.

 

[Guest]

I assume the report has a query for record source. Can you post the SQL?

 

[Guest]

Here's the SQL of the query behind the report in question:

SELECT retro_old_hrly_rate_calculation.employee_num,
retro_old_hrly_rate_calculation.employee_name,
retro_old_hrly_rate_calculation.[Cost Center],
retro_old_hrly_rate_calculation.[Old Hrly Rate], [Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]) AS [New Hrly Rate],
[forms]![process_retro_calc]![incr_pct] AS [Percentage of Increase], [Old
Hrly Rate]*[forms]![process_retro_calc]![incr_pct] AS [Amount of Increase],
labor_distribution.ppe_date, labor_distribution.reghrs,
labor_distribution.othrs, labor_distribution.vachrs,
labor_distribution.holhrs, labor_distribution.sickhrs, ([reghrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([reghrs]*[Old Hrly Rate])
AS retro_reg, ([othrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct])*1.5)-([othrs]*[Old Hrly
Rate]*1.5) AS retro_ot, ([vachrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([vachrs]*[Old Hrly Rate])
AS retro_vac, ([holhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([holhrs]*[Old Hrly Rate])
AS retro_hol, ([sickhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([sickhrs]*[Old Hrly
Rate]) AS retro_sick
FROM retro_old_hrly_rate_calculation INNER JOIN labor_distribution ON
retro_old_hrly_rate_calculation.employee_num = labor_distribution.employee_num
WHERE
(((retro_old_hrly_rate_calculation.employee_num)=[forms]![process_retro_calc]![Select_employee])
AND
((labor_distribution.ppe_date)>=[forms]![process_retro_calc]![select_ppe]));

 

[Guest]

I do not see anything - maybe someone else can. The only thing that comes
to mine is the possibility of leading and training spaces in the database of
if you happen to be pasting criteria in the form or spaces in lookup table if
the form is using a combo or list box.

Here's the SQL of the query behind the report in question:

SELECT retro_old_hrly_rate_calculation.employee_num,
retro_old_hrly_rate_calculation.employee_name,
retro_old_hrly_rate_calculation.[Cost Center],
retro_old_hrly_rate_calculation.[Old Hrly Rate], [Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]) AS [New Hrly Rate],
[forms]![process_retro_calc]![incr_pct] AS [Percentage of Increase], [Old
Hrly Rate]*[forms]![process_retro_calc]![incr_pct] AS [Amount of Increase],
labor_distribution.ppe_date, labor_distribution.reghrs,
labor_distribution.othrs, labor_distribution.vachrs,
labor_distribution.holhrs, labor_distribution.sickhrs, ([reghrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([reghrs]*[Old Hrly Rate])
AS retro_reg, ([othrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct])*1.5)-([othrs]*[Old Hrly
Rate]*1.5) AS retro_ot, ([vachrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([vachrs]*[Old Hrly Rate])
AS retro_vac, ([holhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([holhrs]*[Old Hrly Rate])
AS retro_hol, ([sickhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([sickhrs]*[Old Hrly
Rate]) AS retro_sick
FROM retro_old_hrly_rate_calculation INNER JOIN labor_distribution ON
retro_old_hrly_rate_calculation.employee_num = labor_distribution.employee_num
WHERE
(((retro_old_hrly_rate_calculation.employee_num)=[forms]![process_retro_calc]![Select_employee])
AND
((labor_distribution.ppe_date)>=[forms]![process_retro_calc]![select_ppe]));

I assume the report has a query for record source. Can you post the SQL?

 

[Guest]

Thanks for looking at it....I am not pasting data into the criteria on the
form. I either type it in directly or select it from the combo box if I do
not know the ID.

I do not see anything - maybe someone else can. The only thing that comes
to mine is the possibility of leading and training spaces in the database of
if you happen to be pasting criteria in the form or spaces in lookup table if
the form is using a combo or list box.

Here's the SQL of the query behind the report in question:

SELECT retro_old_hrly_rate_calculation.employee_num,
retro_old_hrly_rate_calculation.employee_name,
retro_old_hrly_rate_calculation.[Cost Center],
retro_old_hrly_rate_calculation.[Old Hrly Rate], [Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]) AS [New Hrly Rate],
[forms]![process_retro_calc]![incr_pct] AS [Percentage of Increase], [Old
Hrly Rate]*[forms]![process_retro_calc]![incr_pct] AS [Amount of Increase],
labor_distribution.ppe_date, labor_distribution.reghrs,
labor_distribution.othrs, labor_distribution.vachrs,
labor_distribution.holhrs, labor_distribution.sickhrs, ([reghrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([reghrs]*[Old Hrly Rate])
AS retro_reg, ([othrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct])*1.5)-([othrs]*[Old Hrly
Rate]*1.5) AS retro_ot, ([vachrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([vachrs]*[Old Hrly Rate])
AS retro_vac, ([holhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([holhrs]*[Old Hrly Rate])
AS retro_hol, ([sickhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([sickhrs]*[Old Hrly
Rate]) AS retro_sick
FROM retro_old_hrly_rate_calculation INNER JOIN labor_distribution ON
retro_old_hrly_rate_calculation.employee_num = labor_distribution.employee_num
WHERE
(((retro_old_hrly_rate_calculation.employee_num)=[forms]![process_retro_calc]![Select_employee])
AND
((labor_distribution.ppe_date)>=[forms]![process_retro_calc]![select_ppe]));

I assume the report has a query for record source. Can you post the SQL?

:

Employee ID is a long integer...in reality all id numbers are 999999 or less.

992992 works, 993131 does not. I have found several others that do not work
as well, and it doesn't seem to have anything to do with the date range I
select either, as I have tried multiple ranges. For an ID with no records in
the selected date range, the report runs with blank results.

:

Is the employee ID straight numbers, letters, or some combination to include
symbols like single quote?
Other symbols might be your problem. What is the ID that does not work and
what is an example of one that does?

:

I have a form that is used to obtain parameters that are used to run a report
that works fine for one set of values, but when I simply change one of the
values (from one employee id to another employee id) I then get the foolowing
error message:

"This expressions is typed incorrectly, or it is too complex to be
evaluated....."

WHY ??? It seems to work just fine for all other employee ids I have
tested...why not for one particular id ? I have already done the
repair/compact routine....no help.

Any ideas ?

Thanks !

 

[Guest]

FYI - I have re-written the query with parameters in it and stopped using the
form....still get the same results. Some ID's work, some give the "too
complex" error.

Anyone have any ideas ???? I'm baffled (and a little bit frustrated)

Thanks !!

I do not see anything - maybe someone else can. The only thing that comes
to mine is the possibility of leading and training spaces in the database of
if you happen to be pasting criteria in the form or spaces in lookup table if
the form is using a combo or list box.

Here's the SQL of the query behind the report in question:

SELECT retro_old_hrly_rate_calculation.employee_num,
retro_old_hrly_rate_calculation.employee_name,
retro_old_hrly_rate_calculation.[Cost Center],
retro_old_hrly_rate_calculation.[Old Hrly Rate], [Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]) AS [New Hrly Rate],
[forms]![process_retro_calc]![incr_pct] AS [Percentage of Increase], [Old
Hrly Rate]*[forms]![process_retro_calc]![incr_pct] AS [Amount of Increase],
labor_distribution.ppe_date, labor_distribution.reghrs,
labor_distribution.othrs, labor_distribution.vachrs,
labor_distribution.holhrs, labor_distribution.sickhrs, ([reghrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([reghrs]*[Old Hrly Rate])
AS retro_reg, ([othrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct])*1.5)-([othrs]*[Old Hrly
Rate]*1.5) AS retro_ot, ([vachrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([vachrs]*[Old Hrly Rate])
AS retro_vac, ([holhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([holhrs]*[Old Hrly Rate])
AS retro_hol, ([sickhrs]*[Old Hrly
Rate]*(1+[forms]![process_retro_calc]![incr_pct]))-([sickhrs]*[Old Hrly
Rate]) AS retro_sick
FROM retro_old_hrly_rate_calculation INNER JOIN labor_distribution ON
retro_old_hrly_rate_calculation.employee_num = labor_distribution.employee_num
WHERE
(((retro_old_hrly_rate_calculation.employee_num)=[forms]![process_retro_calc]![Select_employee])
AND
((labor_distribution.ppe_date)>=[forms]![process_retro_calc]![select_ppe]));

I assume the report has a query for record source. Can you post the SQL?

:

Employee ID is a long integer...in reality all id numbers are 999999 or less.

992992 works, 993131 does not. I have found several others that do not work
as well, and it doesn't seem to have anything to do with the date range I
select either, as I have tried multiple ranges. For an ID with no records in
the selected date range, the report runs with blank results.

:

Is the employee ID straight numbers, letters, or some combination to include
symbols like single quote?
Other symbols might be your problem. What is the ID that does not work and
what is an example of one that does?

:

I have a form that is used to obtain parameters that are used to run a report
that works fine for one set of values, but when I simply change one of the
values (from one employee id to another employee id) I then get the foolowing
error message:

"This expressions is typed incorrectly, or it is too complex to be
evaluated....."

WHY ??? It seems to work just fine for all other employee ids I have
tested...why not for one particular id ? I have already done the
repair/compact routine....no help.

Any ideas ?

Thanks !