Better formatting for SQL Insert Into Query

[Phred]

The following query works fine. Now I am trying to clean up the formatting because its difficult to read or find anything. This is far better than what I had. But I know there is more.

stSQL = "INSERT INTO dbo_Property" & _
     "( County, kPIN, TownshipNO, TownshipNM, CycleID, CycleName, CountyClass, PropertyDesc, LevelofAssess, Type, OpenFile, KattyAssigned, Paddress1, Paddress2, Pcity, PState, Pzip5, TaxCode)" _
    & "VALUES ('" & stCnty & "','" _
    & stKPIN & "','" _
    & stTwnNo & "','" _
    & stSelTwn & "','" _
    & stCycleID & "','" _
    & stCycleNM & "','" _
    & stSelClas & "','" _
    & stDesc & "','" _
    & stLoa & "','" _
    & stTypeNM & "','" _
    & stOpenFile & "','" _
    & stKattAs & "','" _
    & stPaddress1 & "','" _
    & stPaddress2 & "','" _
    & stPCity & "','" _
    & stPState & "','" _
    & PZip5 & "','" _
    & stTaxCode & "');"

Somewhere I saw an example where you were able to place the "Field" and then the "Value" (Variable) on one line. So each line contained the field name and then the corresponding variable and it was so easy to read.

Field "County" Value "stCounty"

I cant remember where to find it. I have several queries like this. Can anyone help. Readability is the goal. If there are other suggestions on making it readable I would be interested in hearing them. But my main goal now is to get each field and variable on one line.



Thanks

Phred

[pbaldy]

I can't think of a way to get an append query that way. An update query is structured that way:

UPDATE TableName
SET Field1 = 123, Field2 = 'ABC'
WHERE...

Appending records with a recordset would also be structured that way:

rs.AddNew
rs!Field1 = 123
rs!Fiedl2 = "ABC"
rs.Update

[Phred]

That's it! So it would look like this:

stSQL = "INSERT INTO dbo_Property" & _
Set County = '" & stCnty & "' _
kPIN = '" & stKPIN & "' _
TownshipNo = '" & stTwnNo & "' _

Continues through all fields. There would be no WHERE statement.

Does this look correct?

Thanks, Phred

[pbaldy]

No. Like I said, I don't think you can do that with an append query. An update query edits an existing record(s), an append query creates new records.

[Phred]

Rats. I thought it would work for all action queries. My bad luck, but thanks.
I reverted to the query at the top as it works fine. Can the field names County, kPIN, TownshipNM be broken down vertically?

County
kPIN
TownshipNM

It would be easier to read if both were in vertical stacks. I am not sure how the syntax would look.

[pbaldy]

Sure, you just can't get them side-by-side with the value. You could switch to a recordset and get that look.

[Phred]

Double rats. I tried to split the fields each to one line. VBA spits out a warning that there are too many One-Line-Splits so I guess there is a limit to it.

But thanks for your help on the above. I will mark this solved.

Phred

[pbaldy]

No problem. It's less efficient by a micron, but you can do it this way instead of with line continuation and not run into the limit:

...
strSQL = strSQL & stCycleNM & "','"
strSQL = strSQL & stSelClas & "','"
...