Syntax error (missing operator) in query expression

The design view can not display a UNION query.

Quote: Originally Posted by RuralGuy 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

Quote: Originally Posted by MikeTheBike 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.

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

Quote: Originally Posted by 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

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