Help with DCount

[Guest]

I am wanting to create a form that counts records from a table based on
criteria.
My table has one field which contains a value consisting of 8 characters
that begin with various number of prefixes. I want to Count the number of
records beginning with the various prefixes but am unable to use DCount with
a wildcard in the criteria section.

Table:

ID123453
ID234565
IE490589
CS83736
CS98726
CS83780

I want the form to return:

ID 2
IE 1
CS 3

 

[Guest]

Create an unbound textbox and enter the formula in the Control Source Property:

=DCount("Field1", "Table1", "Field1 LIKE 'ID*'")
of course substitute the correct Field and Table names in the formula above.

jmonty

 

[Douglas J. Steele]

You should be able to use DCount("*", "MyTable", "MyField Like 'ID*'"),
DCount("*", "MyTable", "MyField Like 'IE*'"), etc.

Of course, you might find it easier to create a query that returns
everything for you:

SELECT Left([MyField], 2) AS Prefix, Count(*) AS Total
FROM MyTable
GROUP BY Left([MyField], 2)

 

[Guest]

EB,

For the single two-character prefix "CS", the expression syntax using DCount
is:

=DCount("[YourField]","YourTable","Mid([YourField],1,2)='CS'")

A better way is to use a Totals query, and base a continuous form on it:

SELECT Mid([YourField],1,2) AS Prefix, Count(YourTable.YourPK) AS
CountOfYourPK
FROM YourTable
GROUP BY Mid([YourTable],1,2);

This will return the table of all prefixes and their counts.

Sprinks

 

[Guest]

Thanks! That worked. I'm actually doing this in a report and discovered I
get what I want when placed in the pg header or footer.

I had tried this and was getting close. I had the syntax wrong in the
criteria portion of the the DCount.

Thanks again,
EB

 

[Guest]

Very cool! That worked great. Thanks!

You should be able to use DCount("*", "MyTable", "MyField Like 'ID*'"),
DCount("*", "MyTable", "MyField Like 'IE*'"), etc.

Of course, you might find it easier to create a query that returns
everything for you:

SELECT Left([MyField], 2) AS Prefix, Count(*) AS Total
FROM MyTable
GROUP BY Left([MyField], 2)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am wanting to create a form that counts records from a table based on
criteria.
My table has one field which contains a value consisting of 8 characters
that begin with various number of prefixes. I want to Count the number of
records beginning with the various prefixes but am unable to use DCount
with
a wildcard in the criteria section.

Table:

ID123453
ID234565
IE490589
CS83736
CS98726
CS83780

I want the form to return:

ID 2
IE 1
CS 3