Allen Brownes ECount()

  • Thread starter Thread starter Asib
  • Start date Start date
A

Asib

I am currently useing Allen Brownes Ecount to count distinct records in a
query. i was wondering if it was possible to adapt the code to count distict
records on a filtered continuous form? This is the code for the filter String.

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I have tried substituting the string for the query or table to no avail.
Any and all help or input is greatly appreciated.

Thanks, Asib
 
The code example you posted is not from Allen Browne's
ECount function, it is from his Search form, so presumably
you are wanting to return the recordcount after you have
selected criteria in the search form.

You should be able to use the same criteria string (strWhere)
that is built by the search form as the criteria argument in the
ECount function. Something like;

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSometable", strWhere)
End If

End Sub

The line right after Me.FilterOn = True is the only thing different.
You would need to use your own control and table names of course.
 
Hi Asib

There's no need to use a count function here. Just use the RecordCount
property of the form's Recordset:

Me.Filter = strWhere
Me.FilterOn = True
MsgBox "Rour filter returned " & Me.Recordset.RecordCount & " records"
 
Thanks for the response. I understand that the code i posted is not the
ecount code. I figured anyone responding would know the reference to allens
Ecount code. Your added line of code does work. The problem lies in the fact
that the form is not always filtered with that string.
And as far as the other post is concerned, i am counting distinct
records. So record count wont work.
Can i add an if, else to tell the form to use the ecount code that is
already the control source of the unbound textbox as default and use the
filter ecount if the form is filtered?

Thanks for the help.
Asibs

Beetle said:
The code example you posted is not from Allen Browne's
ECount function, it is from his Search form, so presumably
you are wanting to return the recordcount after you have
selected criteria in the search form.

You should be able to use the same criteria string (strWhere)
that is built by the search form as the criteria argument in the
ECount function. Something like;

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSometable", strWhere)
End If

End Sub

The line right after Me.FilterOn = True is the only thing different.
You would need to use your own control and table names of course.

--
_________

Sean Bailey


Asib said:
I am currently useing Allen Brownes Ecount to count distinct records in a
query. i was wondering if it was possible to adapt the code to count distict
records on a filtered continuous form? This is the code for the filter String.

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I have tried substituting the string for the query or table to no avail.
Any and all help or input is greatly appreciated.

Thanks, Asib
Click to expand...
Click to expand...
 
You could add another line in the existing If..Then..Else
statement towards the en of the procedure;

If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus
Me!txtRecordCount = ECount("*", "tblSomeTable") '<< new line
Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSomeTable", strWhere)
End If

Or, if you have some type of reset command button on your form to
remove the filter, you could put it in the click event of that button.
You may also want to put it in the form's Load event to make sure the
correct count is displayed when the form opens.

--
_________

Sean Bailey


Asib said:
Thanks for the response. I understand that the code i posted is not the
ecount code. I figured anyone responding would know the reference to allens
Ecount code. Your added line of code does work. The problem lies in the fact
that the form is not always filtered with that string.
And as far as the other post is concerned, i am counting distinct
records. So record count wont work.
Can i add an if, else to tell the form to use the ecount code that is
already the control source of the unbound textbox as default and use the
filter ecount if the form is filtered?

Thanks for the help.
Asibs

Beetle said:
The code example you posted is not from Allen Browne's
ECount function, it is from his Search form, so presumably
you are wanting to return the recordcount after you have
selected criteria in the search form.

You should be able to use the same criteria string (strWhere)
that is built by the search form as the criteria argument in the
ECount function. Something like;

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSometable", strWhere)
End If

End Sub

The line right after Me.FilterOn = True is the only thing different.
You would need to use your own control and table names of course.

--
_________

Sean Bailey


Asib said:
I am currently useing Allen Brownes Ecount to count distinct records in a
query. i was wondering if it was possible to adapt the code to count distict
records on a filtered continuous form? This is the code for the filter String.

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I have tried substituting the string for the query or table to no avail.
Any and all help or input is greatly appreciated.

Thanks, Asib
Click to expand...
Click to expand...
Click to expand...
 
I think this is going to work. Thanks for the help. These boards are a great
resource. Being the only one in the company even remotely involved in
creating the applications leaves me without anyone to bounce things off of. I
added the code to the strWhere and set the default value of the textbox to
=Ecount("[ORDNO]","[Query]","",True) seems like it should do the job. Now
all i have to do is get it to work with the reports. but that shouldnt be
that much different. im already passing the filter to the reports if it
exsists. Again thanks for all the help.
Asibs

Beetle said:
You could add another line in the existing If..Then..Else
statement towards the en of the procedure;

If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus
Me!txtRecordCount = ECount("*", "tblSomeTable") '<< new line
Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSomeTable", strWhere)
End If

Or, if you have some type of reset command button on your form to
remove the filter, you could put it in the click event of that button.
You may also want to put it in the form's Load event to make sure the
correct count is displayed when the form opens.

--
_________

Sean Bailey


Asib said:
Thanks for the response. I understand that the code i posted is not the
ecount code. I figured anyone responding would know the reference to allens
Ecount code. Your added line of code does work. The problem lies in the fact
that the form is not always filtered with that string.
And as far as the other post is concerned, i am counting distinct
records. So record count wont work.
Can i add an if, else to tell the form to use the ecount code that is
already the control source of the unbound textbox as default and use the
filter ecount if the form is filtered?

Thanks for the help.
Asibs

Beetle said:
The code example you posted is not from Allen Browne's
ECount function, it is from his Search form, so presumably
you are wanting to return the recordcount after you have
selected criteria in the search form.

You should be able to use the same criteria string (strWhere)
that is built by the search form as the criteria argument in the
ECount function. Something like;

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me!txtRecordCount = ECount("*", "tblSometable", strWhere)
End If

End Sub

The line right after Me.FilterOn = True is the only thing different.
You would need to use your own control and table names of course.

--
_________

Sean Bailey


:

I am currently useing Allen Brownes Ecount to count distinct records in a
query. i was wondering if it was possible to adapt the code to count distict
records on a filtered continuous form? This is the code for the filter String.

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.fltOrdno) Then
strWhere = strWhere & "([Ordno] Like ""*" & Me.fltOrdno & "*"") And"

End If

If Not IsNull(Me.fltItemNumber) Then
strWhere = strWhere & "([ITEM1]= """ & Me.fltItemNumber & """) And"

End If

If Not IsNull(Me.fltQtyord1) Then
strWhere = strWhere & "([QTYORD1] >= " & Me.fltQtyord1 & ") And"

End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox " No criteria", vbInformation, "Nothing to do."
Forms!TabbedForm!SubEverything!fltCustomer.SetFocus

Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I have tried substituting the string for the query or table to no avail.
Any and all help or input is greatly appreciated.

Thanks, Asib
Click to expand...
Click to expand...
Click to expand...
 
Back
Top