HELP : query error (Error 3071)

[eddii]

i have connected two queries i get this error

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

this is the sql view of error query ( final1)

SELECT imtemasterqry.[New No], imtemasterqry.RegDate, imtemasterqry.IMTENAME, imtemasterqry.IMTENUMBER, imtemasterqry.SHOP, imtemasterqry.PLANT, imtemasterqry.mfgdate, imtemasterqry.FREQ, imtemasterqry.monthofcal, imtemasterqry.STATUS, imtemasterqry.REMARKS, imtemasterqry.DUMMY4, imtemasterqry.damar, imtemasterqry.nuber, imtemasterqry.TOTMON, imtemasterqry.divf, imtemasterqry.divf1, imtemasterqry.addd, imtemasterqry.mfg, imtemasterqry.SYSNEXTDUEDATE, imtemasterqry.pp, imtemasterqry.NewDateField, IIf([STATUSCER]="ok",1,0) AS OK_Count, IIf([STATUSCER]="ng",1,0) AS ng_Count, IIf([STATUSCER]="pending",1,0) AS pending_Count, IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, IIf(IsNull([statuscer]),1,0) AS blank_count, IIf([STATUSCER]="scrap",1,0) AS scrap_Count, IIf([NEXTCALDUEDATE]=[SYSNEXTDUEDATE],1,0) AS duecheck, imtemasterqry.monthofcal AS Mymonth
FROM imtemasterqry LEFT JOIN calibcertiqry ON (imtemasterqry.IMTENUMBER = calibcertiqry.IMTENUMBER) AND (imtemasterqry.allformula1 = calibcertiqry.formatfor1);



this is the sql view of working query ( final)
SELECT imtemasterqry.[New No], imtemasterqry.RegDate, imtemasterqry.IMTENAME, imtemasterqry.IMTENUMBER, imtemasterqry.SHOP, imtemasterqry.PLANT, imtemasterqry.mfgdate, imtemasterqry.FREQ, imtemasterqry.monthofcal, imtemasterqry.STATUS, imtemasterqry.REMARKS, imtemasterqry.DUMMY4, imtemasterqry.damar, imtemasterqry.nuber, imtemasterqry.TOTMON, imtemasterqry.divf, imtemasterqry.divf1, imtemasterqry.addd, imtemasterqry.mfg, imtemasterqry.SYSNEXTDUEDATE, imtemasterqry.pp, imtemasterqry.NewDateField, IIf([STATUSCER]="ok",1,0) AS OK_Count, IIf([STATUSCER]="ng",1,0) AS ng_Count, IIf([STATUSCER]="pending",1,0) AS pending_Count, IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, IIf(IsNull([statuscer]),1,0) AS blank_count, IIf([STATUSCER]="scrap",1,0) AS scrap_Count, IIf([NEXTCALDUEDATE]=[SYSNEXTDUEDATE],1,0) AS duecheck, imtemasterqry.monthofcal AS Mymonth
FROM imtemasterqry LEFT JOIN calibcertiqry ON (imtemasterqry.IMTENUMBER = calibcertiqry.IMTENUMBER) AND (imtemasterqry.allformula = calibcertiqry.formatfor);


name of the query is final and final1

i want both the query to work can you help in correcting or finding the error

so that i can put that result in one query to take report

 

[jdraw]

It isn't clear to me what you are trying to do. I see a number of tables, but no tables have primary keys, and there are no relationships between tables.

I don't see anything wrong in the sql....

 

[eddii]

Thanks jdraw Re: HELP : query error (Error 3071)

Thanks for the reply jdraw

i have two tables which are connected using join properties


iam able to get report using the query named (final)

sql view of link query:

FROM imtemasterqry LEFT JOIN calibcertiqry ON (imtemasterqry.IMTENUMBER = calibcertiqry.IMTENUMBER) AND (imtemasterqry.allformula = calibcertiqry.formatfor);



but with same conditions iam not able to get the report usiing query named (final1)


sql view of link query:
FROM imtemasterqry LEFT JOIN calibcertiqry ON (imtemasterqry.IMTENUMBER = calibcertiqry.IMTENUMBER) AND (imtemasterqry.allformula1 = calibcertiqry.formatfor1);

