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)

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)

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)