By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Error Message: Number of query values and destination fields are not the same.

100+
P: 153
I'm getting this error message: Number of query values and destination fields are not the same.

This is the code:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblDateDifference (  DateDifference )
  2. SELECT tblContracts.EndDate As EndDate, 
  3. IIf([Date()] > EndDate, DaysToCompletion(EndDate), DaysCompleted(EndDate)) AS DateDifference,
  4. FROM tblContracts;
Can someone please tell me why I'm getting this error message? PLEASE?!?! lol
Dec 7 '06 #1
Share this Question
Share on Google+
9 Replies


MSeda
Expert 100+
P: 159
Delete tblContracts.EndDate As EndDate, from the select statement. You only want the value returned from the iif statement to be inserted, correct.
Dec 7 '06 #2

100+
P: 153
No the select statement is necessary...then the IIf statements put the selected date into a module based on their values.
Dec 7 '06 #3

MSeda
Expert 100+
P: 159
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
Dec 7 '06 #4

100+
P: 153
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 DaysToCompletion() As Long

'Opening tblContracts as recSet1
Dim con1 As ADODB.Connection
Dim recSet1 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
recSet1.Open "tblContracts", 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
'"tblWhateverYourTableNameIs", 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.MoveNext
Loop

'Clearing recSet1 and Con1 (Connection1)
recSet1.Close
con1.Close
Set con1 = Nothing
Set recSet1 = Nothing

End Function


Cheers,
Kosmös
Dec 7 '06 #5

MSeda
Expert 100+
P: 159
You are inserting into one field, DateDifference. however you select two fields to insert, tblContracts.EndDate 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.EndDate As EndDate, is assigning the temporary name of EndDate to TblContracts.EndDate, and is not neccessary for the iif statement to function.
Dec 7 '06 #6

NeoPa
Expert Mod 15k+
P: 31,276
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.
Dec 8 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblDateDifference (  DateDifference )
  2. SELECT DaysToCompletion(EndDate)  AS DateDifference
  3. FROM tblContracts
  4. WHERE Date() > tblContracts.EndDate;
  5.  
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblDateDifference (  DateDifference )
  2.  SELECT DaysCompleted(EndDate) AS DateDifference
  3.  FROM tblContracts
  4. WHERE Date() <= tblContracts.EndDate;
  5.  
Mary
Dec 9 '06 #8

100+
P: 153
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
Dec 15 '06 #9

100+
P: 153
By the way if anyone was following the code I put in to open the form...
you can just do:

Sub openForm()
DoCmd.openForm "frmCustomer"
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....this code assumes you are only working in the current database
Dec 15 '06 #10

Post your reply

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