var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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
...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
GROUP BY Patients.Name
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.
You're HAVING clause should be a WHERE clause.
WHERE Patients.Name = 'John'
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?
<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.
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!
Yes, yes they do, but one is better than the other
Originally Posted by
I only posted it for reference, thought you already had the solution, sorry!
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.
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.
That's a really good explanation Paul - I didn't know stuff like that was in the help files!
Thanks George. I actually got that from the JET help file, rather than Access Help itself. It probably isn't in the regular file.