none
Error: xx RRS feed

  • Question

  • I have a table that holds the five date fields that I want to be able to count based on a user directed date range.  I have created a query to gather the dates, without restriction.  I have created the form that will guide the user to input the required date requirements.  However, when I run the code below I receive and error....

    The error is occurring in "For Each fld In db.TableDefs!ScoreCard_Query.Fields"....I tried changing "TabelDefs" to the db name (front end), but it did not work.

    Thank you, for your help/insight.

    Mayhem.

    Private Sub CalcScoreCard_Click()
      Dim tmpRS As DAO.Recordset
      Dim db As DAO.Database
      Dim fld As DAO.Field
      Set db = CurrentDb
        For Each fld In db.TableDefs!ScoreCard_Query.Fields

          For Each ctlVar In Me.Controls
            If ctlVar.ControlType = acTextBox Then
          
              If ctlVar.Name = fld.Name Then
                Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.StartDate & "# and #" & Me.EndDate & "#;")
                  
                  If tmpRS.RecordCount > 0 Then
                    ctlVar.Value = tmpRS.Fields(0)
                         
                  Else
                    ctlVar.Value = 0
                  
                  End If
              End If
            End If
                
                Set tmpRS = Nothing

          
          Next
        Next
    End Sub


    Thank you, Mayhem65

    Thursday, March 1, 2018 8:41 PM

Answers

