Count Text for the date

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Count Text for the date

Post by adam »

Hi anyone,

I'm trying to make the following code to count the number or rows containing 21:00 - 22:00 in my access table, when the user writes a date in the text box txtDate.

However, when doing so I'm getting syntax error messages. What may I be dong wrong here?

Code: Select all

Private Sub txtDate_Change()

Dim cnn         As ADODB.Connection
 Dim rst         As ADODB.Recordset
 Dim strSQL      As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Me.txtDate.Value <> "" Then
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=D:\V\C.accdb;"
    
    Set rst = New ADODB.Recordset
   
    strSQL = "SELECT COUNT[Name]FROM tblA WHERE [Name] = '21:00 - 22:00';"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Me.txtOne.Value.CopyFromRecordset rst
    
    'End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Text for the date

Post by HansV »

1) There should be parentheses around [Name] in COUNT[Name]:

Code: Select all

    strSQL = "SELECT Count([Name]) FROM tblA WHERE [Name] = '21:00 - 22:00';"
2) CopyFromRecordset is a method of the Range object in Excel; it does not apply to a text box, so the line

Code: Select all

    Me.txtOne.Value.CopyFromRecordset rst
makes no sense.

3) You have commented out the End If belonging to If Me.txtDate.Value <> "" Then.

4) You don't do anything with the value of txtDate.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Count Text for the date

Post by adam »

Instead of

Code: Select all

 Me.txtOne.Value.CopyFromRecordset rst
I changed the line to

Code: Select all

Me.txtOne.Value = rst![Name]
Here's the new version. I'm getting the error "Item cannot be found in the collection corresponding to the requested name or ordinal".

Code: Select all

Private Sub txtDate_Change()

Dim cnn         As ADODB.Connection
 Dim rst         As ADODB.Recordset
 Dim strSQL      As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Me.txtDate.Value <> "" Then
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=D:\V\C.accdb;"
    
    Set rst = New ADODB.Recordset
   
    strSQL = "SELECT COUNT([Name])FROM tblA WHERE [Name] = '21:00 - 22:00';"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Me.txtOne.Value = rst!
    
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Text for the date

Post by HansV »

1) Although it probably doesn't matter, I'd insert a space between Count([Name]) and FROM - see my previous reply.

2) Change the line

Code: Select all

    Me.txtOne.Value = rst![Name]
to

Code: Select all

    Me.txtOne.Value = rst(0)
3) The lines

Code: Select all

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
should be above End If, for you only open the connection and the recordset in the If ... End If block.

4) You still don't do anything with the value of txtDate.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Count Text for the date

Post by adam »

The code now counts the total rows for the 21:00 - 22:00 mentioned in the code. As you've been repeatedly saying how could I change the line so that the code would count the text for the date mentioned in the txtDate?

Code: Select all

Private Sub txtDate_Change()

   Dim cnn         As ADODB.Connection
   Dim rst         As ADODB.Recordset
   Dim strSQL      As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Me.txtDate.Value <> "" Then
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=D:\V\C.accdb;"
    
    Set rst = New ADODB.Recordset
   
    strSQL = "SELECT COUNT([Name]) FROM tblA WHERE [Name] = '21:00 - 22:00';"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Me.txtOne.Value = rst(0)
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Text for the date

Post by HansV »

Does tblA contain a date field? If so, what is its name?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Count Text for the date

Post by adam »

Yes it contain a date field. It's name is AppDate.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Text for the date

Post by HansV »

Change the line

Code: Select all

    strSQL = "SELECT COUNT([Name]) FROM tblA WHERE [Name] = '21:00 - 22:00';"
to

Code: Select all

    strSQL = "SELECT COUNT([Name]) FROM tblA WHERE [Name] = '21:00 - 22:00' AND AppDate=#" & Format(Me.txtDate, "mm/dd/yyyy") & "#"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Count Text for the date

Post by adam »

Thanks for the help hans. I don't get any error messages now. However even though I have text in column Name I am getting "0" in the textbox txtOne.

what may be the reason for this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Text for the date

Post by HansV »

I'd have to see a copy of the database (zipped) and of the workbook with the userform.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Count Text for the date

Post by adam »

It seemed to a problem with the format of the date. Now it's working fine. Thanks for the help Hans.
Best Regards,
Adam