locked
VB.NET/Access - Counting date entries in a field RRS feed

  • Question

  • I have a table with the follow fields.  StudentID, LastName, FirstName, TrainDate.  I have windows form that I use to input the date of attendance in the TrainDate field.  The TrainDate field is short text.  My goal is to count how many TrainDate entries each person has and display it in a datagridview.

    This is the query I am working on but I get the following error:  "Date type mismatch in criteria expression"

    Access.ExecQuery("SELECT FirstName AS [FIRST NAME], LastName AS [LAST NAME],
                                     SUM(TrainDate) AS [TOTAL CLASSES]
                              FROM ACJATTENDANCE
                              GROUP BY FirstName, LastName, TrainDate;")
    
            ' REPORT & ABORT ON ERRORS
            If NoErrors(True) = False Then End
    
            ' FILL DATAGRID
            dgvAttend.DataSource = Access.DBDT

    Can someone help me figure out the reason for the error?  I have used this query to calculate wins and losses in another part of the program successfully.  I'm not sure but I think this has something to do with TrainDate.

    Tuesday, March 24, 2020 1:36 PM

Answers

  • Hello,

    You can't count TrainDate as short text.

    Use

    SELECT 
       FirstName AS [First Name],
      LastName AS [Last Name], 
      TrainDate AS [Training Date], Count(*) AS TotalCount
    FROM 
       ACJATTENDANCE
    GROUP BY FirstName, LastName, TrainDate, TrainDate;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 24, 2020 2:00 PM

All replies

  • Hello,

    You can't count TrainDate as short text.

    Use

    SELECT 
       FirstName AS [First Name],
      LastName AS [Last Name], 
      TrainDate AS [Training Date], Count(*) AS TotalCount
    FROM 
       ACJATTENDANCE
    GROUP BY FirstName, LastName, TrainDate, TrainDate;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 24, 2020 2:00 PM
  • Hi,

    I looked closer at the results of the query you suggested and it was doing what I needed it too.

    What it doing is returning all the training dates for everyone unless they had multiple classes on the same day.  So I screwed up my test data by copying and pasting rows which is how they had multiple classes on the same day.

    I figured out that TrainDate was causing the issue.  So I did this...

    SELECT FirstName AS [FIRST NAME], 
           LastName AS [LAST NAME],
    COUNT(TrainDate) AS [TOTAL CLASSES]
    FROM BJJATTENDANCE
    GROUP BY FirstName, LastName
    Now my issue is how to get the date of their most recent training session on the datagridview as its own column displaying the most recent training date for each student without screwing my total class count.

    Do you have any suggestions?

    Thursday, March 26, 2020 5:28 PM
  • I would do this via "group by" or "GroupBy" in VB.NET, not in the database. Sadly I'm on the front lines of this virus thing and will not get back to my person laptop until Saturday and don't have time to rig everything up to provide a quality example. 

    Although the following will not assist with your exact task if you never used grouping in code feel free to look at some of my examples. The majority are Entity Framework with some using DataTable containers.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 26, 2020 7:43 PM