473,883 Members | 1,652 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 #1
44 26736
5,501 Recognized Expert Moderator Expert
Do I understand correctly, that this is code taken from a VBA module or form?

IIF this is correct, then the issue will more than likely be in how you are obtaining the comparison information. Thus, I would start with double checking that the comparison data is in the correct data-type.

Let me explain my thought there...

(because I think clearer with the code in-front of me I'll just pull a snip from your post):
Expand|Select|Wrap|Line Numbers
  1. (...) " AND Format([EffectiveDate],'yyyymm') =" 
  2. & Format( [EffectiveDate] ,'yyyymm')(...)
in line two, the underlined portion... from where is that information being taken?

If taken directly from a form text box control, then often it will be interpreted as a string (esp if delimited (one thousand being 1,000.00 or 1.000,00 depending on region). This is one reason I usually do not use the form control values directly in VBA, been burned here many times, instead I will define a variable of the correct data-type and then assign the value from the control to it, even going so-far as to wrap the control in one of the conversion functions such as
when assigning to a variable dimensioned as an integer to insure that I get an integer value from the control. If you're taking this from an inputbox some how, then you might want to consider CLong() for your [EffectiveDate].

Jul 12 '12 #2
Seth Schrock
2,965 Recognized Expert Specialist
My code is in a query. The part that gets me is that in the same query execution, some of the records work and some don't. When I get to work, I'll send a print screen of the results.
Jul 12 '12 #3
32,584 Recognized Expert Moderator MVP
Seth, 286 posts and I haven't linked you to Before Posting (VBA or SQL) Code yet? I doubt it somehow ;-) Please follow the guidelines there when posting your questions, as otherwise you just make the whole situation so much more complicated to deal with.
Jul 12 '12 #4
Seth Schrock
2,965 Recognized Expert Specialist
Sorry, I thought I was following those guidelines. Is the edited version better?
Jul 12 '12 #5
Seth Schrock
2,965 Recognized Expert Specialist
Update: I've done some more testing and got the following results. I changed the following line of code (lines 2 -7 of OP):
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]","[ACHID] <=" 
  2.          & [ACHID] 
  3.          & " AND Format([EffectiveDate],'yyyymm') =" 
  4.          & Format([EffectiveDate],'yyyymm') 
  5.          & " AND [ACHCompanyID] = " 
  6.          & [ACHCompanyID]) AS FileCount,
to the following:
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]")
just to make sure that there were no problems there. It worked fine. I then added some of the criteria and had
Expand|Select|Wrap|Line Numbers
  1. DCount("*",[tblACHFiles]","[ACHID] <=" & [ACHID])
Again this worked fine. I then tried the following:
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]","[ACHID] <=" 
  2. & [ACHID] 
  3. & " AND Format([EffectiveDate],'yyyymm') =" 
  4. & Format([EffectiveDate],'yyyymm'))
This time, I got the error. So I'm thinking that there is a problem in that field for some of the records. I tried re-entering the date on some of the records that had errored out, but no change.
Jul 12 '12 #6
5,501 Recognized Expert Moderator Expert
Duh... That's what I get for reading code at 1am... I should have seen this to start with:

Could the SQL parser be getting lost in the criteria? If so then there might be a type mismatch between the [ACHID] and [EffectiveDate] as the parser gets confused... are all of the [ACHID] fields full of data? Are all of the [EffectiveDate} fields full of data?

In anycase, try enclosing the entire criteria inbetween "()" and "()" between the AND operators to help the parser group the criteria. I know that in a WHERE statement that this will often solve such an issue.

Sorry for being so turttle and mule about the question!

Jul 12 '12 #7
32,584 Recognized Expert Moderator MVP
Sorry, I thought I was following those guidelines. Is the edited version better?
I'm confused. It certainly seems like you've made an effort, but I'm looking at post #1 and it hasn't been edited, so maybe post #6 is what you're referring to. It's nicely formatted, but it seems to use DCount rather than a SELECT as used in post #1. DCount is VBA but SELECT is SQL, so there seems to be some confusion here.

All that said, it seems clear you're doing your best and that's all we ever need really :-)
Jul 12 '12 #8
Seth Schrock
2,965 Recognized Expert Specialist
@zmbd I'm not sure if the SQL parser could be getting lost in the criteria. Both ACHID and EffectiveDate are populated in all records (I believe that I have thse as required fields). I'll try adding the "()" and let you know the results.

@NeoPa I changed the first post to make the code a little more neat. I'm using the DCount in SQL. The DCount in post #1 is in line 2. Post #6 is looking at just that field.

Always tell me if I'm not posting correctly. I want to make sure my posts are formatted as well as possible, both in code arrangement and wording.
Jul 13 '12 #9
Seth Schrock
2,965 Recognized Expert Specialist
Okay, I just tried the following for the DCount portion of the SQL and still no go.

Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]",("[ACHID] <=") 
  2. & [ACHID] 
  3. & (" AND Format([EffectiveDate],'yyyymm') =") 
  4. & (Format([EffectiveDate],'yyyymm')) 
  5. & (" AND [ACHCompanyID] = ") 
  6. & [ACHCompanyID]) AS FileCount
Attached is a screen shot of the results.
Attached Files
File Type: pdf Database Screen Shot.pdf (16.3 KB, 305 views)
Jul 13 '12 #10

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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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: 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: 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: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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 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.