Syntax for code convert fieldnames to values in field

[dean.brunne]

Hi,

In the code below I scroll throught the firldnames of a query ignoring
the first three then converting the remaining fields to first: the
fieldnames as a record in a field called Product (e.g- Fieldname is
BEER, convert to BEER as the record value in a field called product)
Second: The values of the field to be populated in a field called
Baseline Units. I capture the fieldname as a string but when the code
below tries to populate the Products field it pastes the field values
instead of the field name string of BEER.

I assume it is either in the syntax of the SQL statement or the string
type is incorrect. Please help.

Thanks and regards,

Dean

Sub Convert()

Dim db As Database
Dim qryProd As QueryDef
Dim SQL As String
Dim fldProd As Field
Dim strProd As String
Dim strProd1 As Variant
Set db = CurrentDb()
Set qryProd = db.QueryDefs("Query1")

For Each fldProd In qryProd.Fields

strProd = fldProd.Name

If strProd <"Planning Customer" Then
If strProd <"Business Plan Id" Then
If strProd <"Week Number" Then

DoCmd.SetWarnings False
SQL = "INSERT INTO calc_Output ( [Planning Customer], [Business Plan
Id], [Week Number], Product,[Baseline Units] ) " & _
"SELECT Query1.[Planning Customer], Query1.[Business Plan Id], Query1.
[Week Number], " & strProd & " AS Product, Query1.[" & strProd & "] AS
[Baseline Units]" & _
"FROM Query1;"

DoCmd.RunSQL SQL

Else

End If
End If
End If

Next

DoCmd.SetWarnings True
MsgBox ("Update Complete")

Exit Sub

End Sub

[pietlinden]

<SNIP>
For Each fldProd In qryProd.Fields

strProd = fldProd.Name

If strProd <"Planning Customer" Then
If strProd <"Business Plan Id" Then
If strProd <"Week Number" Then

DoCmd.SetWarnings False

SQL = "INSERT INTO calc_Output ( [Planning Customer], [Business Plan
Id], [Week Number], Product,[Baseline Units] ) " & _
"SELECT Query1.[Planning Customer], Query1.[Business Plan Id], Query1.
[Week Number], '" & strProd & "' AS Product, Query1.[" & strProd & "]
AS [Baseline Units]" & _
"FROM Query1;"

DoCmd.RunSQL SQL.

Easy problem. You need to delimit string literals with single quotes
in SQL.

eg
....cQUOTE & strProd & cQUOTE...
it's one of those things that the query engine/grid whatever does for
you behind the scenes, so if you build your own SQL, you have to put
it in.

Testing the first 3 fields... you _could_ use something like
For intCounter = 2 to qdf.fields.count-1
'do your thing
next intCounter

which would skip the first 3 fields in the querydef... if you want to
test it, try something like

for intCounter = 0 to qdf.Fields.Count-1
debug.print qdf.fields(intCounter).Name
next intCounter