Code to take fieldnames and convert to records

[dean.brunne]

Hi,

I have a query with 104 fields of a time series where the fieldnames
are dates such as 04/10/06. I need to take those field names and have
them as a record in one field called Date and the data in the
respective fields in a field called Average Selling Price. I have
created a loop to go through the fieldnames and save as a string
variable. The value is then used in an SQL statement. My problem is
that the string variable does not seem to store the value of the
fieldname and the query looks for an inputted parameter value. I am
new to all this so I figure it is a datatype issue. I have attached
the code below. I welcome any suggestions as to how to fix this.

Thanks,

Dean

Sub CrosstabASP()

Dim db As Database
Dim qdfDate As QueryDef
Dim SQL As String
Dim fldDate As Field
Dim strDate As Variant


Set db = CurrentDb()
Set qdfDate = db.QueryDefs("qryAdd_PC_ASP")

For Each fldDate In qdfDate.Fields

strDate = fldDate.Name

If strDate <> "Banner" Then
If strDate <> "Product" Then
If strDate <> "Planning Customer" Then

SQL = "INSERT INTO tblASP_convert ( [Planning Customer], [Date],
[Average Selling Price] ) " & _
"SELECT qryAdd_PC_ASP.[Planning Customer], strDate AS [Date],
qryAdd_PC_ASP.strDate AS [Incremental Units]" & _
"FROM qryAdd_PC_ASP;"

DoCmd.RunSQL SQL

Else

End If
End If
End If

Next

Exit Sub

End Sub

 

[Damian S]

Hi Dean,

There is a problem with your SQL String for inserting the dates you find in
the querydef.

You need to build the SQL String like this:
SQL = "INSERT INTO tblASP_convert ( [Planning Customer], [Date],
[Average Selling Price] ) " & _
"SELECT qryAdd_PC_ASP.[Planning Customer], #" & strDate & "# AS [Date],
qryAdd_PC_ASP.[" & strDate & "] AS [Incremental Units]" & _
"FROM qryAdd_PC_ASP;"

Also, you might like to consider using the isdate() function to check for
your field name being a valid date to make your code neater... One last
thing, using a reserved word (date) for a field name is asking for grief down
the track...

Hope this helps.

Damian.

 

[dean.brunne]

Thanks Damian!

Hi Dean,

There is a problem with your SQL String for inserting the dates you find in
the querydef.

You need to build the SQL String like this:
SQL = "INSERT INTO tblASP_convert ( [Planning Customer], [Date],
[Average Selling Price] ) " & _
"SELECT qryAdd_PC_ASP.[Planning Customer], #" & strDate & "# AS [Date],
qryAdd_PC_ASP.[" & strDate & "] AS [Incremental Units]" & _
"FROM qryAdd_PC_ASP;"

Also, you might like to consider using the isdate() function to check for
your field name being a valid date to make your code neater... One last
thing, using a reserved word (date) for a field name is asking for grief down
the track...

Hope this helps.

Damian.

Hi,

I have a query with 104 fields of a time series where the fieldnames
are dates such as 04/10/06. I need to take those field names and have
them as a record in one field called Date and the data in the
respective fields in a field called Average Selling Price. I have
created a loop to go through the fieldnames and save as a string
variable. The value is then used in an SQL statement. My problem is
that the string variable does not seem to store the value of the
fieldname and the query looks for an inputted parameter value. I am
new to all this so I figure it is a datatype issue. I have attached
the code below. I welcome any suggestions as to how to fix this.

Thanks,

Dean

Sub CrosstabASP()

Dim db As Database
Dim qdfDate As QueryDef
Dim SQL As String
Dim fldDate As Field
Dim strDate As Variant


Set db = CurrentDb()
Set qdfDate = db.QueryDefs("qryAdd_PC_ASP")

For Each fldDate In qdfDate.Fields

strDate = fldDate.Name

If strDate <> "Banner" Then
If strDate <> "Product" Then
If strDate <> "Planning Customer" Then

SQL = "INSERT INTO tblASP_convert ( [Planning Customer], [Date],
[Average Selling Price] ) " & _
"SELECT qryAdd_PC_ASP.[Planning Customer], strDate AS [Date],
qryAdd_PC_ASP.strDate AS [Incremental Units]" & _
"FROM qryAdd_PC_ASP;"

DoCmd.RunSQL SQL

Else

End If
End If
End If

Next

Exit Sub

End Sub