Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Executing COUNT SQL from VBA on form!

    OK, here is what I want to do!

    After the user enters a person's name in a data-entry form I want to know how many times that name has been entered in the table.

    For example user enters

    Name: John

    ...in a text field on a form

    and I want another unbound text box to display

    We have 9 John named persons so far.



    It comes down to query that looks like this in SQL view. My only problem is I can't get it to work inside forms VBA code.

    SELECT Patients.Name, Count(Patients.Name) AS CountOfName
    FROM Patients
    GROUP BY Patients.Name
    HAVING (((Patients.Name)="John"));

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Simplest would probably be:

    =DCount("*", "Patients", "Name = 'John')

    You'd probably want to modify it to get the name from the form. You could also open a recordset on that SQL and get the result from that.
    Paul

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're HAVING clause should be a WHERE clause.
    Code:
    SELECT Patients.Name
         , Count(Patients.Name)
    FROM   Patients
    WHERE  Patients.Name = 'John'
    GROUP
        BY Patients.Name
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    Both queries produce the same and correct result georgev!

    However my question is how I assign the value of that COUNT to a variable in VBA code?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    <myvariablename> =DCount("*", "Patients", "Name = 'John')
    replace <myvariablename> with the required variable name
    theres a thing called help in Access.. select the item you need help on and press f1
    ...or just dive right on in by selecting help form the command bar menu.

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    Thanks healdem

    I use help all the time, number of questions I'd have would be in the range of 100s if I didn't - instead I have 2 and a half going on!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Riorin
    Both queries produce the same and correct result georgev!
    Yes, yes they do, but one is better than the other

    I only posted it for reference, thought you already had the solution, sorry!
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Posts
    108
    You got me curious now georgev!

    I honnestly don't know why one is better than the other!

    I haven't really dived into depths of queries, I understand what they do and implement them on need-to-know basis.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your answer lies in the difference between WHERE and HAVING, and when each is applied. From Help:

    WHERE determines which records are selected. Similarly, once records are grouped with GROUP BY, HAVING determines which records are displayed.
    Paul

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's a really good explanation Paul - I didn't know stuff like that was in the help files!
    George
    Home | Blog

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Thanks George. I actually got that from the JET help file, rather than Access Help itself. It probably isn't in the regular file.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •