Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using Union All
Hi, My name is Peres and...
I have a pivot table that uses another xls as database Into this xls I have 2 tables of data with equal fields. The fields Custo and Qdade are numerics fields but may have no data into it. 1-When I use CDbl() function to get the decimal numbers, it cause error when the lines are blank 2- when I try to use AS to obtain a proper name of the field, it disappear form the statements. How can I write this to work well? thanks! This is what I did But remember: it works if there are number in Custo and Qdade and "AS" looks as if not existed: SELECT tabela1.Até, tabela1.Atividade, tabela1.Categoria, CDbl(tabela1.Custo) as custo, tabela1.De, tabela1.`Detalhe da Atividade`, tabela1.Filial, tabela1.Grupo, tabela1.Mês, tabela1.Observações, cdbl(tabela1.Qdade) as Quantidade, tabela1.Regional, tabela1.Semana, tabela1.`Tipo de Gasto` FROM `C:\Base_Book`.tabela1 tabela1 WHERE (tabela1.Atividade Is Not Null) Union all SELECT tabela2.Até, tabela2.Atividade, tabela2.Categoria,cdbl(tabela2.Custo) as Custo, tabela2.De, tabela2.`Detalhe da Atividade`, tabela2.Filial, tabela2.Grupo, tabela2.Mês, tabela2.Observações,cdbl(tabela2.Qdade) as Quantidade, tabela2.Regional, tabela2.Semana, tabela2.`Tipo de Gasto` FROM `C:\Base_Book`.tabela2 tabela2 WHERE (tabela2.Atividade Is Not Null) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using Union All
you are dealing with NULL issues
goto Allen's site http://allenbrowne.com/tips.html#apps tips 11 & 12 deal with NULL "Peres-br-sp" wrote: Hi, My name is Peres and... I have a pivot table that uses another xls as database Into this xls I have 2 tables of data with equal fields. The fields Custo and Qdade are numerics fields but may have no data into it. 1-When I use CDbl() function to get the decimal numbers, it cause error when the lines are blank 2- when I try to use AS to obtain a proper name of the field, it disappear form the statements. How can I write this to work well? thanks! This is what I did But remember: it works if there are number in Custo and Qdade and "AS" looks as if not existed: SELECT tabela1.Até, tabela1.Atividade, tabela1.Categoria, CDbl(tabela1.Custo) as custo, tabela1.De, tabela1.`Detalhe da Atividade`, tabela1.Filial, tabela1.Grupo, tabela1.Mês, tabela1.Observações, cdbl(tabela1.Qdade) as Quantidade, tabela1.Regional, tabela1.Semana, tabela1.`Tipo de Gasto` FROM `C:\Base_Book`.tabela1 tabela1 WHERE (tabela1.Atividade Is Not Null) Union all SELECT tabela2.Até, tabela2.Atividade, tabela2.Categoria,cdbl(tabela2.Custo) as Custo, tabela2.De, tabela2.`Detalhe da Atividade`, tabela2.Filial, tabela2.Grupo, tabela2.Mês, tabela2.Observações,cdbl(tabela2.Qdade) as Quantidade, tabela2.Regional, tabela2.Semana, tabela2.`Tipo de Gasto` FROM `C:\Base_Book`.tabela2 tabela2 WHERE (tabela2.Atividade Is Not Null) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using Union All
Allesn's site is really a very good place for tips and there are some for
null using access But remember : Im using excel. Does the NZ() function work in the sql in excel? May be Im not turning on the proper library.help please "Vacation's Over" escreveu: you are dealing with NULL issues goto Allen's site http://allenbrowne.com/tips.html#apps tips 11 & 12 deal with NULL "Peres-br-sp" wrote: Hi, My name is Peres and... I have a pivot table that uses another xls as database Into this xls I have 2 tables of data with equal fields. The fields Custo and Qdade are numerics fields but may have no data into it. 1-When I use CDbl() function to get the decimal numbers, it cause error when the lines are blank 2- when I try to use AS to obtain a proper name of the field, it disappear form the statements. How can I write this to work well? thanks! This is what I did But remember: it works if there are number in Custo and Qdade and "AS" looks as if not existed: SELECT tabela1.Até, tabela1.Atividade, tabela1.Categoria, CDbl(tabela1.Custo) as custo, tabela1.De, tabela1.`Detalhe da Atividade`, tabela1.Filial, tabela1.Grupo, tabela1.Mês, tabela1.Observações, cdbl(tabela1.Qdade) as Quantidade, tabela1.Regional, tabela1.Semana, tabela1.`Tipo de Gasto` FROM `C:\Base_Book`.tabela1 tabela1 WHERE (tabela1.Atividade Is Not Null) Union all SELECT tabela2.Até, tabela2.Atividade, tabela2.Categoria,cdbl(tabela2.Custo) as Custo, tabela2.De, tabela2.`Detalhe da Atividade`, tabela2.Filial, tabela2.Grupo, tabela2.Mês, tabela2.Observações,cdbl(tabela2.Qdade) as Quantidade, tabela2.Regional, tabela2.Semana, tabela2.`Tipo de Gasto` FROM `C:\Base_Book`.tabela2 tabela2 WHERE (tabela2.Atividade Is Not Null) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union range | Excel Discussion (Misc queries) | |||
UNION of Arrays - is possible? | Excel Discussion (Misc queries) | |||
Help w/ Union Queries | Excel Discussion (Misc queries) | |||
Union only works for sometimes. | Excel Programming | |||
VBA union of two ranges | Excel Programming |