Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007

    Unanswered: access pass through query -- sql

    I have a query (NOT a pass through query) written in Access which is working fine!

    ChangeControlFormNum is an interger field
    RevisedCode is an "text" field.

    When I run the following query, a pop up box saying "Enter Change Control Form Number" will pop up. User can input e.g. 1400 or 1400a or 140* or *40* to search the database and set the "SearchPrintReport" field to True on all the records that satisfied the condition. (Well, it goes without saying that I do set the SearchPrintReport field on all the records to False to begin with.)

    UPDATE tblChangeControlFormDetails SET tblChangeControlFormDetails.SearchPrintReport = True
    WHERE ((([ChangeControlFormNum] & [RevisedCode]) Like [Enter Change Control Form Number:]));

    Now I want to change this query into a pass through query to speed up the search. I hard code %130 to condition and it does work.

    update tblChangeControlFormDetails
    set SearchPrintReport = 1
    where ChangeControlFormNum like '%130';

    When I add the "Revisdcode" criteria to it, it doesn't work. I got this error message "ODBC -- call failed. [Microsoft][ODBC SQL Server Driver][SQL Server] Conversion failed when converting the nvarchar value 'a' to data type int. (#245). By the way, I do have a record with ChangerControlFormNum == 1130 and RevisedCode = a.. Thanks

    update tblChangeControlFormDetails
    set SearchPrintReport = 1
    where (([ChangeControlFormNum] & [RevisedCode]) like '%130' );

    Of course, my goal is to have the pass through query give me a chance to input the search value. I guess I will ask that after I figure out my inter-mediate problem.

    Thank you for your help.

    Last edited by sweetmail; 04-16-09 at 15:28.

  2. #2
    Join Date
    May 2005
    Why not
    UPDATE tblChangeControlFormDetails
    SET SearchPrintReport = 1
    WHERE (([ChangeControlFormNum] LIKE '%130') AND ([RevisedCode] LIKE '%130'));
    Me.Geek = True

Posting Permissions

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