Using Variables in an append query

[SpankYou]

I have an append query which I am trying to implement as part of a loop, so I don't have to write 10 or more different Append Queries and load them as necessary. Now I have tried this several way, and I'm not having much success. My first attempt is below....

''put into variables
AppendSQL = "INSERT INTO TblMiscShare ( CERTID, ShareTypeID, PricePerShare, NoOfSharesPurchased, NoOfSharesOffered, ShareStartNo, ShareEndNo, DatePaid, SHID, DateOffered, DateIssued, TotalSharesToDate ) " & _
'"SELECT " & AppendCertID & ", " & AppendShareTypeID & ", " & AppendPricePerShare & ", " & AppendNoOfSharesPurchased & ", " & AppendNoOfSharesOffered & ", " & AppendShareStartNo & ", " & AppendShareEndNo & ", " & AppendDatePaid & ", " & AppendSHID & ", " & AppendDateOffered & ", " & AppendDateIssued & ", " & AppendTotalSharesToDate & ";"

DoCmd.RunSQL (AppendSQL)

Now this nearly works, only problem is that when it puts in the dates, it puts it in almost a time format. e.g. 1/3/2004 comes out as 00:00:29. I have messed around with formats and all sorts but could not get it working. All other data is entered correctly. The other method I tried, is to produce an Append Query, which uses variables from my code. I'm pretty sure this is possible, but I keep getting the error "13 : Type mismatch". this error occurs on this line "Set prm1 = qd.Parameters(0)". I'm not sure whether it's the code or the SQL so i've posted both. If you have any posible ideas, please let me know, this is my second day on this problem.

Thanks in advance

Sam


CODE

Dim qd As DAO.QueryDef
Dim db As DAO.Database

Dim prm1 As Parameter
Dim prm2 As Parameter
Dim prm3 As Parameter
Dim prm4 As Parameter
Dim prm5 As Parameter
Dim prm6 As Parameter
Dim prm7 As Parameter
Dim prm8 As Parameter
Dim prm9 As Parameter
Dim prm10 As Parameter
Dim prm11 As Parameter
Dim prm12 As Parameter

Set db = CurrentDb
'open your query and set the query definitions so that you can use
'your variables as parameters in the query
Set qd = db.QueryDefs("QryMiscAppendTest")

Set prm1 = qd.Parameters(0)
Set prm2 = qd.Parameters(1)
Set prm3 = qd.Parameters(2)
Set prm4 = qd.Parameters(3)
Set prm5 = qd.Parameters(4)
Set prm6 = qd.Parameters(5)
Set prm7 = qd.Parameters(6)
Set prm8 = qd.Parameters(7)
Set prm9 = qd.Parameters(8)
Set prm10 = qd.Parameters(9)
Set prm11 = qd.Parameters(10)
Set prm12 = qd.Parameters(11)

prm1.Value = AppendCertID
prm2.Value = AppendShareTypeID
prm3.Value = AppendPricePerShare
prm4.Value = AppendNoOfSharesPurchased
prm5.Value = AppendNoOfSharesOffered
prm6.Value = AppendShareStartNo
prm7.Value = AppendShareEndNo
prm8.Value = AppendDatePaid
prm9.Value = AppendSHID
prm10.Value = AppendDateOffered
prm11.Value = AppendDateIssued
prm12.Value = AppendTotalSharesToDate

qd.Execute ' if it's an Append query, execute it rather than open

Set prm1 = Nothing
Set prm2 = Nothing
Set prm3 = Nothing
Set prm4 = Nothing
Set prm5 = Nothing
Set prm6 = Nothing
Set prm7 = Nothing
Set prm8 = Nothing
Set prm9 = Nothing
Set prm10 = Nothing
Set prm11 = Nothing
Set prm12 = Nothing
Set qd = Nothing
Set db = Nothing

SQL APPEND QUERY

INSERT INTO TblMiscShare ( CERTID, ShareTypeID, PricePerShare, NoOfSharesPurchased, NoOfSharesOffered, ShareStartNo, ShareEndNo, DatePaid, SHID, DateOffered, DateIssued, TotalSharesToDate )
SELECT [AppendCertID], [AppendShareTypeID], [AppendPricePerShare], [AppendNoOfSharesPurchased], [AppendNoOfSharesOffered], [AppendShareStartNo], [AppendShareEndNo], [AppendDatePaid], [AppendSHID], [AppendDateOffered], [AppendDateIssued], [AppendTotalSharesToDate];


"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SpankYou]

Ok so I never managed to figure out the second option, and even came across a third option, but I got the first option working, why were the dates messed up you ask? Because there was no darn #'s before and after the dates, having never written an Append query using SQL and dates, it didn't really cross my mind, especially as they aren't shown in the SQL view of a query builder. But wooohooooooo I deserve a Star!

Cheers

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer