Solved Ecount and concatenation

[Marshall Brooks]

I have a query that extracts records from a table that match a field in the current record on a form.

I use Dcount to tell if the query has any results, like this, and it works fine, but I would prefer to use Ecount:

If DCount("EVENT", "qryAuditLog") = 0 Then

Ecount works with other queries, but in this case, if I use Ecount, I get "Too few parameters; Expected one".

I saw https://allenbrowne.com/ser-66.html that the code with not work with a reference to a form in the arguments.

The WHERE clause in my query is:

WHERE tblAuditLog.REFERENCE= [Forms]![Form_A].[REFERENCE]

If I change this to:

WHERE tblAuditLog.REFERENCE= """ & [Forms]![Form_A].[REFERENCE] & """

I don't get the error with Ecount, but I also don't get any query results, b/c the query is looking for a field in the table with the actual text Forms!Form_A.Reference, not the value of the field.

If I change this to:

WHERE tblAuditLog.REFERENCE= "" & [Forms]![Form_A].[REFERENCE] & ""

The query works, but I still get the same error if I try to use Ecount.

If I change this to:

WHERE "tblAuditLog.REFERENCE= "" & [Forms]![Form_A].[REFERENCE] & """

I get hundreds of inaccurate results for the query, and I'm not sure if I get the error or not.

I'm hoping someone sees something I'm missing and can resolve the error message!

 

[theDBguy]

Give this one a try:

WHERE tblAuditLog.REFERENCE=Eval("[Forms]![Form_A].[REFERENCE]")

Just a thought...

 

[Marshall Brooks]

WHERE tblAuditLog.REFERENCE=Eval("[Forms]![Form_A].[REFERENCE]")

Good thought. Works for the query, but still gives me the Too Few Parameters Error with Ecount, and then the Invalid Use of Null Error.

I also tried:

WHERE tblAuditLog.REFERENCE=Eval(""" & [Forms]![Form_A].[REFERENCE] & """)

And with that, the query didn't show any results.

 

[Josef P.]

Could it be that you are trying to write the SQL text in the Access query in the same way as in VBA?

I would use a query without filtering by the form control and pass the filter expression to DCount/ECount.

If DCount("EVENT", "qryAuditLogWithoutFilterByForm", "REFERENCE = '" & Replace(Forms!Form_A!REFERENCE, "'", "''") & "'") = 0 Then

Perhaps better to understand (long version):

Private Sub DoSomething()

   Dim ReferenceString as String
   Dim WhereCondition as String

   ReferenceString = Forms!Form_A!REFERENCE ' or maybe just = Me!REFERENCE
   WhereCondition = "REFERENCE = " & TextToSqlText(ReferenceString)

   If DCount("EVENT", "qryAuditLogWithoutFilterByForm", WhereCondition) = 0 Then
   ....

End Sub

Public Function TextToSqlText(ByVal Value As Variant, _
                     Optional ByVal Delimiter As String = "'") As String

   Dim Result As String

   If IsNull(Value) Then
      TextToSqlText = "Null"
      Exit Function
   End If

   Result = Replace$(Value, Delimiter, Delimiter & Delimiter)
   Result = Delimiter & Result & Delimiter

   TextToSqlText = Result

End Function

 

[MajP]

I do not think Ecount will work with a query using a Parameter.

In the code you have
"SELECT Count(" & Expr & ") AS TheCount FROM " & Domain
If you pass in lets say Event, qryAuditlog then you will get something like

Select Count (Event) as TheCount From qryAudtiLog

Someone correct me if I am wrong, because I did not test this. But if I create a recordset in code using a query that has a Parameter reference to a form I will get an error, even if the Sql string itself does not include a parameter reference.

You are much better off rolling your own dcount without a query using a Parameter reference.

 

[Marshall Brooks]

@Josef P. and @MajP - I didn't quite follow the replies ...

Could it be that you are trying to write the SQL text in the Access query in the same way as in VBA?

No, not exactly.

The query works fine as written as long as I use DCount. ECount fails with it if I try to use it. I was trying to revise the query SQL to get DCount to work and that was failing.

I would use a query without filtering by the form control and pass the filter expression to DCount/ECount.

If DCount("EVENT", "qryAuditLogWithoutFilterByForm", "REFERENCE = '" & Replace(Forms!Form_A!REFERENCE, "'", "''") & "'") = 0 Then

I want the query filtered. I am using Dcount to tell if the query has any records. If it does, I display a button which when clicked displays the results of the query.

If I understand correctly, you are saying I should create a second unfiltered query and use your statement above, and if that generates results, then enable the button to show the initial query. That seems overly complex. Rather than that, I could just use an ELookup for the query or the underlying table matching on the form reference value - which would work fine, but I thought from what I'd read that Dcount/Ecount was faster.

@MajP - I didn't understand your response, other than that Ecount won't work with a query with a parameter reference - which seems to be what I am seeing. As I see it, my options are:

