Format function in SQL

[Jean]

Hi,

I have a query that contains the following in the WHERE part of the
query:

WHERE....Format(IIf(nz([tblLaserscanmessungen_test.KoerperH])<>0,[tblLaserscanmessungen_test.KoerperH],
IIf(nz([tblRContourmessungen_test.KoerperH])<>0,[tblRContourmessungen_test.KoerperH],
IIf(nz([tblVideomessungen_test.KoerperH])<>0,[tblVideomessungen_test.KoerperH],
[tblVThronmessungen_test.KoerperH]))),"0.0") AS Körperhöhe,....

My problem is, that this changes the data type to a string, I think.
Why can't this value stay a decimal, as it is before the Format()
function is applied? Can I do something about it in the syntax of the
Format function?

In the query design window, I have also changed the Format property for
the Körperhöhe field properties to 0,0 - this works by making the
query output a number, but then later when using the query in a
CopyFromDataset funtion, it goes back to it's original behaviour as a
string.

I have read through the VBA help file on using the Format function, but
cannot get what I want - it keeps giving me a string back as the result
of the search!

Please help.

[Tom van Stiphout]

On 1 Feb 2005 03:31:29 -0800, "Jean" <je**********@hotmail.com> wrote:

The Format function returns a variant of subtype string. That's by
design, because it is supposed to be used to format final output. It
appears you perhaps want to round instead. So use a rounding function.

-Tom.

Hi,

I have a query that contains the following in the WHERE part of the
query:

WHERE....Format(IIf(nz([tblLaserscanmessungen_test.KoerperH])<>0,[tblLaserscanmessungen_test.KoerperH],
IIf(nz([tblRContourmessungen_test.KoerperH])<>0,[tblRContourmessungen_test.KoerperH],
IIf(nz([tblVideomessungen_test.KoerperH])<>0,[tblVideomessungen_test.KoerperH],
[tblVThronmessungen_test.KoerperH]))),"0.0") AS Körperhöhe,....

My problem is, that this changes the data type to a string, I think.
Why can't this value stay a decimal, as it is before the Format()
function is applied? Can I do something about it in the syntax of the
Format function?

In the query design window, I have also changed the Format property for
the Körperhöhe field properties to 0,0 - this works by making the
query output a number, but then later when using the query in a
CopyFromDataset funtion, it goes back to it's original behaviour as a
string.

I have read through the VBA help file on using the Format function, but
cannot get what I want - it keeps giving me a string back as the result
of the search!

Please help.

[Jean]

Hi Tom,

I tried your suggestion as follows:

WHERE.......Round(IIf(nz([tblLaserscanmessungen_test.KoerperH])<>0,[tblLaserscanmessungen_test.KoerperH],
IIf(nz([tblRContourmessungen_test.KoerperH])<>0,[tblRContourmessungen_test.KoerperH],
IIf(nz([tblVideomessungen_test.KoerperH])<>0,[tblVideomessungen_test.KoerperH],
[tblVThronmessungen_test.KoerperH]))),2) AS Körperhöhe, ............

But, this has no effect! The numbers for Körperhöhe still have about
8 digits after the decimal.
What am I doing wrong here?

Please help, I'm quite a newbie at this!

[Jean]

Just read a few articles....I should mention that I am using Access
2000, so there should be a built in Round function.

Also, I should say that my locale setting for numbers is 123.456,789,
meaning the decimal sign is a comma (,)