Union query in A97

I am working with a union query in Access 97 (for a client). All
works well except some of the numeric columns come through as text. A
couple of them, that is fine. However, other I put in a zero when the
field does not have a value from that query. So I can't figure out
why I am getting that.

Any ideas?



SELECT Closeout_Labor.Rtgseq, Closeout_Labor.Setup, Oper as OperDesc,
Closeout_Labor.Eqty, Closeout_Labor.Scp, Closeout_Labor.[Total Cost],
Closeout_Labor.[Cost Per Pc], 0 AS Mat, Closeout_Labor.LbrCostGP,
Closeout_Labor.VarPC, Closeout_Labor.FixPC,
Closeout_Labor.MinPc,QuotedTime,Quoted$
FROM Closeout_Labor
WHERE [Department]<>'12'

UNION Select Rtgseq&".1",false, " " & materialID, Eqty,
0,Cost,MatGC,MatGC,0,0,0,0," ",Quoted
From Closeout_Mat

UNION Select RtgSeq, false,"Metal",GoodPieces,0,Cost,MatCost,MatCost,
0,0,0,0," ",QuotedMetal
From Closeout_Metal

Union Select RtgSeq, false,Desc,Qty,0,Cost,MatGP,MatGP,0,0,0,0," ",0
From Closeout_MeltLoss

Union Select RtgSeq,False,Desc,Qty,0,Cost,val([VarGC])+val([FixGC]),
0,0,VarGC,FixGC,0," ",Quote$
From Closeout_MeltCost

UNION Select RtgSeq, false,Operation ,GoodPieces,0,Cost,MatCost,
0,MatCost,0,0,0," ",0
From Closeout_SalesCost
ORDER BY RtgSeq, OperDesc
WITH OWNERACCESS OPTION;

Re: Union query in A97

"Dean" wrote in message
news:c574d67b-c467-4603-9ddd-6324faa938f1@s8g2000prg.googleg roups.com...
> I am working with a union query in Access 97 (for a client). All
> works well except some of the numeric columns come through as text. A
> couple of them, that is fine. However, other I put in a zero when the
> field does not have a value from that query. So I can't figure out
> why I am getting that.
>
> Any ideas?
>
>
>
> SELECT Closeout_Labor.Rtgseq, Closeout_Labor.Setup, Oper as OperDesc,
> Closeout_Labor.Eqty, Closeout_Labor.Scp, Closeout_Labor.[Total Cost],
> Closeout_Labor.[Cost Per Pc], 0 AS Mat, Closeout_Labor.LbrCostGP,
> Closeout_Labor.VarPC, Closeout_Labor.FixPC,
> Closeout_Labor.MinPc,QuotedTime,Quoted$
> FROM Closeout_Labor
> WHERE [Department]<>'12'
>
> UNION Select Rtgseq&".1",false, " " & materialID, Eqty,
> 0,Cost,MatGC,MatGC,0,0,0,0," ",Quoted
> From Closeout_Mat
>
> UNION Select RtgSeq, false,"Metal",GoodPieces,0,Cost,MatCost,MatCost,
> 0,0,0,0," ",QuotedMetal
> From Closeout_Metal
>
> Union Select RtgSeq, false,Desc,Qty,0,Cost,MatGP,MatGP,0,0,0,0," ",0
> From Closeout_MeltLoss
>
> Union Select RtgSeq,False,Desc,Qty,0,Cost,val([VarGC])+val([FixGC]),
> 0,0,VarGC,FixGC,0," ",Quote$
> From Closeout_MeltCost
>
> UNION Select RtgSeq, false,Operation ,GoodPieces,0,Cost,MatCost,
> 0,MatCost,0,0,0," ",0
> From Closeout_SalesCost
> ORDER BY RtgSeq, OperDesc
> WITH OWNERACCESS OPTION;

I think Union queries get the field type from the data in the 1st row. So if
the 1st row has any null fields, those field types become text. One thing to
try is in the 1st query replace the field name with something like the
following "nz([tblname].[Fieldname],cdbl(0)) as [Fieldname]", which will
force the field type to double.

Re: Union query in A97

On Dec 7, 8:11 am, "paii, Ron" wrote:
> "Dean" wrote in message
>
> news:c574d67b-c467-4603-9ddd-6324faa938f1@s8g2000prg.googleg roups.com...
>
>
>
>
>
> > I am working with a union query in Access 97 (for a client). All
> > works well except some of the numeric columns come through as text. A
> > couple of them, that is fine. However, other I put in a zero when the
> > field does not have a value from that query. So I can't figure out
> > why I am getting that.
>
> > Any ideas?
>
> > SELECT Closeout_Labor.Rtgseq, Closeout_Labor.Setup, Oper as OperDesc,
> > Closeout_Labor.Eqty, Closeout_Labor.Scp, Closeout_Labor.[Total Cost],
> > Closeout_Labor.[Cost Per Pc], 0 AS Mat, Closeout_Labor.LbrCostGP,
> > Closeout_Labor.VarPC, Closeout_Labor.FixPC,
> > Closeout_Labor.MinPc,QuotedTime,Quoted$
> > FROM Closeout_Labor
> > WHERE [Department]<>'12'
>
> > UNION Select Rtgseq&".1",false, " " & materialID, Eqty,
> > 0,Cost,MatGC,MatGC,0,0,0,0," ",Quoted
> > From Closeout_Mat
>
> > UNION Select RtgSeq, false,"Metal",GoodPieces,0,Cost,MatCost,MatCost,
> > 0,0,0,0," ",QuotedMetal
> > From Closeout_Metal
>
> > Union Select RtgSeq, false,Desc,Qty,0,Cost,MatGP,MatGP,0,0,0,0," ",0
> > From Closeout_MeltLoss
>
> > Union Select RtgSeq,False,Desc,Qty,0,Cost,val([VarGC])+val([FixGC]),
> > 0,0,VarGC,FixGC,0," ",Quote$
> > From Closeout_MeltCost
>
> > UNION Select RtgSeq, false,Operation ,GoodPieces,0,Cost,MatCost,
> > 0,MatCost,0,0,0," ",0
> > From Closeout_SalesCost
> > ORDER BY RtgSeq, OperDesc
> > WITH OWNERACCESS OPTION;
>
> I think Union queries get the field type from the data in the 1st row. So if
> the 1st row has any null fields, those field types become text. One thing to
> try is in the 1st query replace the field name with something like the
> following "nz([tblname].[Fieldname],cdbl(0)) as [Fieldname]", which will
> force the field type to double.- Hide quoted text -
>
> - Show quoted text -

You are sooo correct. Thanks for the help. What I failed to mention
here is I am using a customized rounding function:

Function RoundCC(x, DecimalPlaces) As Double
Dim Factor As Variant
x = Nz(x, 0)
Factor = 10 ^ DecimalPlaces
RoundCC = Val(Int(x * Factor + 0.5) / Factor)
End Function

I originally had Function RoundCC(x, DecimalPlaces) As Variant.




How I don't miss Access 97.