Why add when you can concatenate?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following query, instead of adding the numbers 0, 0, and 1 to finish the
calculation in the last column, is instead concatenating them to come up with
the number 001. Aaarrrgggh!

SELECT FMS_HELOC_crosstab.EmpID, FormatNumber(Nz([Mortgage],0),0) AS
Mortgages, FormatNumber(Nz([Security],0),0) AS Securities,
FormatNumber(Nz([Trust],0),0) AS Trusts,
Nz([Mortgages]+[Securities]+[Trusts],0) AS FMS
FROM FMS_HELOC_crosstab
WHERE (((FMS_HELOC_crosstab.EmpID)=122));

It is pulling its data from a crosstab query, but that query is able to add
up the numbers in each category for each officer, so apparently the crosstab
query sees them as numbers and not text. Somehow, in the second query (SQL
above), the numbers are being converted to text. At least that's my guess.

Thanks in advance,
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor
 
the Format() function returns a text string, regardless of whether the value
"goes in" as Text or Number data type. try changing your query to

SELECT FMS_HELOC_crosstab.EmpID, FormatNumber(Nz([Mortgage],0),0) AS
Mortgages, FormatNumber(Nz([Security],0),0) AS Securities,
FormatNumber(Nz([Trust],0),0) AS Trusts,
Nz([Mortgage],0)+Nz([Security],0)+Nz([Trust],0) AS FMS
FROM FMS_HELOC_crosstab
WHERE (((FMS_HELOC_crosstab.EmpID)=122));

hth
 
Back
Top