Problem with an SQL Query in VB 1

[tstrike]

I have a append query that I am trying to create in vb.

When I run it I get this Error

Too few parameters, Expected 5


Here is the code I am using

strSQL = "INSERT INTO Upload ( JID, DocDate, Type, CompanyCode, PostingDate, " _
& "Period, Curancy, ReferenceDocument, HeaderText, Account, Amount, " _
& "DebitCreditIndicator, ShortText ) " _
& "SELECT 1 as JID, (Format(Date(), ""mmddyyyy"&quot) AS DocDate," _
& """ZU"" AS Type, ""1000"" AS CompanyCode, " _
& "[Forms]![Posting Date/period]![Posting Date] AS PostingDate, " _
& "[Forms]![Posting Date/period]![Period] AS Period, ""USD"" AS Curancy, " _
& """90-CompaqCapital"" AS ReferenceDoc, " _
& """HP Sched "" & [Forms]![Posting Date/period]![Schedule#] & ""-"" & " _
& "[Forms]![Posting Date/period]![Month] AS HeaderText, " _
& """152100"" AS Account, [Forms]![Posting Date/period]![Amount] AS Amount, " _
& """C"" AS DebitCreditIndicator, " _
& """HP Schedule "" & [Forms]![Posting Date/period]![Schedule#] & "" for "" & " _
& "[Forms]![Posting Date/period]![Month] AS ShortText"

The form that is runing the code is the one that has all of the data I am pulling into the table.

I also would like to make the JID feild a veriable that the sql calls in.

 

[beetee]

General purpose SQL script debugging technique:

Add a statement to your code

debug.print strSQL

copy the generated sql from the debug window (press ^G to open the debug window), create a new query, switch to SQL view, paste in the query, and work with it until it's correct.

If you have trouble nailing down the location of the error, simplify the query until it works, then add parts back in until it doesn't.

 

[tstrike]

I think my problem is the Feilds I am pulling from the form. Is there another way to get the form data into the sql?

I also tried using Me instead of the form name, but I get the same message.

 

[GHolden]

I think it's because you're trying to write the actual text '[Forms]![Posting Date/period]![Posting Date]' to the field rather than the contents of that control...

You need to have...

& "'" & [Forms]![Posting Date/period]![Posting Date] & "' AS PostingDate, " _

etc..

put MsgBox strSQL after you have built the string to see if you have the right values.



There are two ways to write error-free programs; only the third one works.

 

[tEkHEd]

beetee

gave u a star.. the Debug.Print is very useful...

 

[tstrike]

GHolden

Thank you for the advice. It works great.

The other question I have is the JID needs to be a veriable. I am only able to put 899 line in a transaction going into our Accounting system. The Feild JID is what we use to break up the transactions. Can I use a vb value, such as the value of X in the SQL code. See line 4 of my code above. Right now it says 1, I need to make it a veriable in the queries to come.

 

[GHolden]

You can make it a variable and append it as per the form references...

eg.
Dim intID as Integer

intID = 4

strSQL = "SELECT * FROM tblTable WHERE fldID = " & intID

would select the records from table tblTable where field fldID was equal to intID (4 in this case)



There are two ways to write error-free programs; only the third one works.

 

[tstrike]

Thank you all for your help. Lin 4 of my code now looks like this:

& "SELECT '" & JID & "' as JID, (Format(Date(), ""mmddyyyy"&quot) AS DocDate," _


JID is the veriable that I set up to be used.

Again thank you all for your help & advise.