Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: I should know this

    I confess I should know this.
    How do I reference a VBA variable in in line SQL
    IE..
    Dim ed as Interger
    Ed = 2
    Docmd.RunSql "Select table.field where field = (my Ed variable) "

    And Vice vers if I have an Inline SQL that says select Whatever as ED
    how do I use the value of Ed in the VBA ?

    As stated I think I should probally know this. Maybe I do and donl't realize it. (it is that kind of day)

    Thanks.
    Darasen

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DoCmd.RunSQL "SELECT blah FROM blah WHERE (blah=" & MyVariableNameHere & ");"
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    If what Owen wrote doesn't work, try putting a single quote around your VBA variable:

    "SELECT blah FROM blah WHERE (blah = '" & MyVariableName & "')"

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Lint Diggy
    If what Owen wrote doesn't work, try putting a single quote around your VBA variable:

    "SELECT blah FROM blah WHERE (blah = '" & MyVariableName & "')"
    to expand on Lint Diggy's statement
    if the column you are trying to use in the SQL is a text column then it must be enclosed in either single or double quotes. Other data types can be left 'as is'

    If you use the double quote to enclose text/string datatypes then an alternative construct is :_

    Code:
    SELECT blah FROM blah WHERE (blah = " & chr$(34) & MyVariableName & chr$(34) & ");"
    To a certain extent its a matter of coding style. Personally I find the latter easier to read but both contstructs are valid. It used to annoy the heck out of me using double quotes " when access would try to be helpfull and bung some extra double quotes, just to make sure that each " didn't feel lonely.

  5. #5
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Thats Great. Now what aboout the other waty around?
    If I have
    DoCmd.RunSQL "Select Table.Field, Count (Table.Field) As Result From Table...."
    The Sql would create a Value for Result. How would I use that Value in my Vba ?
    Here is Why In my Access xp App. I have a Tab on a form for the user to inpiut a clients Employment Information. I want to see of that client already has a record on my employment table and if not create a record for the client.
    Hope that makes sense.
    Darasen

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Darasen
    Thats Great. Now what aboout the other waty around?
    If I have
    DoCmd.RunSQL "Select Table.Field, Count (Table.Field) As Result From Table...."
    The Sql would create a Value for Result. How would I use that Value in my Vba ?
    Here is Why In my Access xp App. I have a Tab on a form for the user to inpiut a clients Employment Information. I want to see of that client already has a record on my employment table and if not create a record for the client.
    Hope that makes sense.
    Um ... Problem. RunSQL requires ACTION queries (UPDATE, APPEND, DELETE, etc ...) You'll need to use a recordset to do this ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you use the docmd construct can't help you

    but i would normally do something similar to:-

    Code:
    dim rstemp as adob.recordset
    set rstemp= new adob.recordset
    with rstemp
      .open ("Select * from tblEmployee where EmpName=" & chr$(34) & "Darasen" & chr$(34) & " and EmpRole=1, currentproject.connection)
      if .eof=true and .bof=true ' then no record found
      'insert new record
    'eg
        .add
        .empName="Darasen"
        'blah blah blah
        .update
    'if employeeis was an autonumber field then after update the value will bve available
      else
      'insert anyon found process
      endif
    end with
    'note the PK autonumber field would be available using rstemp!employeeID
    this is "air" code, you need to fill out the .open paramters, it hasn't been tested etc....

  8. #8
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Quote Originally Posted by M Owen
    Um ... Problem. RunSQL requires ACTION queries (UPDATE, APPEND, DELETE, etc ...)
    Aha ! I did not know that, Thanks.
    I have not in all honesty worked with records sets much (avoided actually) But, now looks like a good time to plunge in.

    Thanks Guys.
    Darasen

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this is most simply done with DCOUNT()

    dim ED as long
    ED = DCOUNT("myField", "tblMine", "Name = '" & nameVar & "'")

    yes aggregates are "slow", but how much faster can a one-off lookup on a local table be to justify a page of code?

    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
  •