By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,067 Members | 918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,067 IT Pros & Developers. It's quick & easy.

Dcount statement returns Type Mismatch error

P: 2
no matter what syntax I try I continue to get a Type Mismatch error on my Dcount statement. can anyone please help? Here is my statement:

Expand|Select|Wrap|Line Numbers
  1. Me.txtBHA = _
  2.     DCount("[BHud]", _
  3.            "Consumers", _
  4.            "[CallDate] Between #" & _
  5.                Me.txtStartDate & _
  6.                "# And #" & Me.txtEndDate & _
  7.                "# " And " [BHUD] = 'YES' ")
NOTE: If I remove the second part of the criteria "[BHud] ='YES'" the Dcount will run without error but returns count of all records in the date range and not just those that are populated with YES. My conclusion is that many fields in the column are blank, empty or otherwise Not Null resulting in the inaccurate count. Any advice would be greatly appreciated.
4 Weeks Ago #1

✓ answered by twinnyfo

rcsmith0712,

Welcome to Bytes!

Since the end of your criteria does not consist of additional variables or fields, there is no need to put it in and out of quotation marks, which may be causing the problem.

Try this:

Expand|Select|Wrap|Line Numbers
  1. Me.txtBHA = _
  2.     DCount("[BHud]", _
  3.            "Consumers", _
  4.            "[CallDate] BETWEEN #" & Me.txtStartDate & "# " & _
  5.            "AND #" & Me.txtEndDate & "# " & _
  6.            "AND [BHUD] = 'YES'")
Hope this hepps!

Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
rcsmith0712,

Welcome to Bytes!

Since the end of your criteria does not consist of additional variables or fields, there is no need to put it in and out of quotation marks, which may be causing the problem.

Try this:

Expand|Select|Wrap|Line Numbers
  1. Me.txtBHA = _
  2.     DCount("[BHud]", _
  3.            "Consumers", _
  4.            "[CallDate] BETWEEN #" & Me.txtStartDate & "# " & _
  5.            "AND #" & Me.txtEndDate & "# " & _
  6.            "AND [BHUD] = 'YES'")
Hope this hepps!
4 Weeks Ago #2

cactusdata
Expert 100+
P: 102
Probably, BHUD is a Boolean (Yes/No) field. And you should force a format on the concatenated date expressions, thus:

Expand|Select|Wrap|Line Numbers
  1. Me.txtBHA = _
  2.     DCount("[BHud]", "Consumers", _
  3.         "[CallDate] Between #" & Format(Me.txtStartDate, "yyyy\/mm\/dd") & "# " & _
  4.         "And #" & Format(Me.txtEndDate, "yyyy\/mm\/dd") & "# " & _
  5.         "And [BHUD] = True")
4 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,761
Hi & welcome to Bytes.com.

This is a perfect example of why it really doesn't make good sense to post SQL as VBA (See How to Debug SQL String). As you're a new visitor I'll check the VBA for you and tell you that the code wouldn't even compile as it doesn't fit VBA syntax.

NB. You should Always compile code before asking for help with it.

After your 4th hash (#) you end the string literal and then the text And is there as VBA syntax, followed by another string completely out of context.

If you remove the two quotes around your And then you'll at least have compilable code and a SQL string. "[BHUD] = 'YES' " as a string is fine. As SQL on the other hand, this will only work if [BHUD] is a text field that contains the text "YES". I suspect [BHUD] is actually a Boolean field so you could replace that with "[BHUD]=True" if you want to be messy, Alternatively just "[BHUD]" makes better sense.

PS. I fully support CactusData's comment about formatting date literals when used within SQL of any form. See Literal DateTimes and Their Delimiters (#) for more on that.
4 Weeks Ago #4

P: 2
Twinnyfo, Thank you so much. Your suggestion was spot on.
4 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,482
Glad I could be of service. Let us know if you need any more hepp!
4 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.