473,889 Members | 1,981 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Why am I getting a data type mismatch in some of the query results?

Seth Schrock
2,965 Recognized Expert Specialist
I have the following piece of code for my query. For some reason some of the records showup with the FileCount field having an error. When I click in the field it says "Data type mismatch in criteria expression." I assume that it is talking about the criteria in the DCount() function. The three fields that are referenced in the criteria portion of the DCount() function are as follows:
Expand|Select|Wrap|Line Numbers
  1. ACHID           AutoNumber
  2. EffectiveDate   Date 
  3. ACHCompanyID    Number
The following is the code.

Expand|Select|Wrap|Line Numbers
  1. SELECT   tblACHFiles.ACHID, 
  2.          DCount("*","[tblACHFiles]","[ACHID] <=" 
  3.          & [ACHID] 
  4.          & " AND Format([EffectiveDate],'yyyymm') =" 
  5.          & Format([EffectiveDate],'yyyymm') 
  6.          & " AND [ACHCompanyID] = " 
  7.          & [ACHCompanyID]) AS FileCount, 
  8.          Format([EffectiveDate],'yyyymm') AS YearMonth, 
  9.          tblACHFiles.EffectiveDate
  10. FROM     tblACHFiles
  11. WHERE    InvoiceID = 105
  12. ORDER BY Month(EffectiveDate), 
  13.          Day(EffectiveDate);
What I don't understand is that about half of the records work and the other half don't. I have checked to make sure that the data in the table is the correct data type. I don't know where to go from here.
Jul 11 '12
44 26741
5,501 Recognized Expert Moderator Expert
We'll I'm home with the twins :) today so this may not be my best work... I tend to get distracted, they're 2-1/2 and require a lot of love ;-)

I took a look, it seems as though the parenthesise are not quite what I was thinking so I tried a hand at sticking them in the places I thought they should go.

SO... I've left this on one line so that you should be able to Copy&Paste... normally I'd break this for ease of reading:
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]", "(([ACHID] <="  & [ACHID]  & ") AND ( Format([EffectiveDate],'yyyymm') ="  & Format([EffectiveDate],'yyyymm')  & ") AND ([ACHCompanyID] = " & [ACHCompanyID] &"))") AS FileCount 
I think I have this correct; however, I don't have the luxary of building a test database today... if it doesn't work or tosses an error at you I applogise in advance...

Hey, kids, don't pull the dog's tail... stop that... Daddy's typingg,, =dkaslre ahhhh save from the twins ( gota-luv-em)

Jul 13 '12 #11
Seth Schrock
2,965 Recognized Expert Specialist
There weren't any errors in the code, but I still get the same results. If you need more information, just ask. I've done all the troubleshooting that I know of and given the results here, so I don't know what other information might be useful to you.

Tells the twins hi for me :)
Jul 13 '12 #12
5,501 Recognized Expert Moderator Expert
darn... :(

I think that you're were on the correct troubleshooting path by breaking the code down.

What I think I would do now is take each of your 'yyyymm' formatted [EffectiveDate] within the DCOUNT() and place them in there own fields. Then do a comparison between the fields.... clear as mud?

- OK, said Hi to the twins for you. Twins were in the process of destroying the bathroom... I have 9yr DD that forgets to close the door... then the twins... Bathrooms + Twins = Natural Disaster of Biblical proportions! I must now go fish the Egyptians out of the tub, put the potty back on the hole, and mop up what left of the Nile on the Bathroom floor.
Jul 13 '12 #13
Seth Schrock
2,965 Recognized Expert Specialist
I think I know what you mean by placing them in their own fields (example line 8 of OP), but I'm not sure of how to compare them differently then they are being compare right now. Do you mean
Expand|Select|Wrap|Line Numbers
  1. "YearMonth=" & YearMonth
(using the example mentioned earlier)?
Jul 13 '12 #14
5,501 Recognized Expert Moderator Expert
I think I might have taken the wrong fork, I've been focusing on the DCOUNT issue...

so the following may be a red-herring:

I took a look at pdf you posted in 10: the order of the fields is:

I double checked your OP SQL and the two jogged my memory about a situation where I was using results from one calculated field within another... and would occasionally get these strange errors. Solved it by accident when I rebuilt the query from scratch... that was like... 10 years ago so I had forgotten about the situation.

Switch the order so that we now have:

Ok... why I think it worked in my query is that I suspect that MSA reads left to right. So by having the source fields first, then the results from any calculated fields used in subsequent fields, then that information is available for the following fields.

and you tie your shoes by making an "X" with the string...

SO we now have the root data first: [ACHID];[EffectiveDate];
Then the first calculted field: [YearMonth];
and then finally the field that uses all of the afor mentioned information: [FileCount]

The field order might still be a red-herring.

and yet I like Pickled Herring:

I am solidly convinced and I'm still thinking that it has to do with the AND comparision having numeric-type in the [ACHID] and date-type in [EffectiveDate].
If so, then we need to group so that we have
"(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))"
that way we're comparing the boolean results between the two data-types and the entire comparison is enclosed into itself.

Just finished rebuilding Rome, need to go harvest a field or two to feed the kids... maybe I can get them to finish tying their shoes... (ok... now the rabbit runs around the tree and jumps thru the hole... no, thru the hole, no... hold the tree... the bunny runs... OK, start back at the "X"...)

Jul 13 '12 #15
Seth Schrock
2,965 Recognized Expert Specialist
Here is the thread from which I got the code that I'm using here: How do I count the number of entries in a certain month This might help you understand what I'm doing with the code. The code that I'm working on right now just focuses on the main fields are broken.

I've changed the order of the fields and got the same result. I'm working on getting the criteria changed to match the order you have. When you say "(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))", do you mean that there would only be one instance of the AND operator or just that there would be an AND operator separating the numbers and the dates?

Sounds like your house is a zoo:)
Jul 13 '12 #16
5,501 Recognized Expert Moderator Expert
I'll look at the link here in a moment or two... if I can get them to stay down for a nap :)

