I'm getting this error message: Number of query values and destination fields are not the same.
This is the code: - INSERT INTO tblDateDifference ( DateDifference )
-
SELECT tblContracts.EndDate As EndDate,
-
IIf([Date()] > EndDate, DaysToCompletion(EndDate), DaysCompleted(EndDate)) AS DateDifference,
-
FROM tblContracts;
Can someone please tell me why I'm getting this error message? PLEASE?!?! lol
9 3358 MSeda 159
Recognized Expert New Member
Delete tblContracts.En dDate As EndDate, from the select statement. You only want the value returned from the iif statement to be inserted, correct.
No the select statement is necessary...the n the IIf statements put the selected date into a module based on their values.
MSeda 159
Recognized Expert New Member
You only have one destination field listed, DateDifference, in the INSERT INTO statement. Anything listed in the SELECT statement SQL will try to insert into the feilds designated in the INSERT INTO clause. In your SELECT statement you have two values DateEnd and DateDifference, hence the error message. You will either have to add DateEnd into your list of destination fields or drop it from the SELECT statement.
If you are trying to insert datedifference only for certain enddates you need to incorporate enddate into a WHERE clause
DateDifference is what it's being assigned to I thought? I thought I was saying Insert into blah blah
Select blah blah as EndDate (I thought this was assigning it with a temporary name) then saying iif(so and so....blah blah come up with value) then assign the object I'm inserting into AS DateDifference
Is this incorrect?
Well anyways I wrote the code using a different method which worked just as well...but I'd still like to know about the proper way to write that and if I didn't understand it correctly if you happen to view this again. Thanks MVeda :)
The code I used is posted below in case anyone else needs help with this...it's code for a module:
Public Function DaysToCompletio n() As Long
'Opening tblContracts as recSet1
Dim con1 As ADODB.Connectio n
Dim recSet1 As ADODB.Recordset
Set con1 = CurrentProject. Connection
Set recSet1 = New ADODB.Recordset
recSet1.Open "tblContrac ts", con1
'Declaring UntilCompletion as the amount of days until completion
Dim UntilCompletion As Long
'Looping until EOF (until the last record for EndDate in tblContracts... so
'someone else would have declared recSet1.Open
'"tblWhateverYo urTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields ("fieldname" ))
Do Until recSet1.EOF
' End Date must be in quotes or will not work
UntilCompletion = DateDiff("d", Date, recSet1.Fields( "EndDate"))
Debug.Print UntilCompletion
recSet1.MoveNex t
Loop
'Clearing recSet1 and Con1 (Connection1)
recSet1.Close
con1.Close
Set con1 = Nothing
Set recSet1 = Nothing
End Function
Cheers,
Kosmös
MSeda 159
Recognized Expert New Member
You are inserting into one field, DateDifference. however you select two fields to insert, tblContracts.En dDate As EndDate AND the result of the iif statement as DateDifference.
It is the "AS dateDifference" that assigns the temporary name to the result of iif.
the part that I suggested you drop, tblContracts.En dDate As EndDate, is assigning the temporary name of EndDate to TblContracts.En dDate, and is not neccessary for the iif statement to function.
NeoPa 32,567
Recognized Expert Moderator MVP
Kosmos,
MSeda is right on the button here.
He has perfectly correctly (and quite clearly) answered your original question.
If you read it again carefully you should understand what was wrong with your SQL code.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Kosmos
As both NeoPa and MSeda have explained you have been given the answer. To explain it further You cannot do the two actions in the one INSERT statement. You will have to separate them out into two separate queries. Please find below the code as it should be written. -
INSERT INTO tblDateDifference ( DateDifference )
-
SELECT DaysToCompletion(EndDate) AS DateDifference
-
FROM tblContracts
-
WHERE Date() > tblContracts.EndDate;
-
-
INSERT INTO tblDateDifference ( DateDifference )
-
SELECT DaysCompleted(EndDate) AS DateDifference
-
FROM tblContracts
-
WHERE Date() <= tblContracts.EndDate;
-
Mary
Thanks...yeah I just hadn't understood exactly how things worked...the books availaible for my company were not very helpful in starting off...but I found a very good one recently that allows me to do more module based coding which I prefer more
By the way if anyone was following the code I put in to open the form...
you can just do:
Sub openForm()
DoCmd.openForm "frmCustome r"
End Sub
But it's better to open a form the other way because it's just better to practice it that way in case you are opening forms in other databases....th is code assumes you are only working in the current database
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Robert McEuen |
last post by:
Using Access 97, Windows XP
I'm receiving a Numeric Field Overflow error during text import that I
did not receive before I split my database. Another thread I found
suggested that the cause of this error might be due to columns on the
text file being in a different order than fields in the destination
table, but they're the same in my case.
I'm thinking a workaround might be to import to a temp table in the
front-end, run an append...
|
by: ralamo |
last post by:
When i execute the following insert query, the above mentioned error
is coming. Anything wrong in the query?
INSERT INTO ECN_1 (Old_SbPartNo, Old_PartDesc, Old_ManPartNo,
Old_Manuf, Old_Vendor, Old_RefDesi, Old_Qty,
New_SbPartNo, New_PartDesc, New_ManPartNo,New_Manuf, New_Vendor,
New_RefDesi, New_Qty)
select * from (select SB0014.SbPartNo, SB0014.partDesc,
SB0014.mANpARTnO, SB0014.manufacturer,
|
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
|
by: windandwaves |
last post by:
Hi Gurus
Does anyone know how I set the error trapping to option 2 in visual basic.
I know that you can go to tools, options and then choose on unhandled errors
only, but is there a VB command that I can use instead?
Cheers
Nicolaas
|
by: Ecohouse |
last post by:
I have a main form with two subforms. The first subform has the child
link to the main form identity key.
subform1 - Master Field: SK
Child Field: TrainingMasterSK
The second subform has a master-child link to the first subform.
subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
| |
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is...
|
by: Shokoth |
last post by:
Hi
I have been trying to run this code, and what it is doing is inserting records into a table called tblRequestByService. the values that it is inserting are taken from the form itslef (They are normal text boxes). every time i click the button to do that, i get the folling error message:
"Number Of Query Values and Destination Fields are not the same". I have been having similar problems throughout. i know that the number of fields that i...
|
by: cypriot |
last post by:
Hi. I am developing an application program in java. I use MsAccess for keeping data.
I had a problem with sql insert method.
public void AddPatient()
{
String no= numberinput.getText();
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge.
I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
|
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: 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: 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: 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
| |