473,836 Members | 2,340 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
12,516 Recognized Expert Moderator MVP
@Seth, perhaps you could attach your database. Preferably in 2003 format so more people can look at it.
Jul 16 '12 #31
Seth Schrock
2,965 Recognized Expert Specialist
I'll see what I can do. The database hold confidential customer information, so posting it would require me to replace all of the company TINs with fake numbers without breaking anything. I'll also see it will save easily in 2003 format. Some default settings for 2010 keep it certain things from converting to older version without a design change.
Jul 16 '12 #32
Seth Schrock
2,965 Recognized Expert Specialist
I just took the plunge and deleted all of the records that were empty and it now works. I don't know what made me think of doing that, but I didn't think that the contents of one record would affect the others. Now I just need to figure out what needs done to keep empty records from being created.

Thanks to everyone that has jumped in here to help and especially to zmbd - I wish I could give you a best answer since you have spent so much time trying to help me.
Jul 16 '12 #33
5,501 Recognized Expert Moderator Expert
Taking the datbase I posted...
Duplicating the first 7 records into new records.
Deleteing the information other than that in the [ACHID] field.... I only get errors in those records. I also get the type mis-match pop and malfromed sql pop-up; however, that is when the [effectivedate] is nulled.
It doesn't cascade thru the query.
Jul 16 '12 #34
Seth Schrock
2,965 Recognized Expert Specialist
For some reason the errors would cascade through some of the records. As you can see from the screen shot in post #10, the EffectiveDate was populated even for the records whose FileCount had the error. A bit of a mystery I guess.
Jul 16 '12 #35
5,501 Recognized Expert Moderator Expert
Post 10 does not show the [ACHCompanyID]; I would guess that it was blank as I can duplicate the errors as shown when I delete the [ACHCompanyID] values for [ACHID]=491,525,561,58 9,620,659,690,7 21.

We nolonger get the error you describe so there we have it... I'm off to a late lunch.
Jul 16 '12 #36
32,584 Recognized Expert Moderator MVP
Seth, I normally use the following procedure when using any kind of complicated filtering (The WhereCriteria - or 3rd - parameter to DCount() is such an example) :
I create a string beforehand to pass to the function. This is very helpful when you have issues as :
  1. You can review the contents of the string before running the function while debugging.
  2. You can include the actual string value in a thread such as this and it will prove very helpful to those trying to assist you.

An example for your code might be :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  3. With Me
  4.     strWhere = "(([ACHID]<=%A) AND ([ACHCompanyID]=%C) AND " & _
  5.                "(Format([EffectiveDate],'yyyymm')='%E'))"
  6.     strWhere = Replace(strWhere, "%A", .ACHID)
  7.     strWhere = Replace(strWhere, "%C", .ACHCompanyID)
  8.     strWhere = Replace(strWhere, "%E", Format(.EffectiveDate, "yyyymm"))
  9. End With
Clearly, you have an extra, unnecessary pair of parentheses around the first two sets of criteria which will cause no issue, but you are also trying to compare a numeric string with a number, which will certainly cause an issue. Also, the SQL string refernce in the VBA will fail. I've resolved these obvious issues in your code in this example.

Let us know if this helps :-)

PS. It seems that a fair bit of water has flowed under the bridge since I last refereshed, so ignore this if it is no help.
Jul 17 '12 #37
Seth Schrock
2,965 Recognized Expert Specialist
I will definitely keep that in mind when I do any complicated stuff in VBA, but I'm using DCount in SQL on this project. I wouldn't say that I have solved my issue here, but I did find a way to make it work. I deleted all of the intermittent empty records that only had the PK field populated. This fixed all of the errors that I was getting on the records that were populated. I don't know why, but since it works, I'm happy.
Jul 17 '12 #38
32,584 Recognized Expert Moderator MVP
Seth Shrock:
I will definitely keep that in mind when I do any complicated stuff in VBA, but I'm using DCount in SQL on this project.
Indeed you are. That's exactly the point Seth. With this approach you can post the exact contents of the SQL-format string you are about to pass to the DCount() function, rather than simply showing the VBA code you THINK ought to result in something usable. In this case the original certainly wouldn't have ;-)

Now you've found something that works you needn't worry about it for this question, but I hope you understand how this can help you get answers much more quickly and easily in similar future situations (I can absolutely guarantee this thread would never have reached 38 posts had you posted this way initially). In many cases just taking this approach will be enough to show you the problem without even needing to post a question.
Jul 17 '12 #39
5,501 Recognized Expert Moderator Expert
The following code uses the IIF to null-proof the SQL,
It has been proofed in the test db and will return a 0 instead of the error (should have done this to begin with however, there was no mention that null values were allowed in the data at the time (post 9):
Expand|Select|Wrap|Line Numbers
  2.    tblACHFiles.ID, 
  3.    tblACHFiles.ACHID, 
  4.    tblACHFiles.ACHCompanyID, 
  5.    tblACHFiles.EffectiveDate, 
  6.    Format([EffectiveDate],'yyyymm') AS YearMonth, 
  7.    IIf(IsNull([ACHID]) Or 
  8.       IsNull([ACHCompanyID]) Or
  9.       IsNull([EffectiveDate]),
  10.          0, 
  11.             DCount("*","[tblACHFiles]","(([ACHID]<= " & [ACHID] & ") 
  12.                AND 
  13.               ([ACHCompanyID] =" & [ACHCompanyID] & "))
  14.                  AND
  15.                (( Format([effectivedate],'yyyymm')=" & Format([effectivedate],'yyyymm') & "))")) AS FileCount
  16. FROM tblACHFiles;
;-) NeoPa... ouch.... I did the best I could in 39 posts... that "Z" in the Avatar isn't the sound of a race engine more like a sleeping turtle, ( Z _ Z ) -zzzzzzzzzzzzzzz };-) However, that string replace is pretty slick... why didn't I think of that before... so obvious now that I see it! I'll be stealing that for my bag of tricks! - Thnx NeoPa!!!!
Jul 17 '12 #40

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: 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: 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: 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: 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: 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.