Append queries

[daz321]

i would like to do an append query and would like to include both data from an existing table and variable assigned in my VB code. Is this possible, as i am getting an error message saying:
"Number of query values and destination fields aren't the same"

my code is:

Private Sub cmdFullRun_Click()
Dim msg As String
Dim Letter As String
Dim Data As String
Dim LetterType As String
Dim AccessApp As Access.Application
Dim DBPath As String
Dim DropMacro As String

Dim todays_date As Date

todays_date = Date

LetterType = Left(lblLetterType.Caption, 3)

DropMacro = Left(LetterType, 3)

DBPath = "C:\My Documents\darryl.mdb"
Set AccessApp = New Access.Application
With AccessApp
.OpenCurrentDatabase DBPath
.DoCmd.RunSQL "INSERT INTO letter_history ( letter_type, account_no, arrears_amount, todays_date) SELECT letter_type, account_no, arrears_amount FROM LetterSelection"
.DoCmd.RunMacro ("warnings off&quot
.DoCmd.RunSQL ("Select * into output from LetterSelection&quot
.DoCmd.RunMacro ("Export&quot
.DoCmd.RunSQL ("drop table output&quot
.DoCmd.RunMacro ("warnings on&quot
.CloseCurrentDatabase

End With

'Letter = "C:\darryl\uni work\project\" & LetterType & "Letter.doc"

'Data = "C:\my documents\darryl.txt"

'msg = FireUpWord(Letter, _
Data)
'If msg <> &quot;&quot; Then MsgBox msg
End Sub

 

[jantie78]

There is something wrong with your SQL statement :
&quot;INSERT INTO letter_history ( letter_type, account_no, arrears_amount, todays_date) SELECT letter_type, account_no, arrears_amount FROM LetterSelection&quot;

This should be something like this :
&quot;INSERT INTO letter_history ( letter_type, account_no, arrears_amount, todays_date) SELECT letter_type, account_no, arrears_amount,now FROM LetterSelection&quot;

The Now() function returns the current date/time.

Greetz,
Jan

 

[CCLINT]

I would do it just a little different in order to optimize the query:

&quot;INSERT INTO letter_history (letter_type, account_no, arrears_amount, todays_date) SELECT letter_type, account_no, arrears_amount,&quot; & Now() & &quot; AS todays_date FROM LetterSelection&quot;

Putting the Now() function outside it only needs to be calculated once and will keep the provider from having to do the extra work for each record.

You may want to change the Now() function for the Date() function.