preserve formatting in crosstab query

[zaskodnik]

Hi All,

I have a corsstab query which used 3 queries and adds them togethe
into 1.
The original queries have data in format "genreal number" an
"currency". (or zero.. if there is no data)
But when I add them like this: new:[QTY1]+[QTY2] in crosstab query, al
data just become strings.
I want to add some columns in the srosstab query and instead of addin
35 to 20 and getting 55, it writes string "3520". How do I fix it?

Thanks,
Petr

 

[zaskodnik]

I had a closer look at my problem and found out that before I added Nz()
function, the formatting was correct.

After I added Nz(), I lost my correct formating (general number, or
currency) and the data just became strings.

But I need my Nz() functions in order to fill blank fields with zeros.

How do I make sure that they stay in format "currency" or "general
number"?

Thank you.

 

[zaskodnik]

I had a closer look at my problem and found out that before I added Nz(
function, the formatting was correct.

After I added Nz(), I lost my correct formating (general number, o
currency) and the data just became strings.

But I need my Nz() functions in order to fill blank fields with zeros.

How do I make sure that they stay in format "currency" or "genera
number"?

Thank you

 

[Duane Hookom]

Apply the formatting in the text boxes/controls on your form or report.

 

[John Spencer (MVP)]

NZ in a query always returns a string (at least according to the documentation
and my limited testing) if you don't specify the value to be returned. The best
way to take care of that is to force the value back to a number. This is often
not noticeable, because Access often will change the data type back without you
noticing. For instance, if you multiply, divide, or subtract, Access will
say"OH! a math operation! Need to change this string to a number.") But the +
sign can mean ADD (arithmetic operation) or Concatenate (a string operation).
So, ...

Something like:

CCur(NZ(TheField))

or

Nz(TheField,0)

should both work. Try the alternatives and see if that works.

 

[zaskodnik]

Thanks for your help!

I used CCur(NZ(ExtPriceField,0) to convert Ext.Price to currency. Work
fine.
I have problems converting Quantity to integer. I trie
CInt(Nz(QTYfield,0)
It says "Error#" when I want to view query.

Any suggestions on how to convert string to regular integer?

Thanks.

BTW, what is "MVP"

 

[John Spencer (MVP)]

CInt(Nz([QTYfield],0)) should work, UNLESS your field is not null but is a
zero-length string or contains multiple spaces.

TRY

CInt(IIF(Len(Trim([QTYfield] & ""))=0,0,MyField))

If that works then try removing function one at a time and see if you can
determine what is needed in your particular case.


First drop the CInt
Then drop the Trim

 

[zaskodnik]

I tried CInt(Nz([fieldName,0)), but it puts "Error###" into som
fields... where integer should be.

I tried Cint((IIF(Len(Trim([QTYfield] & ""))=0,0,Myfield)), but it ask
for parameter and then puts the value in all fields.

So, I figured out that my numbers are too big to be INT and used long
CLNG and it worked!

Thanks