Using Cross Tab qry as Report Source Value seems 2b TXT!!

[Fotorat]

I have this transform:

TRANSFORM Nz(Sum(IIf([CODE]="L",[TOTAL],[TOTAL])),0) AS SumOfTOTAL
SELECT UNION.JOB
FROM [UNION]
WHERE (((UNION.JOB) Not Like "**Other - Non RetailPosition* *" And (UNION.JOB) Not Like "Dec*"))
GROUP BY UNION.JOB
ORDER BY [UNION].[Months2] & '-' & [CODE]
PIVOT [UNION].[Months2] & '-' & [CODE];


And I want to create a report from it, and it looks fine (as a report) but when I try to total the rows, on the report as =SUM([txt1]+[txt2]) and then run the report, I always get the msg box "The Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type".

So I went back to the cross tab query and on the properties of the value field set it to general number - still no good! and I know the underlying data is numeric as the cross tab can sum it up perfectly - any suggestions?

Kind regards

tony

[Fotorat]

Yes I have numbers up until the cross tab which converts the summed values into Text - so I need to know how to force the cross to retain the numerical formatting on its value field

[NeoPa]

You're talking of the Format property, which doesn't effect the underlying data.
In your report, try

=SUM(Val([txt1])+Val([txt2]))

Assuming the two fields both contain valid numeric characters.

[Fotorat]

Excellent that help me nail it, I used this in the detail section to sum each row:

=Val([1-Transfers])+([2-Transfers]) ect

I think you should be charging for this service- but as you don't I am helping out on some questions that I know!

[NeoPa]

Good for you Fotorat.
That's an excellent attitude.
BTW. PEB has warned, in other posts, that the Val() function does not repond to the systems's 'Regional Settings'. It only works with '.' as decimal point. You could try CDbl() or CLng() instead if this is an issue for you.

[Fotorat]

Now I feel a right idiot, I am still doing the same report and in the page footer I need column totals. =sum() wont work, nor does =val(), nor does any combination of =sum(cdbl()) or = sum(Clng()) any suggestions?

[NeoPa]

I don't see any more problems.
Maybe you're doing something wrong which, because you're not aware of it, you haven't posted in here.
This is where the hands on bit takes over I'm afraid.

If you can find any more related info we can look at it for you, but I can't tell you what to look for :(.
It's a bit like "Hands up all those who aren't here!"
Sorry.

[PEB]

Hi

Try Sum(val(text1)) +Sum(val(text2) )

Or simply for the test try once only Sum(Val(Text1))

If there isn't pb try the other one...

Now I feel a right idiot, I am still doing the same report and in the page footer I need column totals. =sum() wont work, nor does =val(), nor does any combination of =sum(cdbl()) or = sum(Clng()) any suggestions?