comma, list separator and VBA

I'm building an MS-Access 97 application where queries are created on the fly. One of the queries has an iif expression.

Something like: iif([books].[price]<5;2;7,5)

important to note: - ; is the list separator I use on my system - , is the decimal separator I use on my system. - the 7,5 (which is 7.5 is UK/US notation) is a VBA calculated field (result of a function) - it says in the VBA documentation (and this is so, because I tested it) that the list separator in VBA should be a comma

resulting that the above expression would be: iif([table].[field1]<5,2,7,5) which of course gives an error message because there are too many parameters for the IIF function

set db = currentdb() rsSQL = "select title, author, price, iif([books].[price],2," & getresult() & ") as reduction from table" set rs = db.openrecordset(rsSQL) 'this is where the error appears, because there are too many comma's in the IIF expression end sub

Since this is a application that I need to distribute on an international level, there is no way of knowing what each user has as list separator and/or decimal separator in their regional settings.

Does anyone of you know how to handle this?

Thanks in advance,

Jacques

Mon, 16 May 2005 03:33:42 GMT

Patrick Mollo#2 / 4

comma, list separator and VBA

getresult" sends back 7,5 and you're building a text string so there are two possibilities you can try (1) in getresult change this getResult = result to this getResult = format(result,"0.0")

which forces a decimal point sepator

or (2) on MakeQuery change this part of the sql text

iif([books].[price],2," & getresult() & ")

to

iif([books].[price];2;" & getresult() & ")

ie replace the two commas with semi-colons so that the result looks like your access query

I'm building an MS-Access 97 application where queries are created on the fly. One of the queries has an iif expression.

Something like: iif([books].[price]<5;2;7,5)

important to note: - ; is the list separator I use on my system - , is the decimal separator I use on my system. - the 7,5 (which is 7.5 is UK/US notation) is a VBA calculated field (result of a function) - it says in the VBA documentation (and this is so, because I tested it) that the list separator in VBA should be a comma

resulting that the above expression would be: iif([table].[field1]<5,2,7,5) which of course gives an error message because there are too many parameters for the IIF function

set db = currentdb() rsSQL = "select title, author, price, iif([books].[price],2," & getresult() & ") as reduction from table" set rs = db.openrecordset(rsSQL) 'this is where the error appears, because there are too many comma's in the IIF expression end sub

Since this is a application that I need to distribute on an international level, there is no way of knowing what each user has as list separator and/or decimal separator in their regional settings.

Does anyone of you know how to handle this?

Thanks in advance,

Jacques

Mon, 16 May 2005 03:44:47 GMT

Patrick Mollo#3 / 4

comma, list separator and VBA

Jacques asked how one could determine what regional settings the user has on their PC. There is VBA code that can get this using API calls. However, by using the Format method ( example 2 below) you enforce the required settings in the text string, without having to worry about the PC's settings.

I'm building an MS-Access 97 application where queries are created on the fly. One of the queries has an iif expression.

Something like: iif([books].[price]<5;2;7,5)

important to note: - ; is the list separator I use on my system - , is the decimal separator I use on my system. - the 7,5 (which is 7.5 is UK/US notation) is a VBA calculated field (result of a function) - it says in the VBA documentation (and this is so, because I tested it) that the list separator in VBA should be a comma

resulting that the above expression would be: iif([table].[field1]<5,2,7,5) which of course gives an error message because there are too many parameters for the IIF function

set db = currentdb() rsSQL = "select title, author, price, iif([books].[price],2," & getresult() & ") as reduction from table" set rs = db.openrecordset(rsSQL) 'this is where the error appears, because there are too many comma's in the IIF expression end sub

Since this is a application that I need to distribute on an international level, there is no way of knowing what each user has as list separator and/or decimal separator in their regional settings.

Does anyone of you know how to handle this?

Thanks in advance,

Jacques

Mon, 16 May 2005 15:35:18 GMT

Jacques Schoof#4 / 4

comma, list separator and VBA

just wondering:

what happens if on a pc where this application is supposed to run the following regional settings apply?:

Jacques asked how one could determine what regional settings the user has on their PC. There is VBA code that can get this using API calls. However, by using the Format method ( example 2 below) you enforce the required settings in the text string, without having to worry about the PC's settings.

I'm building an MS-Access 97 application where queries are created on the fly. One of the queries has an iif expression.

Something like: iif([books].[price]<5;2;7,5)

important to note: - ; is the list separator I use on my system - , is the decimal separator I use on my system. - the 7,5 (which is 7.5 is UK/US notation) is a VBA calculated field (result of a function) - it says in the VBA documentation (and this is so, because I tested it) that the list separator in VBA should be a comma

resulting that the above expression would be: iif([table].[field1]<5,2,7,5) which of course gives an error message because there are too many parameters for the IIF function

set db = currentdb() rsSQL = "select title, author, price, iif([books].[price],2," & getresult() & ") as reduction from table" set rs = db.openrecordset(rsSQL) 'this is where the error appears, because there are too many comma's in the IIF expression end sub

Since this is a application that I need to distribute on an international level, there is no way of knowing what each user has as list separator and/or decimal separator in their regional settings.