471,355 Members | 1,621 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Syntax for code convert fieldnames to values in field

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

Jan 31 '07 #1
1 1874
<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

Feb 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Little PussyCat | last post: by
4 posts views Thread by Patrick Arkins | last post: by
3 posts views Thread by Beren | last post: by
6 posts views Thread by Miles Keaton | last post: by
1 post views Thread by meltedown | last post: by
7 posts views Thread by Trickynick1001 | last post: by
2 posts views Thread by john | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.