Syntax error (missing operator) in query expression

[berio211]

"I keep getting the following error when I try to modify a query: Syntax error (missing operator) in query expression 'ADPINTER.'. when this happens the qyuery can no longer be seen in design view it changes to SQL view and I can't get it back to design view. the following is the sql view:

SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.UNION , IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross INTO OCIP FROM EMPMAST INNER JOIN ADPINTER ON (ADPINTER.COMPANY=EMPMAST.[CO CODE]) AND (EMPMAST.EMPNO=ADPINTER.EMPNO) AND (EMPMAST.EMPNO=ADPINTER.EMPNO) WHERE (((ADPINTER.JOB_NUMBER) Between forms!autoload![From Job] And forms!autoload![To Job]) And ((ADPINTER.ENDPERIOD) Between forms!autoload!STARTDATE And forms!autoload!ENDINGDATE) And ((ADPINTER.category)"GC") And ((ADPINTER!EMPNO)909674 And (ADPINTER!EMPNO)909738 And (ADPINTER!EMPNO)902696 And (ADPINTER!EMPNO)6810 And (ADPINTER!EMPNO)7447 And (ADPINTER!EMPNO)902589 And (ADPINTER!EMPNO)909609 And (ADPINTER!EMPNO)939635 And (ADPINTER!EMPNO)909640 And (ADPINTER!EMPNO)909659 And (ADPINTER!EMPNO)909671 And (ADPINTER!EMPNO)909690 And (ADPINTER!EMPNO)909708 And (ADPINTER!EMPNO)909714 And (ADPINTER!EMPNO)909749 And (ADPINTER!EMPNO)909758 And (ADPINTER!EMPNO)909759 And (ADPINTER!EMPNO)909762 And (ADPINTER!EMPNO)909594)); 

Any assistance would be appreciated. Thanks Lou"

[RuralGuy]

The design view can not display a UNION query.

[MikeTheBike]

The design view can not display a UNION query.

Hi

As RuralGuy said, Union Queries can only be displayed in SQL view, not in query designer.

However, this does not seem to be a UNION query.

I assume it fails at ‘ADPINTER.UNION’.

If so then it is likely the keyword UNION field name is triggering this (ie it THINKS it’s a union query).

I suggest this should cure the problem (but can’t guarantee it)

ADPINTER.[UNION]

On this basis, if it works, you will need to put [] round all instances of the field name UNION !!

If it doesn't work, then you will need to change your field name, which is probable the best solution any way !?

HTH

MTB

[berio211]

Hi

As RuralGuy said, Union Queries can only be displayed in SQL view, not in query designer.

However, this does not seem to be a UNION query.

I assume it fails at ‘ADPINTER.UNION’.

If so then it is likely the keyword UNION field name is triggering this (ie it THINKS it’s a union query).

I suggest this should cure the problem (but can’t guarantee it)

ADPINTER.[UNION]

On this basis, if it works, you will need to put [] round all instances of the field name UNION !!

If it doesn't work, then you will need to change your field name, which is probable the best solution any way !?

HTH

MTB

Thank you. Adding the brackets around UNION did fix my problem and I was able to get the query back in Design mode.

[berio211]

As I mentioned before thank you very much for your help. I would like to ask another question which maybe you can assist me with. The question is as follows:

What I am trying to do is create a query in Access that will help in my Worker's Comp reporting. I already have queries set up but I am missing one component. I have a query that states IF payrollold is greater than 750 THEN 750 IF not THEN payrollold. However I need to add a twist in that I need to add a statement which would give me the following result: IF hours is greater then 80 THEN 1500 (in the payroll column) IF not then 750.

I am having trouble mixing the hours part with the payroll part. Below is the SQL view of the query I need to modify.

SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION], IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")

Any help would be greatly appreciated. Thanks Lou

[MikeTheBike]

As I mentioned before thank you very much for your help. I would like to ask another question which maybe you can assist me with. The question is as follows:

What I am trying to do is create a query in Access that will help in my Worker's Comp reporting. I already have queries set up but I am missing one component. I have a query that states IF payrollold is greater than 750 THEN 750 IF not THEN payrollold. However I need to add a twist in that I need to add a statement which would give me the following result: IF hours is greater then 80 THEN 1500 (in the payroll column) IF not then 750.

I am having trouble mixing the hours part with the payroll part. Below is the SQL view of the query I need to modify.

SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION], IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")

Any help would be greatly appreciated. Thanks Lou

Hi

Glad you fixed the first problem.

You should start a new thread for a different problem, however, as I am here,I think you need a nested IIF ie

Change this

IIF([payrollold]>750,750,[payrollold]) AS Payroll

to this

IIF([payrollold]>750,IIF([reg_hrs]+[ovr_hrs]+[other_hours]>80,150,750),[payrollold]) AS Payroll


??

You explanation/logic of the required result(s) is not very explict, but I am sure you can work out what is requied for the desired resulty if the above is incorrect.


MTB