473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dcount statement returns Type Mismatch error

5 Nibble
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.
Jun 8 '20 #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!

5 2137
twinnyfo
3,653 Expert Mod 2GB
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!
Jun 8 '20 #2
cactusdata
214 Expert 128KB
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")
Jun 8 '20 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jun 9 '20 #4
rcsmith0712
5 Nibble
Twinnyfo, Thank you so much. Your suggestion was spot on.
Jun 9 '20 #5
twinnyfo
3,653 Expert Mod 2GB
Glad I could be of service. Let us know if you need any more hepp!
Jun 10 '20 #6

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

Similar topics

4
by: Mike | last post by:
I am getting a type mismatch error when I do a bulk insert. ---Begin Error Msg--- Server: Msg 4864, Level 16, State 1, Line 1 Bulk insert data conversion error (type mismatch) for row 1, column...
3
by: Laurel | last post by:
this is driving me crazy. i need to use a form control as a criteria in a select query, and the control's value is set depending upon what a user selects in an option group on the form. the query...
5
by: TD | last post by:
I created a query in Access 2000 that runs perfectly. I then copied the "sql" version of the same query and set it equal the variable "sql" in the code below. When I run the code below I get an...
8
by: aland | last post by:
Hi, I'm hoping someone can help me with this code. I'm getting a 'Type mismatch' error, and I'm not sure why. The SQL works fine in SQL view, so I'm not sure if that's the problem or not. This is...
3
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
4
by: herman404 | last post by:
Hi everyone, I am trying to write a function that will be called from another function. It will do a save as on a word document, and the document name will be passed via a parameter. I keep...
1
by: lalbee | last post by:
I am trying to use the Dcount function but receive a data type mismatch error, can someone help me determine the cause? Count: DCount("","qry-final everhome results"," <'0' ") Thanks!
5
by: kjworm | last post by:
Hello Everyone, I have been fighting with a type mismatch error for many hours today and I can't seem to find what the problem is. Hopefully it is more than a missing apostrophe! I have isolated...
7
by: Mike | last post by:
Type Mismatch error I recieve a type mismatch error on the following line of code which is based on a specific date. It does NOT break on all records only "some". The dates for the records...
7
by: Rmoeller | last post by:
Hi everyone and thanks in advance for any help on this. I have some VBA code that I would like to use to loop through different criteria in a SQL statement. I actually pulled the SQL statement seen...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.