Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: DCount (2000)

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount (2000)

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: DCount (2000)

    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
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (2000)

    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")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (2000)

    Mark,

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

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DCount (2000)

    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.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DCount (2000)

    What do you mean by "not Exit in the DB"?
    Regards
    John



  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DCount (2000)

    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
    Regards
    John



  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (2000)

    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

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (2000)

    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
    <code>
    If DCount("*", "qryCDNoAsc") = 0 Then
    </code>
    Try it, I think you'll find it does exactly what you want, even if you enter only part of a full name.

  15. #15
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (2000)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •