473,789 Members | 2,624 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Why DISTINCT causes error 3464

6 New Member
Hi

I have a problem with SELECT DISTINCT query.
I'm trying to solve why it causes error 3464.

Guess that problem might be cause by one field.
The source table has text(17) field from which I need to extract 'YYMMDD' date and convert it to date/time format. I convert only from not null and 17char strings

I use following function in SQL statement

IIf(Len(Trim(D. NRPZ))=17 and not D.NRPZ is null,DateValue( Format(Mid(D.NR PZ,8,6),"0000-00-00")))

Source for this query is a subquery that joins two external DBF tables (about 500000 records).

1.
What i have found that top 6991 do not cause the problem but 6992 do.
I have made copy of these 6992 record and put into local table.
The original query works fine on this data set

2. (with subquery limited to top 6992 as source)
Query with only one field with the above function - works fine
Query with 14 fields + one with the above function - works fine
Query with 15 fields + one with the above function - DOES NOT WORK !!!
I have used 15th field instead of 14th to see if this caused the problem but not - works fine
I've added 15th field that is existing field wrapped in MID function - works fine
I've added 16th field that is existing text field joined with & - works fine
I add 17th field numeric, that is new field from source subquery, - DOES NOT WORK !!!
Query with all fields except the one with the above function - works fine

As soon as I remove DISTINCT the query works fine.
I've tried to debug the problem but as I wrote in point 1. local copy of 6992 records work fine for the query.

The error message itself does not help anyway to solve the problem.
Making it one more reason to hate this M$ product.

My question are:
Can anybody explain what exactly could couse the problem?
Is there known limit to use of DISTINCT in Access?
Dec 4 '09 #1
1 2674
missinglinq
3,532 Recognized Expert Specialist
The problem is that the use of DISTINCT in Access produces a recordset that cannot be updated, which is what you're actually trying to do here.
Dec 4 '09 #2

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

Similar topics

0
3617
by: BobG | last post by:
-- The issue This following query works fine on Toad and PowerBuilder yet when it is run on SQLPlus I get: "ORA-03113: end-of-file on communication channel" When I remove the distinct from the first line (capitalized below) it runs fine on SQLPlus. Is there some flag I need to set in SQLPlus to get this to work properly?
2
10821
by: Chicken Kebab Abdullah | last post by:
Does anyone know why I get the error 3464 Data type mismatch from the following code. I have a form with a combo(to choose a consumable) and 2 list boxes on it. list on left is all printers and list on right shows the ones currently compatible with the consumable chosen in the combo at the top.
1
4917
by: Will | last post by:
In the following code I am attempting to open a query and edit the lastMaintenance field for a particular equipment number. My code looks like this: Dim db As Database Dim rs As Recordset Dim SQL As String Set db = DBEngine.Workspaces(0).Databases(0) Set rs = db.OpenRecordset("SELECT * From qryPlannedMaintenance WHERE
5
5040
by: ddd | last post by:
Hi, I am having problems with using the DrawToDC of the MSHTML.iHTMLElementRender in a VB.net application. For some reason I am getting a "catastrophic error". I am basing the code on c# examples, and I am not sure what exactly I am doing wrong. From the C# posts it seems that the drawtodc has a bug and you need to redifine it, so i followed their advice and specified an interface as below:
3
1252
by: Venkat | last post by:
This is the piece of code in Early Binding and works as expected. --------------------------------------------------------------------------- using ControlLicenseService LicenseInfoClient lic =new ControlLicenseService LicenseInfoClient(); lic.Initialize("sk26901CODA904O",10300); if (lic.IsSuccess ) { MessageBox.Show("Success"); }
5
1652
by: Eric Sabine | last post by:
the second I add <appSettings> </appSettings> to my web.config file (just after the <system.web>), my web application will no longer run. I get an error that reads: Error while tyring to run projectL Unable to start debugging on the web
2
4852
by: Frank van Vugt | last post by:
Hi, Not exactly a showstopper, but I noticed this behaviour: db=# create table f1 (id int, value int); CREATE TABLE db=# insert into f1 select 1 as id, null; INSERT 25456306 1
1
2949
by: Angus | last post by:
Why moving a function from protected to public area in class causes error C2556 - overloaded function differs only by return type I have a protected function called GetState like this: enum EState GetState(void) const throw(); If I move this function from protected: to public: I get this compile error:
1
2538
by: Bamboz | last post by:
Dears, I have a table named Tasks that Contains all the Information about my employee , i am making a Form to open a report based on Employee Name, Tasks done, Date. but i'm getting error with this code can u help me figure out where is my error plz Private Sub Command20_Click() Dim sFilter As String sFilter = "" If Me.Name.ListIndex <> -1 Then
0
9666
marktang
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...
0
10408
Oralloy
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...
1
10139
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
9020
agi2029
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...
0
6769
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
5417
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...
0
5551
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4092
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
3700
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.