CODEWITHSUNDEEP
sql-serversql-server-2008ms-access
SDR
SDR

Reputation: 391

Format function in Access

I have the following query in MS Access and needs to be converted to SQL Server. I am trying to understand what does the format function do here and what is the use of "0".

SELECT  Format([SumOfTotalPopulation],"0") AS Expr1 , SumOfTotalPopulation FROM
  qry_ASSET_STREAM_DS_POP_PROP ;

Can anyone help me understad this. The above code is present in MS Access 2003 and Im working on SQL Server 2008 R2.

Upvotes: 0

Views: 728

Answers (2)

alroc
alroc

Reputation: 28174

The Access Format() function behaves differently based on the data type passed into it. In this example, I'm assuming that your SumOfTotalPopulation field is a number, which means that the formatting will be done as described here - basically, it will be formatted as an integer - no decimal point, no thousands separator.

The good news for you is that if the field in SQL Server is already defined as an integer type, you won't have to do this formatting. Otherwise, you should be doing this formatting at the presentation layer (user interface, web page, report, etc.) and not in the query itself.

Access teaches a lot of bad habits. Rather than translate what you have in Access one-for-one, take this opportunity to update things to be done "the SQL Server way" wherever possible.

Upvotes: 1

Scotch
Scotch

Reputation: 3226

In your case, Format([SumOfTotalPopulation],"0") just removes the decimals from the number.

In SQL Server, you could use something like Str(sumOfTotalPopulation,12,0)

Or you could use Round()

Upvotes: 2

Related Questions