Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    function call in query vs VBA

    I am turning a bunch of spreadsheets into a useful database. My problem is turning text that represents "Yes" into a Yes/No field.
    I have written a function that returns true if the Variant I give it is something like Y, y, etc.
    I have a query that works just fine using this function.

    Now I want to convert it to SQL in code, and it doesn't work as expected. The code fragment is as follows:
    <pre> strSQL = "INSERT INTO tblCodeTypeTagList ( TrendPoint ) SELECT "
    strSQL = strSQL & YesToBool("[" & strtblCodeType & "].CiTectTrend") & " AS Expr1 FROM ["
    strSQL = strSQL & strtblCodeType & "];"
    myDB.Execute strSQL</pre>


    This works in the query, but in the code the result of the function is calculated only once. I can see that the function is using the table.field as a string and evaluating that, rather than the data stored in table.field.
    Any suggestions how to rewrite the function or otherwise achieve the end result?

    Thanks,
    Marty

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: function call in query vs VBA

    If this is supposed to pass a table name and field name into the function as a string, then it's doing exactly what you told it to.

    YesToBool("[" & strtblCodeType & "].CiTectTrend")

    Now, can you explain what you actually want it to do? If you want to include the function in the SQl, do it like this:

    strSQL = strSQL & " YesToBool([" & strtblCodeType & "].CiTectTrend) AS Expr1 FROM ["
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function call in query vs VBA

    Thanks Charlotte.

    I hadn't realised I could build the function into the SQL rather than call it as I made the SQL clause. Simple really.

    Works just luvverly.

Posting Permissions

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