Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Store SQL result in a variable

    I am trying to create a variable that would store the results of the following SQL code. Depending on the result means that certain objects are shown or not on a form.

    So example: if result = “Yes” Then Textbox.Visible True
    Else Textbox.Visible False

    And so on ……….

    The form is using data from Batch_Service and not Nominal table.

    The Sql Code is
    “SELECT Nominal.Fixed_Amount
    FROM Batch_Service INNER JOIN Nominal ON Batch_Service.Nom_code = Nominal.Nom_Code;”


    This query may be used on other forms so it would be nice to hold it as a function that can be called another time.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How many records are we looking at?
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    There would be only one record in one field [Nominal].[Fixed_Amount] the result would be either "Yes" or "No"

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Only ever one value, ever, ever, ever?
    In a module you can globally declare your variable to make it available database wide:
    Code:
    Public myVariable As Boolean
    I suggest you use boolean values, even if your values sound like they're as text... You can translate the text value using an If statement as below
    Code:
    Dim x As String
    ...
    x = rs.Fields("Fixed_Amount")
    
    If x = "Yes" Then
      myVariable = True
    ElseIf x = "No" Then
      myVariable = False
    Else
      myVariable = NULL
    End If
    Now on with the recordset...
    Code:
    Dim rs As Object
    Dim conn As String
    Dim sql As String
    Dim result As String
    
    conn = <connection string>
    sql = <sql statement>
    Set rs= CreateObject("ADODB.Recordset")
    
    rs.Open sql, conn
    
    rs.MoveFirst
    
    x = rs.Fields(<field name or index>)
    
    rs.Close
    Set rs = Nothing
    Please note that there is no error handling in the above and it is written completely from memory, so it probably won't work

    Hope this helps!
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Cheers George,

    I shall try that out and let you know how good your memory is"!

    And, definately NEVER EVER more than one record (I hope)
    Thanks again

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, the .MoveFirst goes to the "first" record, so you might want to use an order by clause just to be sure.

    Anyhow, the above code doesn't cycle through records so it will only ever show the one

    Good luck, let me know how you get on!
    George
    Home | Blog

Posting Permissions

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