Saving the result of a query in a variable

[Flo100]

I have a query whose result is a single value like sum of cost's column. Can I save the result in a string or a double variable. I tried using recordset. The code I used is as follows:

Sub CreateRecordset2()
    
    Dim rst As New ADODB.Recordset
    Dim str As String
    Dim str1 As String
    Dim intgr As Double
    
    str1 = "'*' & 'drilling' & '*'"
    
rst.Open "SELECT Sum([QUESTOR Run tbl].[cost]) FROM [Project info Tbl] INNER JOIN [QUESTOR Run tbl] ON [Project info Tbl].[Project ID] = [QUESTOR Run tbl].[Project ID] WHERE ((([Project info Tbl].[Offshore only])=Yes) AND (([QUESTOR Run tbl].[QUE$TOR Version Name])='7.3') AND (([QUESTOR Run tbl].[Cost Tab]) Like " & str1 & "))", CurrentProject.Connection

    str = rst.GetString
    'intgr = CDbl(str)
        
    MsgBox str
           
    'Debug.Print rst.GetString
    
    rst.Close
    
    Set rst = Nothing
        
End Sub

The message box is not giving any value. I think the problem is in the "Like" clause. Can somebody please help me with this. I am getting an answer if I am omitting the "Like" clause.

Thank you.

[Flo100]

I also tried the following code:

Sub CreateRecordset2()
    
    Dim rst As New ADODB.Recordset
    Dim str As String
        
    str = "SELECT Sum([QUESTOR Run tbl].[cost]) FROM [Project info Tbl] INNER JOIN [QUESTOR Run tbl] ON [Project info Tbl].[Project ID] = [QUESTOR Run tbl].[Project ID] WHERE ((([Project info Tbl].[Offshore only])=Yes) AND (([QUESTOR Run tbl].[QUE$TOR Version Name])='7.3') AND (([QUESTOR Run tbl].[Cost Tab]) Like '*' & 'drilling' & '*'))"

    rst.Open str, CurrentProject.Connection
    
    str = rst.GetString
           
    MsgBox str
           
    rst.Close
    
    Set rst = Nothing
        
End Sub

The message box is empty. When I copy the string in the variable "str" into a new query and try to get the result it gives me result in the datasheet view. Why am i not able to get an answer? The same stratedy works fine for queries like "SELECT * from [Project info tbl]. The message box displys all the records in that table. This strategy is also working for queries that have a "like" clause with exact string to match that is a "like" clause without * at the beginning and end. This is not working only for * in the like Clause. Can somebody please help.

Thank you.

[JKing]

Here's a little something from microsoft

The Jet Database Engine is used to work with the data in an Access (.mdb) database and supports the ANSI SQL-89 standard. However, data access pages in Access databases connect to Jet by using ActiveX Data Objects (ADO) and the Jet OLEDB provider. ADO and OLEDB always use ANSI SQL-92 syntax. Thus, data access pages must use ANSI SQL-92 syntax. This means that a form and a page bound to the same query can return different result data sets.

Here's the link as well ADODB Wildcards

So I think the solution here is:

[code=vb]
str = "SELECT Sum([QUESTOR Run tbl].[cost]) FROM [Project info Tbl] INNER JOIN [QUESTOR Run tbl] ON [Project info Tbl].[Project ID] = [QUESTOR Run tbl].[Project ID] WHERE ((([Project info Tbl].[Offshore only])=Yes) AND (([QUESTOR Run tbl].[QUE$TOR Version Name])='7.3') AND (([QUESTOR Run tbl].[Cost Tab]) Like '%drilling%'))"

rst.Open str, CurrentProject. Connection
[/code]

[JKing]

I'm making the assumption that you're using 2002,2003 or later.

[Flo100]

Here's a little something from microsoft

Here's the link as well ADODB Wildcards

So I think the solution here is:

[code=vb]
str = "SELECT Sum([QUESTOR Run tbl].[cost]) FROM [Project info Tbl] INNER JOIN [QUESTOR Run tbl] ON [Project info Tbl].[Project ID] = [QUESTOR Run tbl].[Project ID] WHERE ((([Project info Tbl].[Offshore only])=Yes) AND (([QUESTOR Run tbl].[QUE$TOR Version Name])='7.3') AND (([QUESTOR Run tbl].[Cost Tab]) Like '%drilling%'))"

rst.Open str, CurrentProject. Connection
[/code]

Thank you so much. Its working........ hehe....I am happy...happy.. happy

[JKing]

You're very welcome! Glad it worked for you.

Jking

[NeoPa]

I saw this and it reminded me that I had planned to put together an article on the differences between the ANSI-89 & ANSI-92 standards. It also includes instructions on how to use pattern matching to select the data you want (ANSI Standards in String Comparisons).
I hope you find it useful :)