Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Talking Unanswered: Assigning value using SQL

    Does anyone have a quick way to assign a value to using an SQL statement in VBA??? Something like:

    DIM foo as string

    foo=docmd.runsql ("Select value from table where value = criteria;")

    Any help would be appreciated!!!

  2. #2
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    I dont completely understand what you are saying...assign a value? Do you mean you want to run the sql statement, and then find the first result? or the only result and then assign it to that variable?

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Private Sub assignfoo ()
    Dim conn as ADODB.Connection
    Set conn = CurrentProject.Connection

    Dim rec as ADODB.RecordSet
    rec = New ADODB.Recordset

    Dim SQLCreate As String
    SQLCreate = "SELECT value FROM table WHERE field = value"

    Dim foo As String

    With rec
    .Open SQLCreate, conn, adOpenStatic, adLockOptimistic
    If (.BOF) Then
    MsgBox "There were no records satisfying that criteria"
    Else
    foo = rec(0)
    End if
    End With

    End Sub

  4. #4
    Join Date
    Jan 2004
    Posts
    5

    Trying

    What I am trying to do is add(sum) time for a given day stored in several records with the same date, and display it on a form.

    Eg:
    12/12/03, 1.5
    12/12/03, 2.5
    12/12/03, 1

    So 12/12/03 would return 5

    This:
    Dim n as int
    n = DoCmd.RunSql ("SELECT Sum(ProblemTickets.TimeSpent) AS Expr1
    FROM ProblemTickets
    WHERE (((ProblemTickets.When)=#12/29/2003#);")

    will not work.

    Thanks!

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Trying

    Originally posted by bguymon
    What I am trying to do is add(sum) time for a given day stored in several records with the same date, and display it on a form.

    Eg:
    12/12/03, 1.5
    12/12/03, 2.5
    12/12/03, 1

    So 12/12/03 would return 5

    This:
    Dim n as int
    n = DoCmd.RunSql ("SELECT Sum(ProblemTickets.TimeSpent) AS Expr1
    FROM ProblemTickets
    WHERE (((ProblemTickets.When)=#12/29/2003#);")

    will not work.

    Thanks!
    Try something like that

       Dim ff As String
       Dim num As Long
        ff = "SELECT SUM(ProblemTickets.TimeSpent) AS [Sum of Problems] INTO urTemp FROM ProblemTickets WHERE (((ProblemTickets.When)=#12/29/2003#));"
    DoCmd.RunSQL (ff)


    Now the sum will be stored in the first Record in the TableurTemp

    Hope this will help

  6. #6
    Join Date
    Jan 2004
    Posts
    5

    Thank you!!! Works Great!

    Works Great! With a little modification!!!

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Notice that DoCmd.RunSQL will be used for action Quries only, such as deleting, creating, updating or inserting values.

    And that was the trick

Posting Permissions

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