Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Unanswered: Change query's SQL from code.

    HI,

    I know how to change a query's SQL from code but is there any way of shortening it?

    Example:

    If I'm using this -

    strsql = SELECT tblreview.IntReviewCaseID, tblClassification.StrClassification, tblRegion.StrRegion, tblArea.StrArea, tblsortcode_branch.Branch, tblcase.BranchID, tblcase.CustAccountNo, tblreview.DateInput, tblreview.DateArchNonComp, tblreview.DateType, tblcase.DateSeen, tblsortcode_branch.IntRegion, tblcase.DistributionChannel
    FROM (tblRegion INNER JOIN (tblArea INNER JOIN tblsortcode_branch ON tblArea.IntArea = tblsortcode_branch.IntArea) ON tblRegion.IntRegion = tblsortcode_branch.IntRegion) INNER JOIN (tblClassification INNER JOIN (tblcase INNER JOIN tblreview ON tblcase.IntCase = tblreview.IntReviewCaseID) ON tblClassification.IntClassification = tblreview.IntClassification) ON tblsortcode_branch.BranchID = tblcase.BranchID
    WHERE (((tblcase.BranchID) Like [forms]![frmreport]![txtbranch]) AND ((tblreview.DateInput) Between [forms]![frmreport]![txtinputfrom] And [forms]![frmreport]![txtinputto]) AND ((tblcase.DateSeen) Between [forms]![frmreport]![txtseenfrom] And [forms]![frmreport]![txtseento]) AND ((tblsortcode_branch.IntRegion) Like [forms]![frmreport]![txtregion]) AND ((tblcase.DistributionChannel) Like [forms]![frmreport]![txtdistchannel]));

    is there any way to split it up like this:

    strselect = tblreview.IntReviewCaseID, tblClassification.StrClassification, tblRegion.StrRegion, tblArea.StrArea, tblsortcode_branch.Branch, tblcase.BranchID, tblcase.CustAccountNo, tblreview.DateInput, tblreview.DateArchNonComp, tblreview.DateType, tblcase.DateSeen, tblsortcode_branch.IntRegion, tblcase.DistributionChannel

    strfrom = (tblRegion INNER JOIN (tblArea INNER JOIN tblsortcode_branch ON tblArea.IntArea = tblsortcode_branch.IntArea) ON tblRegion.IntRegion = tblsortcode_branch.IntRegion) INNER JOIN (tblClassification INNER JOIN (tblcase INNER JOIN tblreview ON tblcase.IntCase = tblreview.IntReviewCaseID) ON tblClassification.IntClassification = tblreview.IntClassification) ON tblsortcode_branch.BranchID = tblcase.BranchID

    Strwhere = (((tblcase.BranchID) Like [forms]![frmreport]![txtbranch]) AND ((tblreview.DateInput) Between [forms]![frmreport]![txtinputfrom] And [forms]![frmreport]![txtinputto]) AND ((tblcase.DateSeen) Between [forms]![frmreport]![txtseenfrom] And [forms]![frmreport]![txtseento]) AND ((tblsortcode_branch.IntRegion) Like [forms]![frmreport]![txtregion]) AND ((tblcase.DistributionChannel) Like [forms]![frmreport]![txtdistchannel]));

    then have string of code that looks like this.

    select " & strselect & " from " & strfrom & " where " & strwhere & ";

    thanks everyone overing their input.

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Change query's SQL from code.

    Yes you can do that, just watch out for missing spaces and other minor errors.

    A technique that I use when building long strings is to put a breakpoint in my code on the select statement:

    strsql= "select " & strselect & " from " & strfrom & " where " & strwhere

    Then I go to the immediate window and type

    ? strsql and press enter

    This gives me the string that will be passed to the database, which I try directly on the database. This technique helps in avoiding syntax errors which can occur easily in long strings.
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    No problems doing it this way - more readable format.

    some small typos - (probably just in a rush to post)

    missing quotes
    >strselect = tblreview.IntReviewCaseID, ...

    Extra semicolon
    >strfrom & " where " & strwhere & ";

    output:
    strSQL = "select " & strselect & " from " & strfrom & " where " & strwhere

  4. #4
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    Thanks that did the trick although for some reason I couldn't get it to work before.

Posting Permissions

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