|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 18th, 2008, 07:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Union Query
I am trying to combine the results of several different select queries. Two
of the select queries are listed below. The problem seems to be with the [DEPR YEARS] field. I get an error saying "data type mismatch in criteria expression" for the union query, but not the select queries. SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CAPACITORBANK GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'), ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30, Year([ARCFM_CAPACITORBANK].[INSTALLDATE]) UNION ALL SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CONDUIT GROUP BY ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30, Year([ARCFM_CONDUIT].[INSTALLDATE]) HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2")); 
|
|
#2
July 18th, 2008, 09:34 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Union Query
All of the depr years in the tables feeding the queries need to be the same
data type, probably date. "broncojim" wrote: I am trying to combine the results of several different select queries. Two of the select queries are listed below. The problem seems to be with the [DEPR YEARS] field. I get an error saying "data type mismatch in criteria expression" for the union query, but not the select queries. SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CAPACITORBANK GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'), ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30, Year([ARCFM_CAPACITORBANK].[INSTALLDATE]) UNION ALL SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CONDUIT GROUP BY ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30, Year([ARCFM_CONDUIT].[INSTALLDATE]) HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
|
|
#3
July 18th, 2008, 09:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Union Query
This could be the problem, but I'm not sure. The depr years are fields that
are created inside the selection queries. They do not exist in the main tables. However, since this field is doing the same thing and has the same wording in each selection query, I simply copied and pasted the field from the first selection query to the others. Any ideas? "Golfinray" wrote: All of the depr years in the tables feeding the queries need to be the same data type, probably date. "broncojim" wrote: I am trying to combine the results of several different select queries. Two of the select queries are listed below. The problem seems to be with the [DEPR YEARS] field. I get an error saying "data type mismatch in criteria expression" for the union query, but not the select queries. SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CAPACITORBANK GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'), ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30, Year([ARCFM_CAPACITORBANK].[INSTALLDATE]) UNION ALL SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CONDUIT GROUP BY ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30, Year([ARCFM_CONDUIT].[INSTALLDATE]) HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
|
|
#4
July 18th, 2008, 10:11 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Union Query
You should not use an aliase ( [DEPR YEARS] ) within the same query that
creates it as the query may try to use it before it is created. -- KARL DEWEY Build a little - Test a little "broncojim" wrote: This could be the problem, but I'm not sure. The depr years are fields that are created inside the selection queries. They do not exist in the main tables. However, since this field is doing the same thing and has the same wording in each selection query, I simply copied and pasted the field from the first selection query to the others. Any ideas? "Golfinray" wrote: All of the depr years in the tables feeding the queries need to be the same data type, probably date. "broncojim" wrote: I am trying to combine the results of several different select queries. Two of the select queries are listed below. The problem seems to be with the [DEPR YEARS] field. I get an error saying "data type mismatch in criteria expression" for the union query, but not the select queries. SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CAPACITORBANK GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE, Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'), ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30, Year([ARCFM_CAPACITORBANK].[INSTALLDATE]) UNION ALL SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year], ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST], IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS]=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS], [DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE] FROM ARCFM_CONDUIT GROUP BY ARCFM_CONDUIT.SUBTYPECODE, Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30, Year([ARCFM_CONDUIT].[INSTALLDATE]) HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