• Use the existing query as written, and use Dcount and ignore Ecount since it doesn't work in this case.
• Use the existing as written and use ELookup rather than either Dcount or Ecount.
• Create an unfiltered query and use Ecount on it, and/or create my own query in code (which I think would work but is somewhat beyond my comfort level at present.

 

[MajP]

I didn't understand your response, other than that Ecount won't work with a query with a parameter reference

I was describing how Allen brownes code works.
If creates a Sql string and uses that in a recordset
Normally if you create a recordset in code you need to resolved the Parameters before creating the Sql string used in code.
Since you query already has a parmater reference, you cannot resolve it ahead of time. So I am pretty sure AB's code cannot work if it is based on a query using a parameter reference.
@Josef P. provides the solution I was just explaining why I do not think the Ecount would work with a Parameter reference.

 

[Marshall Brooks]

@Josef P. added more info ...

As I see it, I'm still at the three options shown at the end of Reply #6:

• Keep using Dcount and ignore Ecount in this instance. I have tested and Ecount seems quite a bit faster than Dcount, but it doesn't work.
• Abandon Dcount/Ecount and use ELookup - which would work. Not sure how the speed for Elookup compares with Dcount, but it likely varies and also likely doesn't matter - a few extra milliseconds here or there.
• @Josef P. solution looks like it should allow Ecount to work, but as I understand it, I would be running Ecount on an unfiltered query to determine whether the filtered query has results. It would probably work, but seems like a lot of effort.

 

[MajP]

• @Josef P. solution looks like it should allow Ecount to work, but as I understand it, I would be running Ecount on an unfiltered query to determine whether the filtered query has results. It would probably work, but seems like a lot of effort.

Yes you can continue to use Ecount, but base it on a query without a Parameter reference.
Than pass in the form reference in the Criteria portion of the ECount as described by Allen Browne. Or resolved in code before passing.

 

[theDBguy]

Good thought. Works for the query, but still gives me the Too Few Parameters Error with Ecount, and then the Invalid Use of Null Error.

I also tried:

And with that, the query didn't show any results.

This is how I solved the same issue in my own function. Hopefully, you could learn something from it, enough to adjust AB's function to work with your query.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

 

[Josef P.]

In addition to MajP: ECount works with a Recordset. A Recordset cannot access form elements.
If you want to keep the form reference in the query, you can alternatively adapt the ECount function.
/edit: adapt the ECount function with a QueryDef, as shown in #10

Example file:

 

[Marshall Brooks]

If you want to keep the form reference in the query, you can alternatively adapt the ECount function.

THANK YOU!!!! - Ecount2 works perfectly!

 

[MajP]

@Josef P.
I posted your function so others can see. Sometimes people do not know what is in a db and will not open
Added a statement to the purpose.

Public Function ECount2(Expr As String, Domain As String, Optional Criteria As String, Optional bCountDistinct As Boolean) As Variant
On Error GoTo Err_Handler
    'Purpose:   Enhanced DCount() function, with the ability to count distinct.
     '          Will handle Form Parameter references not handled in ECOUNT           
                
    'Return:    Number of records. Null on error.
    'Arguments: Expr           = name of the field to count. Use square brackets if the name contains a space.
    '           Domain         = name of the table or query.
    '           Criteria       = any restrictions. Can omit.
    '           bCountDistinct = True to return the number of distinct values in the field. Omit for normal count.
    'Notes:     Nulls are excluded (whether distinct count or not.)
    '           Use "*" for Expr if you want to count the nulls too.
    '           You cannot use "*" if bCountDistinct is True.
    'Examples:  Number of customers who have a region: ECount("Region", "Customers")
    '           Number of customers who have no region: ECount("*", "Customers", "Region Is Null")
    '           Number of distinct regions: ECount("Region", "Customers", ,True)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strSql As String

    'Initialize to return Null on error.
    ECount2 = Null
    Set db = DBEngine(0)(0)

    If bCountDistinct Then
        'Count distinct values.
        If Expr <> "*" Then             'Cannot count distinct with the wildcard.
            strSql = "SELECT distinct " & Expr & " FROM " & Domain & " WHERE (" & Expr & " Is Not Null)"
            If Criteria <> vbNullString Then
                strSql = strSql & " AND (" & Criteria & ")"
            End If
            strSql = "select Count(*) from (" & strSql & ") as X"
        Else
            ' raise error?
            Exit Function
        End If
    Else
        'Normal count.
        strSql = "SELECT Count(" & Expr & ") AS TheCount FROM " & Domain
        If Criteria <> vbNullString Then
            strSql = strSql & " WHERE " & Criteria
        End If
    End If
    
    Set qdf = db.CreateQueryDef("", strSql)
    
    CheckParams qdf

    Set rs = qdf.OpenRecordset()
    ECount2 = rs.Fields(0).Value
    rs.Close
    qdf.Close

Exit_Handler:
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

Err_Handler:
    MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number
    Resume Exit_Handler
    
End Function

Private Sub CheckParams(ByVal qdf As DAO.QueryDef)

    If qdf.Parameters.Count = 0 Then
        Exit Sub
    End If
    
    Dim p As DAO.Parameter
    For Each p In qdf.Parameters
        p.Value = Eval(p.Name)
    Next

End Sub

 

[Josef P.]

@MajP: thanks!
Note to code: It is a customized and minimal refactored version of Allen Browne's ECount function.