Results 1 to 11 of 11
  1. #1
    DetrieZ is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    17

    DCOUNT From Two Form Field values

    My form has a combobox [cboRecap] with a list of 4 values Match, No Match, Questionable and Other
    Also has a Text Box [txtScore] that is populated with a with a numeric value 1 through 5
    On [cboRecap]_afterUpdate, I am trying to populate [txtRecap] with the count of records in my [ZIMatchedAccounts] table where the [DMReview] column = [cboRecap] and [ZI_C_MATCH_SCORE] = [txtScore]

    This throws a Run-Time error '2471': The expression you entered as a query parameter produced this error: 'Match'



    Code:
    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = " & Me!cboRecap & " And ZI_C_MATCH_SCORE =" & Me!txtScore)
    This one throws Run-Time error 13" Type mismatch
    Code:
    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = '" & Me!cboRecap & "'" And "ZI_C_MATCH_SCORE = '" & Me!txtScore & "'")
    This one does not throw an error but it populates [txtRecap] with a zero
    Code:
    Me.txtRecap = DCount("*", "[ZIMatchedAccounts]", "[ZI_C_MATCH_SCORE]" = Me.txtScore & "DMReview" = Me!cboRecap)
    

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Text needs to be surrounded by ' unless it has a ' in the string, in which case I think it is """.
    Dates need #
    Numbers do not need anything.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Also, AND needs to be within the literal string. Use dot instead of ! when referencing controls. Try:

    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = '" & Me.cboRecap & "' AND ZI_C_MATCH_SCORE = " & Me.txtScore)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Tip:
    Put the criteria into a string variable and debug.print it. That will show your errors. When you get it correct, the use that variable in the function.
    Also helps in that you can copy that output and paste here if you still cannot see the issue.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by June7 View Post
    Also, AND needs to be within the literal string. Use dot instead of ! when referencing controls. Try:

    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = '" & Me.cboRecap & "' AND ZI_C_MATCH_SCORE = " & Me.txtScore)
    Got controls mixed up
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    DetrieZ is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    17
    Thanks Welshgasman

    I think I did what you suggested but I still get an Run-Time error 13 type mismatch

    Code:
    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = '" & Me!cboRecap & "'" And [ZI_C_MATCH_SCORE] = Me!txtScore)

  7. #7
    DetrieZ is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    17
    Ok.. That worked but, my eyes are crossing trying to figure out single and double quotes.
    If I follow the open and close quotes, the single quotes throw me off. They seem to be orphaned. Or, is this not the proper way to look at it?

    Code:
    "*"
    "ZIMatchedAccounts"
    "DMReview = '"
    "' AND ZI_C_MATCH_SCORE = "

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Second tip:
    Put code within code blocks, using the # icon. That allows you to see ' much easier.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is the rowsource of your combo and which column is the bound one? Probably you are looking at text in the selection but the combo value is an autonumber id?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Quote Originally Posted by DetrieZ View Post
    Ok.. That worked but, my eyes are crossing trying to figure out single and double quotes.
    If I follow the open and close quotes, the single quotes throw me off. They seem to be orphaned. Or, is this not the proper way to look at it?

    Code:
    "*"
    "ZIMatchedAccounts"
    "DMReview = '"
    "' AND ZI_C_MATCH_SCORE = "
    Wrong way to look at. The apostrophes are on each side of concatenated parameter, which you have omitted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by DetrieZ View Post
    Thanks Welshgasman

    I think I did what you suggested but I still get an Run-Time error 13 type mismatch

    Code:
    Me.txtRecap = DCount("*", "ZIMatchedAccounts", "DMReview = '" & Me!cboRecap & "'" And [ZI_C_MATCH_SCORE] = Me!txtScore)
    No you did not.
    Dim strCriteria AS String
    StrCriteria = DCount( etc
    Debug.print StrCriteria

    Edit:
    Sorry, not sure what I was thinking when I posted the above, only half is correct.
    Put the criteria into the string variable and debug.print that
    Code:
    Dim strCriteria AS String
    StrCriteria = "DMReview = '" & Me!cboRecap & "'" And [ZI_C_MATCH_SCORE] = Me!txtScore
    Debug.print StrCriteria
    the use that (when correct) in the Domain function
    Code:
    Me.txtRecap = DCount("*", "ZIMatchedAccounts",strCriteria)
    Last edited by Welshgasman; 11-11-2021 at 02:19 AM. Reason: Correct my post
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-02-2016, 08:07 PM
  2. Replies: 11
    Last Post: 02-13-2016, 12:40 PM
  3. Replies: 18
    Last Post: 03-26-2015, 07:26 AM
  4. DCount to count unique values
    By nlkehl in forum Queries
    Replies: 3
    Last Post: 06-09-2014, 10:46 AM
  5. DCOUNT Find Duplicate values
    By whojstall11 in forum Programming
    Replies: 4
    Last Post: 04-04-2014, 02:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums