DCount (2000)

Topic

New Reply

Hi,

I am using DCount on my search form, and I have three criteria search in two tables. But I don’t know how to do it, I put the following code and it isn’t work.

If DCount(“*”, “table1”, “client information”, “[PO]=Forms!frmSearchClient!PO OR [Name]=Forms!frmSearchClient!Name”) = 0 Then
MsgBox “No records matching the criteria you chose, please try it again.”, vbInformation
DoCmd.Close
DoCmd.OpenForm “frmSearchClient”

Also if the criteria that user enter is not finding in the db, I would like clean the crieria and let use try it again. Currently I use reopen the search form and I don’t know if there’s better way to do it.

Regards

Reply | Quote

Replies

First of all, I’d get in the habit of giving your controls names that include a controlprefix. So instead of a control with the name of Name, I’d make it txtName. This will avoid confusion later on, expecially when you are using controlnames that are the same as property names.

Secondly, I’m not sure Dlookup can evaluate the forms references within the quotes like that. To be safe, you might want to do it this way. Notice that I used chr(34) to embed the result of the Name reference surrounded by double-quotes. I’m assuming PO is numeric?

If DCount(“*”, “table1”, “client information”, “[PO]=” & Forms!frmSearchClient!PO & ” OR [Name]=” & chr(34) & Forms!frmSearchClient!Name & chr(34) ) = 0 Then

Reply | Quote

Mark,

Yes, the domain aggregate functions (DSum, DCount etc.) handle references to Forms!… within the quotes of a where-condition argument without problems.

Reply | Quote

Your DCount has 4 arguments, but it supports only 3:
1st argument = field name
2nd argument = table/query name
3rd argument = where-condition

If the table name is table1, use

DCount(“*”, “table1”, “[PO]=Forms!frmSearchClient!PO OR [Name]=Forms!frmSearchClient!Name”)

and if the table name is client information, use

DCount(“*”, “client information”, “[PO]=Forms!frmSearchClient!PO OR [Name]=Forms!frmSearchClient!Name”)

Reply | Quote

Currently I have 1 st arguement and 2 tables. PO is in table 1 and Name is in client information. PO is text not number. I put the following code:

If DCount(“*”, “table1”, “client information”, “[PO]=” & Forms!frmSearchClient!PO & ” OR [Name]=” & Forms!frmSearchClient!TxtSearch4) = 0 Then

I got the error message: Wrong number of arguents or invalid property assignment.

Please help!

Regards

Reply | Quote

If the data you want to count is in two tables, you need to create a query that joins both tables, and contains both fields and do the DCount on that query.

If DCount(“*”, “qryNewQuery”, “[PO]=” & chr(34) & Forms!frmSearchClient!PO & chr(34) & ” OR [Name]=” & chr(34) & Forms!frmSearchClient!TxtSearch4 & chr(34) ) = 0 Then

As both the fields are text fields you need to surround the value being searched for with quotes. chr(34) does that job.

Added later

Or perhaps you need to do two separate Dcounts and add the results.

Reply | Quote

Thanks a lot.

Another question. If the data that user enter is not exit in the db, what’s the best way to delete the entry and let user enter new data?

Regards

Reply | Quote

What do you mean by “not Exit in the DB”?

Reply | Quote

I mean the data that user search is not find in the DB, so I have to delete the entry that user entered to let user type another data again.

Regards

Reply | Quote

This code will set each of the textboxes (you used for the Search ) back to Null (Nothing)

Forms!frmSearchClient!PO = Null
Forms!frmSearchClient!TxtSearch4 =Null

Reply | Quote

Thanks, and it works.

But I got another problem. In client information table have first name & last name. I combine the first name & last name as Full Name in one query and I do DCount on this query. As I try to search the full name, DCount reply not find but actually the name is there. So if I DCount only first name or last name, data is find. If I DCount Full Name, db doesn’t pick any data. Below is the code that I put the query to combine the first name & last name.

Full Name: [First Name ] & ” ” & [Last Name]

And below is the code for DCount:

If DCount(“*”, “qrySearchClient”, “[PO]= Forms!frmSearchClient!PO OR [Full Name]= Forms!frmSearchClient!Name”) = 0 Then

Please Help.

Regards

Reply | Quote

You should be able to do it that way. Are you sure that Name is the name of the text box in which you enter the full name to search for?
What happens if you temporarily remove the part that refers to PO:

If DCount(“*”, “qrySearchClient”, “[Full Name]= Forms!frmSearchClient!Name”) = 0 Then

Reply | Quote

Hi Hans,

Yes I am sure that the Name is the name of the text box in which I enter the full name to search. It have same problem even I remove the PO.

Please review the attached test file will help you know my problem. For example, if you enter test in the name box then click search. DB will tell you there’s no criteria find but you can see there’s a person name call test. If you remove the DCount code from Search button, you are able to search.

Thank you for your all help.

regards.

Reply | Quote

You are now looking for the full name. There is a person whose full name is Test Test. If you enter that in the Name box, the person will be found. If you enter Test, DCount will report that the name was not found since the DCount function uses = so it tests for an exact match.
In fact, you do not need to specify a Where-condition in DCount, since the query qryCDNoAsc already contains the required criteria based on the text boxes on the form. You can simply use

If DCount("*", "qryCDNoAsc") = 0 Then

Try it, I think you’ll find it does exactly what you want, even if you enter only part of a full name.

Reply | Quote

Thanks, Hans. It works.

But I use VB code instead of putting required criteria in the query because it really improve the search speed. So how can I able to enter only part of a full name to search.

Thanks

Regards

Reply | Quote

The same way it is done in qryCDNoAsc: by using Like and the * wildcard.
When you assemble an SQL string in code, you must either double the double quotes within the SQL string, or replace them with single quotes (apostrophes): see post 230,516.

BTW, you can’t use an SQL string as second argument for DCount. You *must* use the name of a table or stored query there.

Reply | Quote

Hi Hans,

I put the following code in my VBA and I don’t where is wrong because I got a error message.

If DCount(“*”, “qrySearchClient”, “[Full Name]= Like ‘*” & chr(34) & Forms!frmSearchClient!Name & chr(34) & “*'”) = 0 Then

Please help.

Regards

Reply | Quote

You’re mixing up single quotes and double quotes. Try

If DCount("*", "qrySearchClient", "[Full Name]= Like '*" & Forms!frmSearchClient!Name & "*'") = 0 Then

Reply | Quote

Hi Hans,

I got Run-time error ’13’ Type mismatch message.

Regards.

Reply | Quote

Oops – should have seen that. Use Like instead of = Like:

If DCount("*", "qrySearchClient", "[Full Name] Like '*" & Forms!frmSearchClient!Name & "*'") = 0 Then

Reply | Quote

Sorry Hans, Same error message: Run-time error ’13’ Type mismatch

Regards

Reply | Quote

Can you attach a stripped down copy of the database with the problem?

Reply | Quote

Here it is.

Regards

Reply | Quote

There is no qrySearchClient or frmSearchClient in that database.

Reply | Quote

Sorry, Hans. The real DB is really big, so I can upload it. That’s why I put the samiliar code on the small test db.

Regards.

Reply | Quote

But I don’t get a type mismatch error there. What should I try to reproduce the error?

Reply | Quote

Thanks, Hans. I fix it.

Reply | Quote