473,836 Members | 2,210 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 26714
Seth Schrock
2,965 Recognized Expert Specialist
I'll admit that I don't understand how the code works. Someone else created it for me. Each of the three fields referenced in the DCount command (ACHID, ACHCompanyID, & EffectiveDate) are fields in the table tblACHFiles. The only thing that I can think of is that Line 5 is finding all of the records that have the same yyyymm as the current record.
Jul 16 '12 #21
5,501 Recognized Expert Moderator Expert
Try the following - it worked in my test db without error - MSA-2010:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblACHFiles.ID,
  2.  tblACHFiles.ACHID,
  3.  tblACHFiles.ACHCompanyID,
  4.  tblACHFiles.EffectiveDate,
  5.  Format([EffectiveDate],'yyyymm') AS YearMonth,
  6.  DCount("*",
  7.     "[tblACHFiles]",
  8.     "(([ACHID]<= " & [ACHID] & ") AND ([ACHCompanyID] =" & [ACHCompanyID] & "))
  9.    AND
  10.     (( Format([effectivedate],'yyyymm')=" & Format([effectivedate],'yyyymm') & "))") AS FileCount
  11. FROM tblACHFiles;
I'll attach the test db in a little bit... which version of MS Access?
Jul 16 '12 #22
Seth Schrock
2,965 Recognized Expert Specialist
I'm running Access 2010. Okay, the only thing that I had to change was to remove the tblACHFiles.ID as that isn't a field in my database. ACHID is my primary key. And, it still gives me errors on the same records. Is there a way that I can force it to make the data a date? I'm selecting the date from a date picker, but that doesn't seem to fix it. I just have a feeling that somehow it is the data being entered that makes it work on some records and not on others. The data type in the table is set as Date/Time for the EffectiveDate field.
Jul 16 '12 #23
5,501 Recognized Expert Moderator Expert
Not sure what the error is then...
Attached is the test db I've been working with...

and a screen shot for those that don't want to open and look:

Attached Images
File Type: jpg typmismatch.jpg (54.2 KB, 13037 views)
Jul 16 '12 #24
5,501 Recognized Expert Moderator Expert
whew.... compressed the wrong database... thisone should be right...

doubled check... yep, that's a date field!

{edit}2010-07-17:07h50; Removed the test database... see post 40 for a corrected code that has been null proofed.
Jul 16 '12 #25
Seth Schrock
2,965 Recognized Expert Specialist
I don't get any errors with your database. I'm working on copying the data over to a brand new database and creating the query from scratch to see if there is something wrong with the database.
Jul 16 '12 #26
Seth Schrock
2,965 Recognized Expert Specialist
I'm not sure what the difference is between our databases, but yours works and mine doesn't. I just copied the tables over to a new database and am going to try from scratch.

Edit: Please delete this post. I didn't see the other one posted when I posted this one.
Jul 16 '12 #27
Seth Schrock
2,965 Recognized Expert Specialist
Is it possible that empty records being around the records that have errors could cause the error? I just tried starting from scratch and it didn't work. I then deleted the empty records and then it worked. I just want to see if you think that it was a coincidence before I try it on the live data.
Jul 16 '12 #28
5,501 Recognized Expert Moderator Expert
The function as given in your sql will not handle a null value in the [EffectiveDate] field; however, it should not cascade thru the entire query, it should just effect those [ACHCompanyID] that match the record wherein the null value is located {edit} and [ACHID]<= to that records [ACHID] value {/edit}.
Jul 16 '12 #29
Seth Schrock
2,965 Recognized Expert Specialist
I was meaning that the whole record was blank except for the ACHID (which was generated automatically). Would that throw off other records that did have all of the information?
Jul 16 '12 #30

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: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
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: 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: 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: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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 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.