"(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))"

What I'm after is that on the left you have all of your logic comparing the numerics in that group ANDs and ORs etc...
Same thing on the right, in that case the dates... so you'd have:
Expand|Select|Wrap|Line Numbers
  1. (
  2. (numeric1 = numeric2 AND numeric3 = numeric4....) 
  3. AND
  4. (date1 = date2 AND date3 = date4.....)
  5. )
This way, all of the booooooolean hocus-pocus resolves in each group within the same data-type then the final comparison is between the boooooolean results (always reminds me of ghosts and and magic spells).

Zoos don't let you drop off Two year olds... they make you take them with you when you leave... sigh :)

Let's just say... my life is not boring on my days off... just hope I don't turn them into psycotic... oh, they're two... too late. :)
It doesn't last forever... I just keep reminding myself of that... it helps... along with a Rosary or two or three...
Jul 13 '12 #17
32,584 Recognized Expert Moderator MVP
I won't interrupt the flow Seth, but just popped in to say all is fine. I like your attitude.

I'm still confused, because the site shows me when a post was last edited - and according to that post #1 hasn't been since it was first submitted. That said, I doubt it's remotely important that I'm confused as all seems to be going along fine with you, zmbd and the zoo.

Good luck to all of you. It sounds like mayhem :-D
Jul 14 '12 #18
Seth Schrock
2,965 Recognized Expert Specialist
Okay, I've tried the following, and there is no change in the results.

Expand|Select|Wrap|Line Numbers
  1. DCount("*",
  2. "[tblACHFiles]", 
  3. "((([ACHID] <="  & [ACHID]  & ") 
  4. AND ([ACHCompanyID] = " & [ACHCompanyID] &")) 
  5. AND ( Format([EffectiveDate],'yyyymm') ="  & 
  6. Format([EffectiveDate],'yyyymm')  & ") )") AS FileCount
Jul 16 '12 #19
5,501 Recognized Expert Moderator Expert
I'm confused...

Let's just look at the criteria string

Because I'm getting lost in this code I'm going to break the string apart at the concatenation points:
Expand|Select|Wrap|Line Numbers
  1. "((( [ACHID] < = " &
  2. [ACHID] &
  3. ") AND ([ACHCompanyID] = " &
  4. [ACHCompanyID] &
  5. ")) AND (Format([EffectiveDate],'yyyymm') = " &
  6. Format([EffectiveDate],'yyyymm') &
  7. "))"
Ok, now, Lines 2, 4, and 6 have the evaluated data (data that either comes from the user, a form, or the table) and the remaining lines have your fixed criteria strings.

Lets use 1/1/2012 as a date... look at line 5 and 6... once evaluated we get (starting just after the AND):
Expand|Select|Wrap|Line Numbers
  1. (Format([EffectiveDate],'yyyymm') = 201201
I'm lost with this.
Jul 16 '12 #20

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

Similar topics

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 results should return information on either a single employee or all employees. the problem that i have is that if i type in "*" (but without quotes) in the QBE criteria, it works fine. however, if i use Iif() to determine whether or not there...
by: news.paradise.net.nz | last post by:
I have been developing access databases for over 5 years. I have a large database and I have struck this problem with it before but can find nothing in help or online. Access 2000 I have a query that will run fine without any criteria but as soon as I add any criteria it gives a "Data type mismatch" error. As soon as I remove any criteria it runs perfectly. I know this query is based on another query but I have other processes based on...
by: ArcadeJr | last post by:
Good morning all! I have been getting a Run-time Error message #3464 - Data Type mismatch in criteria expression. While trying to run a query. I have a database where the field Asset_Number was once a type Number, but I had to change it to a type Text due to I needed to have two zeros at the beginning of the Asset Number (EX: 001234.) The rease I am writing is that now when I run from My Query Menu a
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 criteria, the Mid function returns the values when I run the query. So if one of the values is a "t" (no quotes), can I not ask to isolate that record by putting "t" as a criteria? Nope - error, error. If I put it within the expression itself...
by: christianlott1 | last post by:
Taken (and modded) from http://www.codeguru.com/vb/gen/vb_misc/tips/ article.php/c13137 'RESULTS' table: REF_STRING - TEXT 250 TEST_STRING - TEXT 250 MATCH_VALU - SINGLE FIXED 2 DECIMAL PLACES (I also have three additional fields but they are not used in this
by: Lysander | last post by:
I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the date is more than 10 months out. The query runs fine, but I when I put the criteria of >10 I get 'Data Type mismatch' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date,...
by: Kermit | last post by:
Hi. Here's the problem. I use C# application to access MSAccess database. I want to get id's of all records containing some phrase and order results by date (a field in the table). here's the code: OleDbCommand cmd = new OleDbCommand("select id, endDate from audits where objectId ='" + (int)data + "' order by endDate DESC", conn); try { conn.Open();
by: fd1 | last post by:
Hi All, I'm getting Data Type Mismatch error when I run the following query: SELECT tbl1.Definition, tbl2.Group FROM tbl1, tbl2 WHERE ((Mid$(!,6)=Mid$(!,9))); Both "Definition" and "Description" are of type text.
by: tmoon3 | last post by:
Hello, This must be a simple mistake, but for some reason I cannot seem to get around it. I am simply trying to create a report of all employees that have a date filled in in a training column in an access DB. I give the users a drop down list to choose employee type, division, and term status and based on their choices I create the selection criteria for the report. This code works without the ForkTruckClass > '#1-01-1900#' (the below...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.