i dont know what is the mistake in the query i have done

 

[MSAccessRookie]

Here is an instance where, despite how much information has been provided, it still is not enough to make a resolution to the problem. The only difference between the Query that "works" and the query that "does not work" is the fact that when you compare allformula to formatfor the Query works, and when you compare allformula1 to formatfor1 the Query does not work. This says to me that you need to check the values of allformula1 and formatfor1. Use the following query and compare the values. I suspect you will find that allformula and formatfor have values that are appropriate, while allformula1 and/or formatfor1 do not. Once you see the inappropriate values, you can take whatever action is required.

SELECT [FONT=Courier]imtemasterqry.IMTENUMBER, imtemasterqry.allformula, imtemasterqry.allformula1, calibcertiqry.formatfor, calibcertiqry.formatfor1[/FONT]
[FONT=Courier]FROM imtemasterqry LEFT JOIN calibcertiqry ON (imtemasterqry.IMTENUMBER = calibcertiqry.IMTENUMBER) [/FONT]

 

[vbaInet]

On a side note eddii, you're still making these redundant calculations:

       IIf([STATUSCER]="ok",1,0) AS OK_Count, 
       IIf([STATUSCER]="ng",1,0) AS ng_Count, 
       IIf([STATUSCER]="pending",1,0) AS pending_Count, 
       IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], 
       IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, 
       IIf(IsNull([statuscer]),1,0) AS blank_count, 
       IIf([STATUSCER]="scrap",1,0) AS scrap_Count,

I did show you how you can count those values in your report and I remember taking them out from the query.

 

[eddii]

Thanks for the reply: HELP : query error (Error 3071)

Thanks MSAccessRookie
Thanks vbaInet
of the reply

i was late in replying as i was out of office and could not reply

MSAccessRookie i will come back as soon as i get check you code

vbaInet iam sorry i have not seen that i will find out in my old attachments and take it out thanks for this help.

is this the new code which you gave me if yes

OK_Count: IIf([STATUSCER]="ok",1,0)
ng_Count: IIf([STATUSCER]="ng",1,0)
pending_Count: IIf([STATUSCER]="pending",1,0)
BREAK DOWN_Count: IIf([STATUSCER]="BREAK DOWN",1,0)
MISSING_Count: IIf([STATUSCER]="MISSING",1,0)
blank_count: IIf(IsNull([statuscer]),1,0)

i am not able to export it as i have raised a post linked

http://www.access-programmers.co.uk/forums/showthread.php?t=218996

i will get back to you tomorrow

thanks once again

 

[vbaInet]

Re: Thanks for the reply: HELP : query error (Error 3071)

vbaInet iam sorry i have not seen that i will find out in my old attachments and take it out thanks for this help.

is this the new code which you gave me if yes

OK_Count: IIf([STATUSCER]="ok",1,0)
ng_Count: IIf([STATUSCER]="ng",1,0)
pending_Count: IIf([STATUSCER]="pending",1,0)
BREAK DOWN_Count: IIf([STATUSCER]="BREAK DOWN",1,0)
MISSING_Count: IIf([STATUSCER]="MISSING",1,0)
blank_count: IIf(IsNull([statuscer]),1,0)

i am not able to export it as i have raised a post linked

http://www.access-programmers.co.uk/forums/showthread.php?t=218996

Nope!

Count(IIf([STATUSCER]="ok",1,Null))

in a textbox in the report then you delete OK_Count: IIf([STATUSCER]="ok",1,0) in your query. The above will give you a count.

Hmmm... looks like I haven't replied back to that thread. I will follow it up later.

 

[eddii]

Thanks vbaInet
the code is working but the total is not comming if i export to excel format
the reports

=Count(IIf([STATUSCER]="ok",1,Null))

File = export = save as excel

sorry to disturb

 

[vbaInet]

I've just updated your other thread so you may continue on that.

Here was what I said anyway:

Did you try DoCmd.OuptutTo with an acFortmatXLS format?

If that doesn't work you will need to replace those textboxes with a subreport that does the count.