All replies

  • Hi,

    I could be wrong but is ScoreCard_Query a table or a query? If the latter, I am not sure it will be a part of the TableDefs collection.

    What is the actual error message you're getting? Is it something about "cannot find" or "does not have this method" or something similar?

    Thursday, March 1, 2018 8:46 PM
  • I could be wrong but is ScoreCard_Query a table or a query? If the latter, I am not sure it will be a part of the TableDefs collection.

    I am sure it will not.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 1, 2018 8:57 PM
  • .theDBguy,

    It is a query.  The error message is....

          Run-time error '3265':

                 Item not found in this collection.

    Mayhem.


    Thank you, Mayhem65

    Thursday, March 1, 2018 8:57 PM
  • You will need to at least declare what ctlVar is. If it's a control, then:

    Dim ctlVar As Control

    Thursday, March 1, 2018 8:59 PM
  • Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.StartDate & "# and #" & Me.EndDate & "#;")

    Aside from the issue with queries not being members of the TableDefs collection, the above constructed query seems a bit odd, and I wonder if it is doing what you want.  Are the names of these fields -- the ones that you are iterating through -- themselves date values?  The clause

        where CDate(" & fld.Name & ") between #" & Me.StartDate & "# and #" & Me.EndDate & "#"

    would be converting the *name* of the field to a date, and checking whether that date falls between StartDate and EndDate.  Is that really what you intended?  Or did you only want to count records where the *value* of the field falls between those dates?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 1, 2018 9:07 PM
  • Is this what you mean?

    Private Sub CalcScoreCard_Click()
      Dim ctlVar As Control
      Dim tmpRS As DAO.Recordset
      Dim db As DAO.Database
      Dim fld As DAO.Field
      Set db = CurrentDb
        For Each fld In db.TableDefs!ScoreCard_Query.Fields

          For Each ctlVar In Me.Controls
            If ctlVar.ControlType = acTextBox Then
          
              If ctlVar.Name = fld.Name Then
                Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.StartDate & "# and #" & Me.EndDate & "#;")
                  
                  If tmpRS.RecordCount > 0 Then
                    ctlVar.Value = tmpRS.Fields(0)
                         
                  Else
                    ctlVar.Value = 0
                  
                  End If
              End If
            End If
                
                Set tmpRS = Nothing
        
          Next
        Next
    End Sub



    Thank you, Mayhem65

    Thursday, March 1, 2018 9:18 PM
  • .theDBguy,

    It is a query.  The error message is....

          Run-time error '3265':

                 Item not found in this collection.

    Mayhem.


    Thank you, Mayhem65

    Hi,

    Can you tell us exactly what you're trying to accomplish (with your code)? Maybe we can modify the code to do what you want.

    Thursday, March 1, 2018 9:18 PM
  • Okay....

    I have a table, within it contains five fields (dates).  These dates record an event occurring.  I need to display how many of these events occur, within a particular; changeable range.  This range needs to be defined by the user.

    I have created a query (ScoreCard Query) that accurately gathers the fields and there data, from the table.  I am now trying to display the results of this evaluation on the appropriate form.

    The form has unbounded text boxes for each of the required fields.  I have named the text boxes to receive the tallies with names of the fields from the table.

    I was able to accomplish this in an excel spreadsheet, but the transition to Access is proving challenging.


    Thank you, Mayhem65

    Thursday, March 1, 2018 9:32 PM
  • Hi,

    Thank you for the clarification. If you have a query that can show the data you need to display, why not just create a form based on that same query rather than try to pull data from it to insert into unbound textboxes on another form?

    Just curious...

    Thursday, March 1, 2018 9:55 PM
  • Okay....

    How would I then display, just the count, for each field (5 separate), that fall into a user defined range?

    I am sorry, but I am still learning the coding part of Access.


    Thank you, Mayhem65

    Thursday, March 1, 2018 10:18 PM
  • ...I started my conversation with this question "

    How do I count dates that fall between user directed start/end dates; for multiple fields?", a few weeks ago.

    Now we have arrived here....


    Thank you, Mayhem65

    Thursday, March 1, 2018 10:20 PM
  • Okay....

    How would I then display, just the count, for each field (5 separate), that fall into a user defined range?

    I am sorry, but I am still learning the coding part of Access.


    Thank you, Mayhem65

    Hi,

    I guess I must have misunderstood your statement when you said you have created a query that gathers the data you need to display.

    If you're saying you still need to calculate the result data, which means you don't have the data you need to display yet, then let's start with you describing your table structure.

    We can then create a query for you showing the "count" of every event based on user input for a date range.

    Just to be clear, we need the name of the table and the name of the fields in it. Thanks.

    Thursday, March 1, 2018 10:36 PM
  • ...I started my conversation with this question "

    How do I count dates that fall between user directed start/end dates; for multiple fields?", a few weeks ago.

    Now we have arrived here....


    Thank you, Mayhem65

    Hi,

    There's no need to shout. We're just trying to help you but please understand it's hard to get the whole picture using just words and without seeing what you have to work with.

    To answer your question, you can use a parameterized Totals query to count the dates in your table but we really need to see your table structure to give you any detailed instruction on how to construct the Totals query.

    Thursday, March 1, 2018 10:39 PM
  • ...not shouting, just pasting the text from the page.

    Thank you, Mayhem65

    Friday, March 2, 2018 2:45 PM
  • Thank you, for your patience.

    The table name is "Customers"; I am running a split database.

    Field names: 'CustDate', 'StudioDate', 'DRSignDate', 'ContractDate', 'DJDate'.


    Thank you, Mayhem65

    Friday, March 2, 2018 2:52 PM
  • ...not shouting, just pasting the text from the page.

    Thank you, Mayhem65

    Oh, okay. In Forum posts, using large fonts is considered shouting. Just so you know...

    To emphasize text, using bold fonts is typically enough.

    Friday, March 2, 2018 3:36 PM
  • Thank you, for your patience.

    The table name is "Customers"; I am running a split database.

    Field names: 'CustDate', 'StudioDate', 'DRSignDate', 'ContractDate', 'DJDate'.


    Thank you, Mayhem65

    Okay, thanks. That's a good start.

    So let's say you want the user to enter a start and end date, and let's just count how many StudioDates are within said range, you can create a query like so:

    SELECT Count(*) As Total FROM Customers WHERE StudioDate Between [Enter Start Date] And [Enter End Date]

    Give it a try and let us know what happens.

    Friday, March 2, 2018 3:38 PM
  • Thank you, for your patience.

    The table name is "Customers"; I am running a split database.

    Field names: 'CustDate', 'StudioDate', 'DRSignDate', 'ContractDate', 'DJDate'.


    Thank you, Mayhem65

    Okay, thanks. That's a good start.

    So let's say you want the user to enter a start and end date, and let's just count how many StudioDates are within said range, you can create a query like so:

    SELECT Count(*) As Total FROM Customers WHERE StudioDate Between [Enter Start Date] And [Enter End Date]

    Give it a try and let us know what happens.

    Okay.  Are you saying that I enter this into the criteria section of my current query; which is pulling all of these fields out of the table?  Or, am I entering this in to the command button that executes the this formula?

    Thank you, Mayhem65

    Friday, March 2, 2018 4:18 PM
  • Nope. I am saying it's one way to demonstrate "how to count" records. I am hoping to teach you, so you can apply the technique to the rest of the fields. Once you understood how the query I gave you works, you might end up with a totally different query than the one you already have; except, it should have the results you were looking for.

    Like I said though, give it a try and let us know how it goes. For example, take the SQL statement I gave you and create a new query using it. What happens?

    Friday, March 2, 2018 4:22 PM
  • Got it....baby steps; thank you.

    So, I created a new query, 'Test Query'.

    I selected the 'Customers' table and 'StudioDate' field.

    I entered the SQL statement into the criteria and I receive a blank return; an empty cell.


    Thank you, Mayhem65

    Friday, March 2, 2018 4:32 PM
  • Ah, I see. It seems I was not clear in my instructions. Try the following steps instead:

    1. Create a new query

    2. Close the Add Tables window

    3. From the Ribbon, click on the View dropdown and select SQL

    4. Copy and paste the SQL statement I gave you into Access

    5. View the query in Design or Datasheet view

    Hope it's clearer...

    Friday, March 2, 2018 4:48 PM
  • Ah, I see. It seems I was not clear in my instructions. Try the following steps instead:

    1. Create a new query

    2. Close the Add Tables window

    3. From the Ribbon, click on the View dropdown and select SQL

    4. Copy and paste the SQL statement I gave you into Access

    5. View the query in Design or Datasheet view

    Hope it's clearer...

    Great - Forward movement....

    It is much clearer, thank you.

    I have run the query for an all inclusive date range and it returns the correct total number of records; when I run it for 2018 (all) correct #, but it does not find two records for 2017 (all).

    Thoughts?


    Thank you, Mayhem65

    Friday, March 2, 2018 5:08 PM
  • Never mind... I found my error.

    It works; returns the correct number of dates in all cases.

    So, the next question is...can a query run five of these at a time or do I need a formula to run a macro?


    Thank you, Mayhem65

    Friday, March 2, 2018 5:16 PM
  • I have tried.....

    SELECT Count(*) AS Prospects, Count(*) AS Appointments
    FROM Customers
    WHERE (((Customers.CustDate) Between [Enter Start Date] And [Enter End Date]) or ((Customers.StudioDate) Between [Enter Start Date] And [Enter End Date]));

    ....with 'and' and 'or'; neither is returning a correct count of the fields individually.  It is not even returning shared values....

    Thoughts?


    Thank you, Mayhem65

    Friday, March 2, 2018 5:32 PM
  • Hi,

    Glad to hear you're making good progress. Please consider all these exercises as a way to give you a broader idea on different approaches to get to the same result in Access.

    Now, let's try a different query. Create a new query using the following SQL statement:

    SELECT DCount("*", "Customers", "StudioDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") As StudioDate, DCount("*", "Customers", "ContractDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#" As ContractDate

    Hope it helps...

    Friday, March 2, 2018 5:39 PM
  • Hi,

    Glad to hear you're making good progress. Please consider all these exercises as a way to give you a broader idea on different approaches to get to the same result in Access.

    Now, let's try a different query. Create a new query using the following SQL statement:

    SELECT DCount("*", "Customers", "StudioDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") As StudioDate, DCount("*", "Customers", "ContractDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#" As ContractDate

    Hope it helps...

    Okay, that worked.  Thank you.

    In an attempt to stretch my legs....I have created the following; although I got an error message when I tried it...

    SELECT DCount("*","Customers","CustDate Between #" & [Start Date] & "# And #" & [End Date] & "&") AS Prospects, DCount("*","Customers","StudioDate Between #" & [Start Date] & "# And #" & [End Date] & "&") AS Appointments, DCount("*","Customers","DRSignDate Between #" & [Start Date] & "# And #" & [End Date] & "&") AS Design Retainers, DCount("*","Customers","ContractDate Between #" & [Start Date] & "# And #" & [End Date] & "&") AS Contracts, DCount("*","Customers","DJDate Between #" & [Start Date] & "# And #" & [End Date] & "&") AS Dead Jobs;

    ...am I close?


    Thank you, Mayhem65

    Friday, March 2, 2018 8:09 PM
  • Hi,

    Just a minor change...

    It looks like you have ... & [End Date & "&") AS... instead of ... & [End Date] & "#") AS...

    In other words, use "#" instead of "&".

    Hope it helps...

    Friday, March 2, 2018 8:12 PM
  • Hi,

    Just a minor change...

    It looks like you have ... & [End Date & "&") AS... instead of ... & [End Date] & "#") AS...

    In other words, use "#" instead of "&".

    Hope it helps...

    Thank you, so much, for your patience.

    I was chewing on it before your reply and this seems to work....

    SELECT DCount("*","Customers","CustDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS Prospects, DCount("*","Customers","StudioDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS Appointments, DCount("*","Customers","DRSignDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS [Design Retainers], DCount("*","Customers","ContractDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS Contracts, DCount("*","Customers","DJDate Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS [Dead Jobs];

    I was able to get this to throw out the correct numbers for an all inclusive search.  All five fields accurately represented.

    So, thinking ahead how would I drive this with a form?  I would like to prompt the user for the date range they want to use and then have the number show in text boxes on the form.  Can I set the formula to run through command button and the results to appear in text boxes with the field names assigned?

    Thoughts?


    Thank you, Mayhem65

    Friday, March 2, 2018 8:49 PM
  • Hi,

    If you simply want to display the counts on a form, then we don't even need a query. You can use the DCount() function in each Textbox on the form. I suspect you want the user to enter the date range on the same form. If so, the DCount() expression might look something like this:

    =DCount("*", "Customers", "StudioDate Between #" & [StartDateTextboxName] & "# And #" & [EndDateTextboxName] & "#")

    Hope it helps...

    Friday, March 2, 2018 9:44 PM
  • Hi,

    If you simply want to display the counts on a form, then we don't even need a query. You can use the DCount() function in each Textbox on the form. I suspect you want the user to enter the date range on the same form. If so, the DCount() expression might look something like this:

    =DCount("*", "Customers", "StudioDate Between #" & [StartDateTextboxName] & "# And #" & [EndDateTextboxName] & "#")

    Hope it helps...

    Thank you.  I will give this a shot next week.

    When you say to put the DCount into each TextBox, do you mean in one of the Event fields under properties?

    Maybe update and then have the command button update/refresh the form after the dates are entered?


    Thank you, Mayhem65

    Friday, March 2, 2018 10:28 PM
  • Hi,

    No, not in an Event. Simply put the exact expression I posted (including the equal sign) in the Control Source of the Textbox and see what you get.

    Saturday, March 3, 2018 4:00 PM
  • Hi,

    No, not in an Event. Simply put the exact expression I posted (including the equal sign) in the Control Source of the Textbox and see what you get.

    Thank you.  This pulls the information up on the form; after a few clicks around the form.  I have tried to attach a command button with me.refresh; to allow the data to be displayed, but this did not work.  Is there another command that I should use to accomplish this?  Currently, after I enter the start and end dates, I have to click one of the text boxes to get the form to update; showing the data.

    Thank you, Mayhem65

    Monday, March 5, 2018 6:00 PM
  • Yes, for your command button, try using: Me.Recalc

    Hope it helps...

    • Marked as answer by Mayhem65 Monday, March 5, 2018 6:21 PM
    Monday, March 5, 2018 6:02 PM
  • Yes, for your command button, try using: Me.Recalc

    Hope it helps...

    Thank you, so much for your help on this.  Now I will try to use this information to put together another, that will show similar data, but using some conditional statements.  We will see how it goes.

    Thank you, Mayhem65

    Monday, March 5, 2018 6:22 PM
  • Hi,

    You're welcome. Good luck with your project.

    Monday, March 5, 2018 6:35 PM