473,836 Members | 2,171 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);
  14.  
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 26713
Seth Schrock
2,965 Recognized Expert Specialist
I'll have to give that a try. Unfortunately, I have already deleted all of the empty records so I'll have to create some to test it. Thanks again.
Jul 17 '12 #41
NeoPa
32,584 Recognized Expert Moderator MVP
zmbd:
;-) 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
Z, That was not a criticism of your performance by any means (I suspect you know that). I was indicating to Seth that future threads won't be so hard for everyone to deal with if he uses the suggestion I gave about including that helpful extra information in the question. I have no doubt you would have reached a satisfactory resolution earlier than this had you that extra information available from the start. Everybody wins. Nor was I even criticising Seth. Just making a suggestion I believe he will find very beneficial going forward.

As for the laying out of the string, I find the concept so useful that I have a function I designed to support multiple pairs of replacement parameters. Actually, the earlier version was designed before I even knew of the Replace() function. It was so useful a function to have available. When I discovered the latter I updated the former to use it and thus run more efiiciently.
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
  2. 'Using VbBinaryCompare means that case is not ignored.
  3. Public Function MultiReplace(ByRef strMain As String, _
  4.                              ByVal varParam As Variant, _
  5.                              ByVal varReplace As Variant, _
  6.                              ParamArray avarArgs()) As String
  7.     Dim intIdx As Integer
  8.  
  9.     If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
  10.     MultiReplace = Replace(Expression:=strMain, _
  11.                            Find:=Nz(varParam, ""), _
  12.                            Replace:=Nz(varReplace, ""), _
  13.                            Compare:=vbBinaryCompare)
  14.     For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
  15.         MultiReplace = Replace(Expression:=MultiReplace, _
  16.                                Find:=Nz(avarArgs(intIdx), ""), _
  17.                                Replace:=Nz(avarArgs(intIdx + 1), ""), _
  18.                                Compare:=vbBinaryCompare)
  19.     Next intIdx
  20. End Function
NB. This uses vbBinaryCompare so the comparisons are case-sensitive, which is not the default when using Replace().
Jul 18 '12 #42
zmbd
5,501 Recognized Expert Moderator Expert
NeoPa.... you didn't see that :Twisted:
I did warn you that I have a dry sense of humor, no... };-)

Neat function... I'll be tearing it appart to understand it after we get back from vacation! Been coding strings the hardway for way too long and the blinders were on!

-z
Jul 18 '12 #43
NeoPa
32,584 Recognized Expert Moderator MVP
zmbd:
NeoPa.... you didn't see that :Twisted:
I did warn you that I have a dry sense of humor, no... };-)
Of course I saw it Z. Unfortunately, I had no idea what it meant so walked into it somewhat maybe :-D

zmbd:
Neat function... I'll be tearing it appart to understand it after we get back from vacation! Been coding strings the hardway for way too long and the blinders were on!
Thanks. I'm sure you'll work it out in no time. It's pretty straightforward in that it simply repeats the replace function successively for each pair of parameters after the initial start-string. Be warned though, It checks the number of parameters passed and Stops if they don't balance. This is because any invalid call is a design issue and the programmer needs to ensure they don't happen. A normal user should never see that.
Jul 18 '12 #44
Seth Schrock
2,965 Recognized Expert Specialist
After just coming across this problem again, I have finally figured out what the problem is and where the data type mismatch was.

From my first post, I had the following section of code:
Expand|Select|Wrap|Line Numbers
  1. DCount("*"
  2.     ,"[tblACHFiles]"
  3.     ,"[ACHID] <=" & [ACHID] 
  4.          & " AND Format([EffectiveDate],'yyyymm') = " & Format([EffectiveDate],'yyyymm') 
  5.          & " AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount,
By running this through VBA, I was able to more easily figure out where the problem was coming from and it turns out that in the WHERE condition of the DCount function, the middle criteria was producing the error. I remembered that the Format() function returns a string value, so I tried placing single quotes around the result that I was comparing it to.
Expand|Select|Wrap|Line Numbers
  1.  & " AND Format([EffectiveDate],'yyyymm') = '" & Format([EffectiveDate],'yyyymm') & "'"
This fixed it.

What I really don't understand is that it worked for about 2/3 of the records in the same result set. Another way that I could fix it is to delete records that were blank except for the PK field (users would start a record and then change their mind and remove all the data that they entered and just leave the record blank). At least I have come up with a solution that doesn't include removing all blank records everytime I have to run this query.
Jan 8 '14 #45

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

Similar topics

3
3982
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...
0
2258
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...
1
6551
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
3
6514
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...
6
4624
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
19
21580
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,...
3
3529
Kermit
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();
1
1643
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.
2
3960
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...
0
9656
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,...
0
10527
jinu1996
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...
1
10571
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,...
0
10241
tracyyun
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...
1
7773
isladogs
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...
0
6973
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();...
0
5642
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...
1
4441
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
2
4001
muto222
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.