Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    10

    Unanswered: CAST in Jet SQL?

    I'm trying to convert a string into a decimal, it has to be inside a SQL command;

    CAST('2.5' as decimal(5,2)), as an example, but with higher precision decimal(19,2)

    So, I understand CAST/CONVERT are not supported in Jet 4.0 or ACE 12.0
    I Can use CDbl for doubles, but what do I use for decimals with higher precision?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Type Conversion Functions - Access - Office.com would suggest using cdec, failing that consider using the val function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    10
    CDec is broken in Jet

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cDec seems to work fine in V2010
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2012
    Posts
    10
    It says its not even supported:
    Error message when you use the CDec() function in an Access query: "The expression you entered has a function containing the wrong number of arguments"
    ^...
    that fix does not seem to work, I think because I'm doing everything from Excel
    I'm using the 2007 version, btw

  6. #6
    Join Date
    Dec 2012
    Posts
    10
    On a slightly different note, even if it worked it would cause other problems:
    I just tested with CDbl: because I have European regional settings, CDbl requires the number in the string to have comma as decimal separator (even though Jet always uses "."), I suspect it is true for CDec as well

    If CAST was available I could have used "CAST('2.5' as decimal(5,2)"
    If CDec was available I should use CDec('2,5')

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cDec is available in VBA
    if you are using European number format then consider REPLACEing the comma with a decimal point

    cDEC isn't available in queries, you may have to write a function which wraps the cDec and does the conversion. it does however beg the question why you are storing numbers as string/text. if you convert your existing data to a suitable numeric data type and don't allow users to add to the string tet column you should be OK
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2012
    Posts
    10
    I'm using a remote function call to retrieve a table from SAP which sends everything as text, the data is returned with some information about the fields, so I am able to recreate the table and parse it into a .mdb-file

    Anyway, I decided to insert all fields as text, and then ALTER COLUMN at the end

    Btw, REPLACE is also not available in access queries, the alternative is to use MID & INSTR, but from this point I feel it has escalated to ridiculous

    Thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •