Count will not work?

[Tejanorey]

I have searched through the website and have searched through a book that I own, the help menu on MS Access, and still no progress...:-( I have a database that is being "fed" by an Excel Document (linked) and have two forms which is a switchboard and a main form. I have no tables and have two different queries. One query has total records and the other had total records but sorted by "shop" both of these reports are generated by the two queries that I have. I have tried the count functions and It wouldn't work. I tried =count(*) and =dcount(*) and =count(shop) and =dcount(shop) and so on. I keep getting the #error message. I would like to know how to count total records on my "total report" and to count by shop on my "shop report" (how many Marines work per shop) so the report will display all of the shops in one report but sorted by specific shop and with a total next to it. My fields are: Name, Shop, Rank, Address, City, Zip, home, work, m/s, spouse, and kids. I know it seems alot to ask but I'm having trouble finding a solution for this so your input would be greatly appreciated. Semper Fi! Tejanorey

"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing

 

[PaulBricker]

You can try variations of this expression
=Sum(IIf([FieldName]= SomeValue,1,0))
Summing 1's and 0's is the same thing as counting. So in your case to count all the records you could try
=Sum(IIf(Not IsNull([Name]),1,0))
Then to get a count of the shops you can try
=Sum(IIf([Shop]= Whatever,1,0))

Paul

 

[Tejanorey]

PaulBricker, thanks for the help but still didn't seem to work for me..I have tried different variations of the expression and still couldn't get it to work..I don't understand..I've never had this much trouble before..I have used simeple expressions to count records such as =count(*)..Could it be because I'm using an excel document as the table and the queries are feeding of that? Seems a long shot..Any more suggestions..Semper Fi! Tejanorey

"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing

 

[PaulBricker]

Post the expressions you tried and what Report section you tried them in. Getting the data directly from Excel certainly could make a difference but I'm surprised that the expressions wouldn't work because of that. You could try putting two textboxs in the detail section. Set the Control Source to =1, set the Running Sum Property for one to Over All and the other to Over Group. Set the visible property for both to No. Then in the Report Footer you can reference the one that is set to Over All using a textbox with the control source set to
=Textbox1Name
and in the Group Footer you can reference the other in a textbox
=Textbox2Name

That may give you what you need.

Paul

 

[Tejanorey]

PaulBricker, again i salute you for your ongoing determination to help me...the report is (not concerned with the grouped one cause this could be a lengthly forum) it is the "totals report". (Just to beat the dead horse a little more) I just need the expression to reflect the total records displayed on the report..these are the following expressions that I have used:

=count(*)
=dcount(*)
=coung([NAME])
=dcoung([NAME])
=Sum(IIf(Not IsNull([NAME]),1,0))---i used name cause my
primary key.
=Sum(IIf([NAME]= Whatever,1,0))-----i used name cause my
primary key.

...Semper Fi! Tejanorey

"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing

 

[PaulBricker]

What section of the Report were you putting these textboxes in. Group Footer, Report Footer. Also, make sure the Control Name is actually the name of the Bound Field and not something like Text22

Paul

 

[Tejanorey]

PaulBricker, I have tried on the Footer and header and on the report itself with the same result. And I have made sure (by going back and lookin at the Excel/table) that the name of the field is the same name I'm using. Tejanorey

"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing