comma, list separator and VBA 
Author Message
 comma, list separator and VBA

Hi,

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

In code it would be something like this:

function getResult()
    dim result
    result = 15 / 2        'of course my function is more complex ! ;o)))
    getResult = result
end function

sub makeQuery()
    dim db as database
    dim rs as Recordset
    dim rsSQL as string

    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  
 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

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------

  Hi,

  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

  In code it would be something like this:

  function getResult()
      dim result
      result = 15 / 2        'of course my function is more complex ! ;o)))
      getResult = result
  end function

  sub makeQuery()
      dim db as database
      dim rs as Recordset
      dim rsSQL as string

      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  
 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.

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------

  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

  --
  Patrick Molloy
  Microsoft Excel MVP
  ----------------------------------

    Hi,

    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

    In code it would be something like this:

    function getResult()
        dim result
        result = 15 / 2        'of course my function is more complex ! ;o)))
        getResult = result
    end function

    sub makeQuery()
        dim db as database
        dim rs as Recordset
        dim rsSQL as string

        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  
 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?:

    - decimal separator: . ( a dot)
    - list separator: , (a comma)

Would your version 2 " iif([books].[price];2;" & getresult() & ")  " still work?

Jacques.


  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.

  --
  Patrick Molloy
  Microsoft Excel MVP
  ----------------------------------

    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

    --
    Patrick Molloy
    Microsoft Excel MVP
    ----------------------------------

      Hi,

      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

      In code it would be something like this:

      function getResult()
          dim result
          result = 15 / 2        'of course my function is more complex ! ;o)))
          getResult = result
      end function

      sub makeQuery()
          dim db as database
          dim rs as Recordset
          dim rsSQL as string

          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



Tue, 17 May 2005 07:47:15 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. comma, list separator and VBA

2. A97 List Box, Comma Separators

3. comma separator option randomly disabled

4. Problem entering array formula when decimal separator is the comma

5. Excel bug with comma decimal separator

6. VBA- Format number without comma separator

7. CSV: Comma not recognized as separator when having non English (US) defined as regional setting

8. remove commas in 1000 separator

9. Disable "Allow Comma as Address Separator"

10. List separator for data validation list


 
Powered by phpBB® Forum Software © phpBB Group