Full Version: counting records
UtterAccess Forums > Microsoft® Access > Access Forms
oliverkazaam41
Hi hope someone can help me with this. I am task to make a report which contain the number of records in my database. In my database I have a field that has a field of project_status. The options in the project_status are 1. Approved 2. Under process 3. etc. What I want is to display the data in a form. example
I have a form. in the form there are unbound fields.
Total Number of applications: (this field will display all the records in the database)
Total number Approved: (this field will display all the records in the database that its project status is approved)
so on with the rest of the options.
If I use the count function will I be able to use a condition?
What are the other alternative. please help me.. Thanks in advance...
JayNoelOlimpo
Hi:
Count() or Recordcount
HTH.
HiTechCoach
would use a query that groups on the status and counts the records. The query can have any criteria you need.
The the query can be the datasource for a subform and your done. No coding required.
oliverkazaam41
how do i use dcount and recordcount?
JayNoelOlimpo
Approved = DCount("[FieldNameHere]","[TableOrQueryNameHere]","[Approved]=True")
All = DCount("*","[TableOrQueryNameHere]")
oliverkazaam41
how do i do that if i want it to be displayed in textbox.
Is it possible like this.
approved.value = DCount("[FieldNameHere]","[TableOrQueryNameHere]","[Approved]=True")
JayNoelOlimpo
approved = DCount("[FieldNameHere]","[TableOrQueryNameHere]","[project_status]=1")
ssuming that project_status is a numeric field that holds
1. Approved 2. Under process 3. etc.
and approved is an unbound text box ...
oliverkazaam41
after doing that a message pop out and says "you have cancelled the previous operation" what does it mean?
JayNoelOlimpo
Where do you exactly place that code and could you paste it here?
oliverkazaam41
I made a form with an unbound field and I placed the code in form load in the visual basic editor.. so that when the form loads it will automatically count the records. If you were to do this how will do it?
R. Hicks
It needs to be placed in the Control Source of an unbound txtbox on the form ...
DH
JayNoelOlimpo
in the control source of an unbound text box ...
oliverkazaam41
it shows #error in the txtbox?? how come plsss help me...
oliverkazaam41
can i use a query as my recordsource of the form? and in the domain can i use query??
dcount("[projectstatus]","tblquery","[projectstatus] = 1) thats my statement.. the tblquery is a query. can i use that or I have to use the table itself?
JayNoelOlimpo
Place it in the control source of an unbound text box ...

=dcount("[projectstatus]","tblquery","[projectstatus] = 1")

You seem to have forgotten about "
oliverkazaam41
the result is #error. y is that??
JayNoelOlimpo
Still...
The Query's Name is tblQuery right?
and their is a field name in that Query as projectstatus ...
and projectstatus is a numeric field ...
if not try this one ...
=dcount("[projectstatus]","tblquery","[projectstatus] = '1'")
with an additional ' betwen one ...
oliverkazaam41
tried that already... stil the same. #error. I have tried using the database itself as the domain. it works. but it displays all records whos projectstatus is 1. the query was used to get the records for a specific date range. is there other way do this? Thanks for your answer jay. Hope I can solve this problem with ur help. If you were to do this how will you do it?
JayNoelOlimpo
dcount("[projectstatus]","tblquery","[projectstatus] = '1'")
Just add a criteria under the datefield in the tblQuery ...
[DateFieldHere]
Between Forms![FormNameHere]![StartDate] and Forms![FormNameHere]![EndDate]
HTH.
oliverkazaam41
joel still dont work, but If I just use the table itself it works. the problem is I cant control the date range. Any option you can advice....
abing
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.