Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    475

    Pass variable to SQL

    Okay - I'm not very good at explaining my problems but I'll do the best I can.

    I have 20 small tables with one field containing approximately twenty office names.

    I need Access VBA code to decalre a variable, then something like, for i from 1 to endOfTable, look at tblName, assign the first office name to the variable then run the SQL code below using the variable.

    Then I will dump the WST and the WSTDV and the variable into a small table that I will place on the last page of a report and look for the next office in the tblName and do it again until all of the offices have been run.

    Whew! Hope I got that right.

    Please remember - I know nothing about VBA!


    SQL:

    SELECT Sum([SALESPRICE]) AS [WSTDV], Count(OCMonthlyData.SalesPrice) AS [WSTCount]
    FROM OCMonthlyData
    WHERE (((OCMonthlyData.OFFICELIST_OFFICENAME) Like "variable") AND ((OCMonthlyData.OFFICESELL_OFFICENAME) Not Like "variable"));

    Thanks Much . . .

    Rick ~ Access Newbie

  2. #2
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    I assume OFFICENAME is a string, therefore:

    sqlString = "SELECT BlabBlabBlab "
    sqlString = sqlString & "WHERE OFFICENAME LIKE '" & myVar & "*' AND OTHEROFFICENAME NOT LIKE '" & myVar & "*'"

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    475

    BlaBlaBla . . ?

    Carl . . . not sure what blablabla is. Fieldname or table name?
    sqlString = "SELECT BlabBlabBlab "

    Can you help me with another definition? SELECT ~ how does this work in Access 2003?

    sqlString = sqlString & "WHERE OFFICENAME LIKE '" & myVar & "*' AND OTHEROFFICENAME NOT LIKE '" & myVar & "*'"

    Does this section go into the Criteria row?

    Is it best to declare my Variable? Dim MyVariable AS string?

    How would I then assign MyVariable to the fields in another table?

    Thanks much . . .

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Let's put the code away for a minute. What are you trying to do here, in plain english?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,448
    Quote Originally Posted by Rick Schreiber
    ...I have 20 small tables with one field containing approximately twenty office names....
    20 small tables containing one field doesn't sound right, wonder if we should be pointing you to rudys site (r937.com/relational.htm)?

  6. #6
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    Sorry, that would be select fields from tablename

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    partageons-nous la même définition de « anglais simple »?

    izy
    currently using SS 2008R2

Posting Permissions

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