Gary_Mc_Mahon
Board Regular
- Joined
- Apr 30, 2003
- Messages
- 87
Hi,
Can anyone help with this one. This is attached to a button on a form if that helps at all.
When I get the line about opening the query, I get a run time error.
Private Sub Command1_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim strSQL As String
Dim CP As String
Dim From As String
Set db = CurrentDb
CP = InputBox("Please Enter the charge period to update, YYYYMM")
From = "From qry_UNION_" + CP
strSQL = "INSERT INTO tbl_RR19_Charge_Bucket_200211_2 ( [Billing Sys Code], [Invoice Number], [Opco Code], [Product Group 1 Desc], [Product Group 2 Desc], [Product Group 3 Desc], [Product Group 4 Desc], [Product Group 1 ID], [Product Group 4 ID], [Charge Period], [Reporting Period], [Invoice Date], [SAP Account ID], [Customer Name], [Segment ID], [Sub Segment ID], [Total Rev Post Disc & Adj (USD)] )" & _
"SELECT [qry_UNION_" + CP + "].[Billing Sys Code], [qry_UNION_" + CP + "].[Invoice Number], [qry_UNION_" + CP + "].[Opco Code], [qry_UNION_" + CP + "].[Product Group 1 Desc], [qry_UNION_" + CP + "].[Product Group 2 Desc], [qry_UNION_" + CP + "].[Product Group 3 Desc], [qry_UNION_" + CP + "].[Product Group 4 Desc], [qry_UNION_" + CP + "].[Product Group 1 ID], [qry_UNION_" + CP + "].[Product Group 4 ID], [qry_UNION_" + CP + "].[Charge Period], [qry_UNION_" + CP + "].[Reporting Period], [qry_UNION_" + CP + "].[Invoice Date], [qry_UNION_" + CP + "].[SAP Account ID], [qry_UNION_" + CP + "].[Customer Name], [qry_UNION_" + CP + "].[Segment ID], [qry_UNION_" + CP + "].[Sub Segment ID], [qry_UNION_" + CP + "].[Total Rev Post Disc & Adj (USD)]" & _
From + ";"
Set qdf = db.CreateQueryDef("qry_ZZTESTSQL1", strSQL)
DoCmd.OpenQuery "qry_ZZTESTSQL1"
db.QueryDefs.Delete ("qry_ZZTESTSQL1")
Set qdf = Nothing
Set db = Nothing
End Sub
Cheers!!
Gary
Can anyone help with this one. This is attached to a button on a form if that helps at all.
When I get the line about opening the query, I get a run time error.
Private Sub Command1_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim strSQL As String
Dim CP As String
Dim From As String
Set db = CurrentDb
CP = InputBox("Please Enter the charge period to update, YYYYMM")
From = "From qry_UNION_" + CP
strSQL = "INSERT INTO tbl_RR19_Charge_Bucket_200211_2 ( [Billing Sys Code], [Invoice Number], [Opco Code], [Product Group 1 Desc], [Product Group 2 Desc], [Product Group 3 Desc], [Product Group 4 Desc], [Product Group 1 ID], [Product Group 4 ID], [Charge Period], [Reporting Period], [Invoice Date], [SAP Account ID], [Customer Name], [Segment ID], [Sub Segment ID], [Total Rev Post Disc & Adj (USD)] )" & _
"SELECT [qry_UNION_" + CP + "].[Billing Sys Code], [qry_UNION_" + CP + "].[Invoice Number], [qry_UNION_" + CP + "].[Opco Code], [qry_UNION_" + CP + "].[Product Group 1 Desc], [qry_UNION_" + CP + "].[Product Group 2 Desc], [qry_UNION_" + CP + "].[Product Group 3 Desc], [qry_UNION_" + CP + "].[Product Group 4 Desc], [qry_UNION_" + CP + "].[Product Group 1 ID], [qry_UNION_" + CP + "].[Product Group 4 ID], [qry_UNION_" + CP + "].[Charge Period], [qry_UNION_" + CP + "].[Reporting Period], [qry_UNION_" + CP + "].[Invoice Date], [qry_UNION_" + CP + "].[SAP Account ID], [qry_UNION_" + CP + "].[Customer Name], [qry_UNION_" + CP + "].[Segment ID], [qry_UNION_" + CP + "].[Sub Segment ID], [qry_UNION_" + CP + "].[Total Rev Post Disc & Adj (USD)]" & _
From + ";"
Set qdf = db.CreateQueryDef("qry_ZZTESTSQL1", strSQL)
DoCmd.OpenQuery "qry_ZZTESTSQL1"
db.QueryDefs.Delete ("qry_ZZTESTSQL1")
Set qdf = Nothing
Set db = Nothing
End Sub
Cheers!!
Gary