count records in search from (1 Viewer)

mohamedmatter

Registered User.
Local time
Today, 00:13
Joined
Oct 25, 2015
Messages
112
I want to count this in form search by school search
1- count all record by school search
2- count device in Lab a as example
3- count Lcd device in Lab a
4- count datashow as kind sony
i attach sample SchoolLabsDB
many thanks for this group
 

Attachments

  • SchoolLabsDB.accdb
    500 KB · Views: 35

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
I think you will need to create a base query for your SQL join as you have for the search control.

Then you can use DCount for your each of your criteria but append the current search criteria as well.?
 

mohamedmatter

Registered User.
Local time
Today, 00:13
Joined
Oct 25, 2015
Messages
112
any help for this problem
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
What do you not understand about what I have suggested.?
 

mohamedmatter

Registered User.
Local time
Today, 00:13
Joined
Oct 25, 2015
Messages
112
So help me to help you.

What do you not understand from what I have suggested as a way to get what you are asking.?
I can not do what you ask me. So I attached the file to an example of me. Thank you for following up on my problem. So I did not understand your previous comment"
think you will need to create a base query for your SQL join as you have for the search control.

Then you can use DCount for your each of your criteria but append the current search criteria as well.?"
 

JHB

Have been here a while
Local time
Today, 08:13
Joined
Jun 17, 2012
Messages
7,732
You could do it link in the attached database!
I'll leave it for you to make the last count.
 

Attachments

  • SchoolLabsDB-2.accdb
    524 KB · Views: 29

essaytee

Need a good one-liner.
Local time
Today, 18:13
Joined
Oct 20, 2008
Messages
512
From my immediate window, from your db:
Code:
? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
 3
Enter the above code in the control source of the relevant text box, image below:


This should give you an idea how to configure your other counts.

Also check out this link for further.
 

Attachments

  • 2019-04-28_17-59-06-dcount-control-source.jpg
    2019-04-28_17-59-06-dcount-control-source.jpg
    19.9 KB · Views: 209

essaytee

Need a good one-liner.
Local time
Today, 18:13
Joined
Oct 20, 2008
Messages
512
@JHB - you hadn't posted when I started. It took me 15 mins just to download db, check form, confirm dcount, but actually took me longer to compose and submit post.
 

isladogs

MVP / VIP
Local time
Today, 07:13
Joined
Jan 14, 2017
Messages
18,186
I think you will need to add an = sign at the start of the control source expression. Omitting it will cause a #Name error. Instead, use this as your control source

