Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: cast as money problem

    Hi,
    I am trying to select some dummy currency values in my query which are later populated in code.

    so I have for example

    SELECT CAST(0.00 as MONEY) AS SPEND1

    However I get a 'syntax error (missing Operator) in query.

    Am i using the cast expression correctly?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you are

    but in the wrong database

    CAST is SQL Server

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    ahhh, bugger. how can i do it in access then?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i can't help you, i don't know what it means to "select some dummy currency values in my query which are later populated in code"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    15
    i am running a query in vba using ado recordsets. I want to show some accounts and some spend by month, and then export the recordset to excel. The spends are calculated in code. and then I use

    rs.fields("Spend1").value = currSpend.

    So all i want is a field in my recordset of the correct datatype that I can then populate.

    if in the SQL i do "SELECT ACC, 0 as Spend1, 0 as Spend2 etc etc" it makes the data type an integer, so i want to tell it that the zero is a currency.

    I cannot calculate the spends in SQL.

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    SELECT CCur(0) as Spend1
    ghozy.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    beauty

    thanks ghozy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    no problem
    ghozy.

  9. #9
    Join Date
    Apr 2004
    Posts
    15
    thanks guys that helps, but it causes another problem.

    If i do

    SELECT targetSpend as Spend1, targetSpend as Spend2 etc etc
    (where targetSpend is a random currency field)
    i can do

    rs.fields("Spend1").value = 25.99

    however if i do

    SELECT CCur(0) as Spend1, CCur(0) as Spend2 etc etc

    when i try to change the value in code as above, i get
    'Multi step operation generated errors. Check Each status value'
    as an error message!

    any ideas?

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Simply Spend1 is a calculated field in its basic form. you can't edit/change calculated fields.
    ghozy.

  11. #11
    Join Date
    Apr 2004
    Posts
    15
    well thats a bit of a bind! thanks for your help though guys.

  12. #12
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    You might try to create Spend fields in table and leave them empty or use a unbound text control. Thats all advice I can give.
    ghozy.

Posting Permissions

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