Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Unanswered: How do I include query fields in VB

    On the close property of a report I want to write some code that would update a field in a table to 'PAID' where the same field in the same table is 'OS' and where 2 fields in an existing query (not the same query used to create the report) are equal.

    My problem is reference the query. Can I do that in VB?

    example:

    update table1 set Status = 'PAID' where [table1].[status] = 'OS' and [query1].[totrecamt] = [query1].[ipprem]

    I tried the above, but I am being prompted to enter amounts for totrecamt and ipprem.

    Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    The problem in your SQL string is:

    update table1

    If you are using a column/field from a query (or a table for that matter) in the criteria it needs to be referenced in the section after UPDATE and before SET.

    eg you need something like

    UPDATE table1 INNER JOIN query1 ON table1.ID = query1ID

    The ID fields are the ones that link your saved query with your table. The rest of the SQL after this that you suggest should be OK.

    Hope this is of help.

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    thanks will give it a try

Posting Permissions

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