Code:
=DCount("Lab","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
Or
Code:
=DCount("*,"DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
 

essaytee

Need a good one-liner.
Local time
Today, 18:13
Joined
Oct 20, 2008
Messages
512
Damn, you are right. I obviously did not test the last test.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
JHB has taken a different approach.

For completeness and to try and get you to understand what my approach on it was...

A DCount cannot specify joins, only the WHERE criteria without the WHERE keyword.?
So create a base query that has the join the same as the form recordsource join.
Break your recordsource sql down to three parts.
strSelect. strWhere and strOrder so you can concatenate them to give you the same sql string as you have now. This then gives you the ability to use the strWhere string/clause elsewhere.

Now use strWhere string/clause and concatenate the other specific criteria for each of the controls.

eg:
StrWhere would currently be "[SchoolName]like '*" & Me.Text0.Text & "*' "

**Count all record in school search
This would result in a where criteria of
Code:
=DCount("*","basequery",strWhere)
no additional criteria required
**For All device in Lab A
Code:
=DCount("*","basequery",strWhere & " AND Lab='Lab-A'")

and so on.

So my database would look like

Code:
Private Sub Text0_Change()
Dim SQL As String, strSelect As String, strWhere As String, strOrder As String
strSelect = "SELECT SchoolNameTbl.ID, SchoolNameTbl.SchoolName, DevicesTbl.Lab, DevicesTbl.device, DevicesTbl.Kind " _
& "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID "
strWhere = "[SchoolName] like '*" & Me.Text0.Text & "*' "
strOrder = " ORDER BY SchoolNameTbl.SchoolName"
SQL = strSelect & "WHERE " & strWhere & strOrder
Me.FrmHlp.Form.RecordSource = SQL
Me.FrmHlp.Form.Text13 = DCount("*", "basequery", strWhere & " AND Lab='Lab-A'")
Me.FrmHlp.Form.Text11 = DCount("*", "basequery", strWhere)
End Sub




I can not do what you ask me. So I attached the file to an example of me. Thank you for following up on my problem. So I did not understand your previous comment"
think you will need to create a base query for your SQL join as you have for the search control.

Then you can use DCount for your each of your criteria but append the current search criteria as well.?"
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
I read the request for the counts as for the records selected in the form?, not the whole table. That is why I thought making a base query was required.?

From my immediate window, from your db:
Code:
? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
 3
Enter the above code in the control source of the relevant text box, image below:


This should give you an idea how to configure your other counts.

Also check out this link for further.
 

essaytee

Need a good one-liner.
Local time
Today, 18:13
Joined
Oct 20, 2008
Messages
512
I read the request for the counts as for the records selected in the form?, not the whole table. That is why I thought making a base query was required.?

I'm not really sure what the OP wanted help on, as he didn't offer any further explanation to your earlier questions. I merely assumed dcount, provided an example and was waiting for a response from the OP, which may have elicited further information.

Admittedly, I didn't check the actual recordsource of the form, but there appeared to be as many records in the table as there were in the form listing.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
Admittedly, I didn't check the actual recordsource of the form, but there appeared to be as many records in the table as there were in the form listing.

There is to start with, as the school control is empty.
I'd helped the O/P previously in another thread with getting the sql correct for the search, so was aware of what they were trying to do with the form.

I must admit however, that it confused the hell out of me to start with, overlaying frmHelp on frmSearch, when I am sure one form would work just as well. :)
I did try =Dcount as source with the statements, but could not get it correct as it returned 0 for LabA each time

Code:
=DCount("*","basequery","[SchoolName] like '*" & [Text0].[Text] & "*' " & " AND [Lab]='Lab-A'")
 

mohamedmatter

Registered User.
Local time
Today, 00:13
Joined
Oct 25, 2015
Messages
112
From my immediate window, from your db:
Code:
? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
 3
Enter the above code in the control source of the relevant text box, image below:


This should give you an idea how to configure your other counts.

Also check out this link for further.
Thank you very much for helping you and cutting off part of your time to help members
after test your modification i found error in dcount
for example when i search to school-d the counter to "Count Lcd Device in Lab A"= 3 but no lcd in lab a in school-D
 

mohamedmatter

Registered User.
Local time
Today, 00:13
Joined
Oct 25, 2015
Messages
112
JHB has taken a different approach.

For completeness and to try and get you to understand what my approach on it was...

A DCount cannot specify joins, only the WHERE criteria without the WHERE keyword.?
So create a base query that has the join the same as the form recordsource join.
Break your recordsource sql down to three parts.
strSelect. strWhere and strOrder so you can concatenate them to give you the same sql string as you have now. This then gives you the ability to use the strWhere string/clause elsewhere.

Now use strWhere string/clause and concatenate the other specific criteria for each of the controls.

eg:
StrWhere would currently be "[SchoolName]like '*" & Me.Text0.Text & "*' "

**Count all record in school search
This would result in a where criteria of
Code:
=DCount("*","basequery",strWhere)
no additional criteria required
**For All device in Lab A
Code:
=DCount("*","basequery",strWhere & " AND Lab='Lab-A'")

and so on.

So my database would look like

Code:
Private Sub Text0_Change()
Dim SQL As String, strSelect As String, strWhere As String, strOrder As String
strSelect = "SELECT SchoolNameTbl.ID, SchoolNameTbl.SchoolName, DevicesTbl.Lab, DevicesTbl.device, DevicesTbl.Kind " _
& "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID "
strWhere = "[SchoolName] like '*" & Me.Text0.Text & "*' "
strOrder = " ORDER BY SchoolNameTbl.SchoolName"
SQL = strSelect & "WHERE " & strWhere & strOrder
Me.FrmHlp.Form.RecordSource = SQL
Me.FrmHlp.Form.Text13 = DCount("*", "basequery", strWhere & " AND Lab='Lab-A'")
Me.FrmHlp.Form.Text11 = DCount("*", "basequery", strWhere)
End Sub

I think your opinion is correct. I wanted to create a query. I hope you will help me solve this problem on the file attached many thanks

What is "basequery".
and how create basequery
And how it is linked to the search form .I want to easy way to arrive solve
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
Set each variable to 0 before the recordset is opened.

Thank you very much for helping you and cutting off part of your time to help members
after test your modification i found error in dcount
for example when i search to school-d the counter to "Count Lcd Device in Lab A"= 3 but no lcd in lab a in school-D
 

Users who are viewing this thread

Top Bottom