Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: sql pass thru query

    I want to rewrite this access code into a pass-thru SQL query. But it complain that nz is not an SQL function.

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET SearchPrintReport = True where ((len(nz(Approvedate))=0) and (len(nz(denieddate))=0)and (len(nz(ChangeControlFormNum)) <> 0 )) ")

    My pass-thru SQL query will look something like this

    UPDATE tblChangeControlFormDetails SET SearchPrintReport = 0 where ((len(nz(Approvedate))=0) and (len(nz(denieddate))=0)and (len(nz(ChangeControlFormNum)) <> 0 ))

    Any help is greatly appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pass-through SQL queries are directly executed on the ODBC-databse you are connected to, not in Access itself. So the queries have to be written in the legal SQL syntax for that ODBC-database. You have not defined the ODBC-database you are using, I guess it will be SQL Server.

    So you have to rewrite your Access-SQL to SQL Sever SQL. (But first say "Thank You Microsoft for having introduced an incompatible SQL dialect that only works on Access!")
    Code:
    UPDATE tblChangeControlFormDetails 
    SET SearchPrintReport = 0 
    where (
           len(COALESCE(Approvedate, '')) = 0 and
           len(COALESCE(denieddate, '')) = 0 and
           len(COALESCE(ChangeControlFormNum, '')) <> 0 
          )
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    I will try it this morning. thank you much.

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Wim,

    We do connect to the SQL server thru ODBC connection.
    I know this sounds crazy. the pass-thru query does not work actually. I have no explaination why I thought it works before.

    Just some more information
    Approvedate and denieddate are "date" type
    ChangeControlFormNum is a "number" type.

    thanks for helping in advance.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The function LEN() only works on strings (char(), varchar(), ...), not on dates or numbers, unless you would first convert them to a string.

    Try this:
    Code:
    UPDATE tblChangeControlFormDetails 
    SET SearchPrintReport = 0 
    where (
           Approvedate IS NULL and
           denieddate IS NULL and
           ChangeControlFormNum IS NOT NULL 
          )
    With IS NOT NULL and IS NULL you can check if a column has got a value - any value - (IS NOT NULL) or has no value at all (IS NULL).

    This is quite basic stuff. If this is new to you, you should read a good book about databases. It's not complicated, but the transition from Access SQL to the SQL of a real database, like SQL Server, can be a bit of a culture shock.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2007
    Posts
    148
    thank you much Wim.

    Thanks

Posting Permissions

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