Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql and variables (VBA Acess 2000)

    Hello, is it possible to assign sql results to a variable and if so how would I do this, this my code:

    Function GetFactor() As Double

    Dim disRate As double
    Dim polstart As Date
    Dim premend As Date
    Dim datAdd As Date
    Dim datDiff As Integer
    Dim cp As Integer

    polstart = Me.Start
    premend = Me.LastPrem
    cp = Me.icp

    datAdd = DateAdd("m", [cp], [polstart])
    datDiff = datediff("m", [premend], DateAdd("m", [icp], [polstart]))

    If Me.cmbFactor = "Monthly 0.75" Then
    DoCmd.RunSQL "SELECT tlbClawbackFactors.ID, tlbClawbackFactors.[Factor M075] FROM tlbClawbackFactors WHERE (((tlbClawbackFactors.ID)=datDiff));", -1

    End Function

    I want to assign the result of the sql statement to the variable disrate.

    Thanks Darren

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql and variables (VBA Acess 2000)

    It won't work this way. RunSQL is meant to execute an action query, i.e. a make-table, append or update query, not a select query. The way to retrieve results from a select query is by opening a recordset on it.

    But what do you want to accomplish? If you would open a query based on this SQL, it would display two columns: ID and Factor M075, because the SQL statement begins with "SELECT tlbClawbackFactors.ID, tlbClawbackFactors.[Factor M075]". You cannot assign two values to one variable, so you'll have to explain what you want.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql and variables (VBA Acess 2000)

    Just realised I had put two fields in the select statement. Want to put one value from a select query into the variable, the factor 075 field. How would i do this with a recordset?

    Thanks Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql and variables (VBA Acess 2000)

    Try this; it uses DLookup instead of a recordset:

    DoCmd.RunSQL "SELECT tlbClawbackFactors.ID, tlbClawbackFactors.[Factor M075] FROM tlbClawbackFactors WHERE (((tlbClawbackFactors.ID)=datDiff));", -1

    disRate = DLookup("Factor M075", "tblClawbackFactors", "ID=" & datDiff)

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql and variables (VBA Acess 2000)

    Thanks Hans.

Posting Permissions

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