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

[Kosmos]

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

[MSeda]

Delete tblContracts.EndDate As EndDate, from the select statement. You only want the value returned from the iif statement to be inserted, correct.

[Kosmos]

No the select statement is necessary...then the IIf statements put the selected date into a module based on their values.

[MSeda]

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

[Kosmos]

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

[MSeda]

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.

[NeoPa]

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]

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

[Kosmos]

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

[Kosmos]

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