Solved Format Number Field in Select Statement (1 Viewer)

[Drand]

Hi

Could someone please show me the correct syntax to format the field [CountryCode] in the following select statement to zero decimal places?

SELECT [qryCountriesWithMissingData].[Member Firm], [qryCountriesWithMissingData].[CountryCode], [qryCountriesWithMissingData].[No of Records] FROM qryCountriesWithMissingData ORDER BY [Member Firm];

I am using this in a list box that is currently displaying 2 decimal places which I do not want.

Much appreciated and thanks

 

[plog]

Int() will give you just the integer portion of a number:

MS Access: Int Function

This MSAccess tutorial explains how to use the Access Int function with syntax and examples. The Microsoft Access Int function returns the integer portion of a number.

www.techonthenet.com

If there are leading zeros though, you probably want to find the position of the decimal with Instr()

MS Access: InStr Function

This MSAccess tutorial explains how to use the Access Instr function with syntax and examples. The Microsoft Access Instr function returns the position of the first occurrence of a string in another string.

www.techonthenet.com

And Mid() to get everything before it:

MS Access: Mid Function

This MSAccess tutorial explains how to use the Access Mid function with syntax and examples. The Microsoft Access Mid function extracts a substring from a string (starting at any position).

www.techonthenet.com

 

[Drand]

Thanks for your response. I am not sure I understand what you are saying though.

What I am trying to do is something like Format (CountryCode, "#,##0") but I am trouble with the syntax within the select statement

 

[arnelgp]

Format([CountryCode], "#")

 

[Drand]

Thanks arenlgp. Where would I place this in the select statement?

I tried

SELECT [qryCountriesWithMissingData].[Member Firm], Format( [qryCountriesWithMissingData].[CountryCode],"#"), [qryCountriesWithMissingData].[No of Records] FROM qryCountriesWithMissingData ORDER BY [Member Firm];

This formats the number correctly but now the column head is showing expr1001.

 

[arnelgp]

try:

Format( [qryCountriesWithMissingData].[CountryCode],"#") As [Country Code]

 

[Drand]

Perfect!

Thank you very much, greatly appreciated.

 

[JonXL]

Seems like the real issue is you have something that isn't a number stored in a numeric data type.