sql help

[buggy2]

I am running the following sql statement in my code by I keep getting an error telling me that this is not a valid sql statement, however when I run a query using the statement it is fine.


DoCmd.RunSQL ("SELECT Count([Master].[Name]) FROM [Master] GROUP BY [Master].[PLACE], [Master].TDATE HAVING ((([Master].[PLACE])=[Forms]![board].[Form]![cmbo_place]) AND (([ABC Master].TDATE)=[Forms]![board].[Form]![cmbo_tdate]));")

 

[smozgur]

RunSQL Method only executes ACTION queries (like UPDATE, INSERT ONLY etc..)

You have a SELECT query. You can use something like this:

Currentdb.OpenRecordSet ("SELECT Count([Master].[Name]) FROM [Master] GROUP BY [Master].[PLACE], [Master].TDATE HAVING ((([Master].[PLACE])=[Forms]![board].[Form]![cmbo_place]) AND (([ABC Master].TDATE)=[Forms]![board].[Form]![cmbo_tdate]));").Fields(0).value

or may be a stored query in database. Or anything else but not RunSQL method.

I hope it helps.
Suat

 

[buggy2]

sql

I have already tried your suggestion but it resulted in an 'invalid use of property' error relating to '.Fields.Value'.

Do you know any other way around this?.

 

[smozgur]

Already tried ?

So you already knew that it returns a value so you should use it like :

SOMEVAR = Currentdb.OpenRecordSet ("SELECT Count([Master].[Name]) FROM [Master] GROUP BY [Master].[PLACE], [Master].TDATE HAVING ((([Master].[PLACE])=[Forms]![board].[Form]![cmbo_place]) AND (([ABC Master].TDATE)=[Forms]![board].[Form]![cmbo_tdate]));").Fields(0).value

Good luck.

 

[Russell Hauf]

You can try something like this:

Option Explicit

Sub CreateSelectQuery()
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim strSQL As String
    
    Set db = CurrentDb
    
    strSQL = "SELECT Count([Master].[Name]) " & _
            "FROM [Master] " & _
            "GROUP BY [Master].[PLACE], [Master].TDATE " & _
            "HAVING ((([Master].[PLACE])=[Forms]![board].[Form]![cmbo_place]) " & _
            "AND (([ABC Master].TDATE)=[Forms]![board].[Form]![cmbo_tdate]));"
    
    Set qdf = db.CreateQueryDef("qryNew", strSQL)
    DoCmd.OpenQuery "qryNew"
    
    ' if you don't want to keep the query,
    ' you can do something like this:
    
    db.QueryDefs.Delete ("qryNew")
    
    Set qdf = Nothing
    Set db = Nothing
End Sub

Note that if you are using Access97 you don't need the "DAO."

You may need to set a reference to "Microsoft DAO X.x Object Library" in Tools-References.

HTH,

Russell

 

[buggy2]

SQL.

thats code work perfectly and i can build other queries around it. I knew '.Fields(Item).Value' returned a value but even if assign it to a variable you still cant use it in the context i need it, it is an invalid use of the .value property.

Thanks for the help.