Domain in DSum function

[arista via AccessMonster.com]

Hi, can I define domain in DSum function in different way ? Generaly DSum
(expr, domain, [criteria]) where domain is name of query or table in quotes.
I am writing code in VBA and would like to use as a domain this code:

dim strSQL as String
Dim myData As New ADODB.Recordset
'here presented strSQL is only shortcut, the real strSQL string is much
longer
strSQL = "SELECT tblShiftData.Date FROM tblShiftData ORDER BY tblShiftData.
Date, tblShiftData.ShiftNo;"
myData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

I was trying to define domain in DSum function like:
Me!Text59.ControlSource = "=CSng(Nz(DSum(""[PayCount]"",""myData"",""
[SuppTypeID]=6""),0))"
or
Me!Text59.ControlSource = "=CSng(Nz(DSum(""[PayCount]"",""" & strSQL & ",""
[SuppTypeID]=6""),0))"

BUT UNCESSFULLY !
Is it possible somehow please ? Thanks

 

[Douglas J Steele]

No, that's not possible.

However, why not define a query using that SQL?

 

[arista via AccessMonster.com]

This is not lucky. I have already a lot of queries, tables, etc. in my
database so some error message during creating mde file sometimes offer (more
than 500 ...). Also I would like to include into the query some conditions
which result to neccessity make more queries. I can easily cope with these
conditions in the VBA code so therefore I thought it could be better to make
the sum (using DSum function) of some field from the code.
Is there please another option how to make a sum of some field from the VBA
code ?
Thanks

 

[Douglas J Steele]

Dynamically create the query.

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef

strSQL = "SELECT ..."
dbCurr = CurrentDb()

On Error Resume Next
Set qdfTemp = dbCurr.QueryDefs("MyTempQuery")
If Err.Number = 3245 Then ' query doesn't exist
Set qdfTemp = CurrentDb.CreateQueryDef('MyTempQuery", strSQL)
Else
qdfTemp.SQL = strSQL
End If

You can now use MyTempQuery in your DSum query.

Alternatively, write your own function that does the equivalent of DSum.

 

[Duane Hookom]

Actually, you should be able to use the generic Concatenate() function found
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You
could set a control source to something like:
=Concatenate("SELECT Sum(Qty*UnitPrice) FROM OrderDetails WHERE OrderNum =
3")

This function takes a sql string as its first argument. If there are more
than one records returned, then the returned value from the function is a
concatenated list of values with a delimiter.

 

[arista via AccessMonster.com]

Thanks a lot for useful tips. I solved task with your help. My question is if
I can do the same (dynamically create the query) with usage of ADODB library
instead of DAO library ? And how please ?

 

[Douglas J Steele]

Not with ADO, but I think you can with ADOX.