vba where Error 3131

  • Thread starter Thread starter Ralph
  • Start date Start date
R

Ralph

I want to use a where statement and keep getting and Error3131 and am using
Access 2003. Here is the code I am using if you have a suggestion, great.

Private Sub cmdPage4_Click()
On Error GoTo ProcError

Dim strSQL As String


strSQL = "INSERT INTO tbl4FNSOCT09 ( ReviewNo, PerNoInc1, [TypeIncome1-1],
[IncomeAmount1-1], [TypeIncome2-1], [IncomeAmount2-1], [TypeIncome3-1],
[IncomeAmount3-1], [TypeIncome4-1], [IncomeAmount4-1], " _
& "PerNoInc2, [TypeIncome1-2], [IncomeAmount1-2], [TypeIncome2-2],
[IncomeAmount2-2], [TypeIncome3-2], [IncomeAmount3-2], [TypeIncome4-2],
[IncomeAmount4-2], " _
& "PerNoInc3, [TypeIncome1-3], [IncomeAmount1-3], [TypeIncome2-3],
[IncomeAmount2-3], [TypeIncome3-3], [IncomeAmount3-3], [TypeIncome4-3],
[IncomeAmount4-3], " _
& "PerNoInc4, [TypeIncome1-4], [IncomeAmount1-4], [TypeIncome2-4],
[IncomeAmount2-4], [TypeIncome3-4], [IncomeAmount3-4], [TypeIncome4-4],
[IncomeAmount4-4], " _
& "PerNoInc5, [TypeIncome1-5], [IncomeAmount1-5], [TypeIncome2-5],
[IncomeAmount2-5], [TypeIncome3-5], [IncomeAmount3-5], [TypeIncome4-5],
[IncomeAmount4-5], " _
& "PerNoInc6, [TypeIncome1-6], [IncomeAmount1-6], [TypeIncome2-6],
[IncomeAmount2-6], [TypeIncome3-6], [IncomeAmount3-6], [TypeIncome4-6],
[IncomeAmount4-6], " _
& "PerNoInc7, [TypeIncome1-7], [IncomeAmount1-7], [TypeIncome2-7],
[IncomeAmount2-7], [TypeIncome3-7], [IncomeAmount3-7], [TypeIncome4-7],
[IncomeAmount4-7], " _
& "PerNoInc8, [TypeIncome1-8], [IncomeAmount1-8], [TypeIncome2-8],
[IncomeAmount2-8], [TypeIncome3-8], [IncomeAmount3-8], [TypeIncome4-8],
[IncomeAmount4-8], " _
& "PerNoInc9, [TypeIncome1-9], [IncomeAmount1-9], [TypeIncome2-9],
[IncomeAmount2-9], [TypeIncome3-9], [IncomeAmount3-9], [TypeIncome4-9],
[IncomeAmount4-9], " _
& "PerNoInc10, [TypeIncome1-10], [IncomeAmount1-10], [TypeIncome2-10],
[IncomeAmount2-10], [TypeIncome3-10], [IncomeAmount3-10], [TypeIncome4-10],
[IncomeAmount4-10], [Timeliness] ) " _
& "SELECT tblPage4.ReviewNo, IIf([PerNoInc1]<1,Null,Right(00 &
[PerNoInc1],2)) AS PNI1, IIF([TypeIncome1-1]<1,Null, [TypeIncome1-1]) AS
[TInc1-1], IIf([IncomeAmount1-1]<1,Null,Right(0000 & [IncomeAmount1-1],4)) AS
[IncA1-1], IIF([TypeIncome2-1]<1,Null,[TypeIncome2-1]) AS [TInc2-1],
IIf([IncomeAmount2-1]<1,Null,Right(0000 & [IncomeAmount2-1],4)) AS [IncA2-1],
IIF([TypeIncome3-1]<1,Null,[TypeIncome3-1]) AS [TInc3-1],
IIf([IncomeAmount3-1]<1,Null,Right(0000 & [IncomeAmount3-1],4)) AS [IncA3-1],
IIF([TypeIncome4-1]<1,Null,[TypeIncome4-1]) AS [TInc4-1],
IIf([IncomeAmount4-1]<1,Null,Right(0000 & [IncomeAmount4-1],4)) AS [IncA4-1],
" _
& "IIf([PerNoInc2]<1,Null,Right(00 & [PerNoInc2],2)) AS PNI2,
IIF([TypeIncome1-2]<1,Null, [TypeIncome1-2]) AS [TInc1-2],
IIf([IncomeAmount1-2]<1,Null,Right(0000 & [IncomeAmount1-2],4)) AS [IncA1-2],
IIF([TypeIncome2-2]<1,Null,[TypeIncome2-2]) AS [TInc2-2],
IIf([IncomeAmount2-2]<1,Null,Right(0000 & [IncomeAmount2-2],4)) AS [IncA2-2],
IIF([TypeIncome3-2]<1,Null,[TypeIncome3-2]) AS [TInc3-2],
IIf([IncomeAmount3-2]<1,Null,Right(0000 & [IncomeAmount3-2],4)) AS [IncA3-2],
IIF([TypeIncome4-2]<1,Null,[TypeIncome4-2]) AS [TInc4-2],
IIf([IncomeAmount4-2]<1,Null,Right(0000 & [IncomeAmount4-2],4)) AS [IncA4-2]
, " _
& "IIf([PerNoInc3]<1,Null,Right(00 & [PerNoInc3],2)) AS PNI3,
IIF([TypeIncome1-3]<1,Null, [TypeIncome1-3]) AS [TInc1-3],
IIf([IncomeAmount1-2]<1,Null,Right(0000 & [IncomeAmount1-3],4)) AS [IncA1-3],
IIF([TypeIncome2-3]<1,Null,[TypeIncome2-3]) AS [TInc2-3],
IIf([IncomeAmount2-3]<1,Null,Right(0000 & [IncomeAmount2-3],4)) AS [IncA2-3],
IIF([TypeIncome3-3]<1,Null,[TypeIncome3-3]) AS [TInc3-3],
IIf([IncomeAmount3-3]<1,Null,Right(0000 & [IncomeAmount3-3],4)) AS [IncA3-3],
IIF([TypeIncome4-3]<1,Null,[TypeIncome4-3]) AS [TInc4-3],
IIf([IncomeAmount4-3]<1,Null,Right(0000 & [IncomeAmount4-3],4)) AS [IncA4-3],
" _
& "IIf([PerNoInc4]<1,Null,Right(00 & [PerNoInc4],2)) AS PNI4,
IIF([TypeIncome1-4]<1,Null, [TypeIncome1-4]) AS [TInc1-4],
IIf([IncomeAmount1-4]<1,Null,Right(0000 & [IncomeAmount1-4],4)) AS [IncA1-4],
IIF([TypeIncome2-4]<1,Null,[TypeIncome2-4]) AS [TInc2-4],
IIf([IncomeAmount2-4]<1,Null,Right(0000 & [IncomeAmount2-4],4)) AS [IncA2-4],
IIF([TypeIncome3-4]<1,Null,[TypeIncome3-4]) AS [TInc3-4],
IIf([IncomeAmount3-4]<1,Null,Right(0000 & [IncomeAmount3-4],4)) AS [IncA3-4],
IIF([TypeIncome4-4]<1,Null,[TypeIncome4-4]) AS [TInc4-4],
IIf([IncomeAmount4-4]<1,Null,Right(0000 & [IncomeAmount4-4],4)) AS [IncA4-4],
" _
& "IIf([PerNoInc5]<1,Null,Right(00 & [PerNoInc5],2)) AS PNI5,
IIF([TypeIncome1-5]<1,Null, [TypeIncome1-5]) AS [TInc1-5],
IIf([IncomeAmount1-5]<1,Null,Right(0000 & [IncomeAmount1-5],4)) AS [IncA1-5],
IIF([TypeIncome2-5]<1,Null,[TypeIncome2-5]) AS [TInc2-5],
IIf([IncomeAmount2-5]<1,Null,Right(0000 & [IncomeAmount2-5],4)) AS [IncA2-5],
IIF([TypeIncome3-5]<1,Null,[TypeIncome3-5]) AS [TInc3-5],
IIf([IncomeAmount3-5]<1,Null,Right(0000 & [IncomeAmount3-5],4)) AS [IncA3-5],
IIF([TypeIncome4-5]<1,Null,[TypeIncome4-5]) AS [TInc4-5],
IIf([IncomeAmount4-5]<1,Null,Right(0000 & [IncomeAmount4-5],4)) AS [IncA4-5],
" _
& "IIf([PerNoInc6]<1,Null,Right(00 & [PerNoInc6],2)) AS PNI6,
IIF([TypeIncome1-6]<1,Null, [TypeIncome1-6]) AS [TInc1-6],
IIf([IncomeAmount1-6]<1,Null,Right(0000 & [IncomeAmount1-6],4)) AS [IncA1-6],
IIF([TypeIncome2-6]<1,Null,[TypeIncome2-6]) AS [TInc2-6],
IIf([IncomeAmount2-6]<1,Null,Right(0000 & [IncomeAmount2-6],4)) AS [IncA2-6],
IIF([TypeIncome3-6]<1,Null,[TypeIncome3-6]) AS [TInc3-6],
IIf([IncomeAmount3-6]<1,Null,Right(0000 & [IncomeAmount3-6],4)) AS [IncA3-6],
IIF([TypeIncome4-6]<1,Null,[TypeIncome4-6]) AS [TInc4-6],
IIf([IncomeAmount4-6]<1,Null,Right(0000 & [IncomeAmount4-6],4)) AS [IncA4-6],
" _
& "IIf([PerNoInc7]<1,Null,Right(00 & [PerNoInc7],2)) AS PNI7,
IIF([TypeIncome1-7]<1,Null, [TypeIncome1-7]) AS [TInc1-7],
IIf([IncomeAmount1-7]<1,Null,Right(0000 & [IncomeAmount1-7],4)) AS [IncA1-7],
IIF([TypeIncome2-7]<1,Null,[TypeIncome2-7]) AS [TInc2-7],
IIf([IncomeAmount2-7]<1,Null,Right(0000 & [IncomeAmount2-7],4)) AS [IncA2-7],
IIF([TypeIncome3-7]<1,Null,[TypeIncome3-7]) AS [TInc3-7],
IIf([IncomeAmount3-7]<1,Null,Right(0000 & [IncomeAmount3-7],4)) AS [IncA3-7],
IIF([TypeIncome4-7]<1,Null,[TypeIncome4-7]) AS [TInc4-7],
IIf([IncomeAmount4-7]<1,Null,Right(0000 & [IncomeAmount4-7],4)) AS [IncA4-7],
" _
& "IIf([PerNoInc8]<1,Null,Right(00 & [PerNoInc8],2)) AS PNI8,
IIF([TypeIncome1-8]<1,Null, [TypeIncome1-8]) AS [TInc1-8],
IIf([IncomeAmount1-8]<1,Null,Right(0000 & [IncomeAmount1-8],4)) AS [IncA1-8],
IIF([TypeIncome2-8]<1,Null,[TypeIncome2-8]) AS [TInc2-8],
IIf([IncomeAmount2-8]<1,Null,Right(0000 & [IncomeAmount2-8],4)) AS [IncA2-8],
IIF([TypeIncome3-8]<1,Null,[TypeIncome3-8]) AS [TInc3-8],
IIf([IncomeAmount3-8]<1,Null,Right(0000 & [IncomeAmount3-8],4)) AS [IncA3-8],
IIF([TypeIncome4-8]<1,Null,[TypeIncome4-8]) AS [TInc4-8],
IIf([IncomeAmount4-8]<1,Null,Right(0000 & [IncomeAmount4-8],4)) AS [IncA4-8],
" _
& "IIf([PerNoInc9]<1,Null,Right(00 & [PerNoInc9],2)) AS PNI9,
IIF([TypeIncome1-9]<1,Null, [TypeIncome1-9]) AS [TInc1-9],
IIf([IncomeAmount1-9]<1,Null,Right(0000 & [IncomeAmount1-9],4)) AS [IncA1-9],
IIF([TypeIncome2-9]<1,Null,[TypeIncome2-9]) AS [TInc2-9],
IIf([IncomeAmount2-9]<1,Null,Right(0000 & [IncomeAmount2-9],4)) AS [IncA2-9],
IIF([TypeIncome3-9]<1,Null,[TypeIncome3-9]) AS [TInc3-9],
IIf([IncomeAmount3-9]<1,Null,Right(0000 & [IncomeAmount3-9],4)) AS [IncA3-9],
IIF([TypeIncome4-9]<1,Null,[TypeIncome4-9]) AS [TInc4-9],
IIf([IncomeAmount4-9]<1,Null,Right(0000 & [IncomeAmount4-9],4)) AS [IncA4-9],
" _
& "IIf([PerNoInc10]<1,Null,Right(00 & [PerNoInc10],2)) AS PNI10,
IIF([TypeIncome1-10]<1,Null, [TypeIncome1-10]) AS [TInc1-10],
IIf([IncomeAmount1-10]<1,Null,Right(0000 & [IncomeAmount1-10],4)) AS
[IncA1-10], IIF([TypeIncome2-10]<1,Null,[TypeIncome2-10]) AS [TInc2-10],
IIf([IncomeAmount2-10]<1,Null,Right(0000 & [IncomeAmount2-10],4)) AS
[IncA2-10], IIF([TypeIncome3-10]<1,Null,[TypeIncome3-10]) AS [TInc3-10],
IIf([IncomeAmount3-10]<1,Null,Right(0000 & [IncomeAmount3-10],4)) AS
[IncA3-10], IIF([TypeIncome4-10]<1,Null,[TypeIncome4-10]) AS [TInc4-10],
IIf([IncomeAmount4-10]<1,Null,Right(0000 & [IncomeAmount4-10],4)) AS
[IncA4-10], [Timeliness] " _
& "FROM tblPage4" _
& "WHERE (CInt(Mid([ReviewNo],2,2))=[grpSampleMonth])"

CurrentDb.Execute strSQL, dbFailOnError

MsgBox "The page 4 data is loaded in the FNS database"

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure YourProcedureName..."
Resume ExitProc

End Sub

I’ve built a form with a control button to run the code and a object group
that has 12 values.
 
Ralph said:
I want to use a where statement and keep getting and Error3131 and am using
Access 2003. Here is the code I am using if you have a suggestion, great.

Private Sub cmdPage4_Click()
On Error GoTo ProcError

Dim strSQL As String


strSQL = "INSERT INTO tbl4FNSOCT09 ( ReviewNo, PerNoInc1, [TypeIncome1-1],
[IncomeAmount1-1], [TypeIncome2-1], [IncomeAmount2-1], [TypeIncome3-1],
[IncomeAmount3-1], [TypeIncome4-1], [IncomeAmount4-1], " _
& "PerNoInc2, [TypeIncome1-2], [IncomeAmount1-2], [TypeIncome2-2],
[IncomeAmount2-2], [TypeIncome3-2], [IncomeAmount3-2], [TypeIncome4-2],
[IncomeAmount4-2], " _
& "PerNoInc3, [TypeIncome1-3], [IncomeAmount1-3], [TypeIncome2-3],
[IncomeAmount2-3], [TypeIncome3-3], [IncomeAmount3-3], [TypeIncome4-3],
[IncomeAmount4-3], " _
& "PerNoInc4, [TypeIncome1-4], [IncomeAmount1-4], [TypeIncome2-4],
[IncomeAmount2-4], [TypeIncome3-4], [IncomeAmount3-4], [TypeIncome4-4],
[IncomeAmount4-4], " _
& "PerNoInc5, [TypeIncome1-5], [IncomeAmount1-5], [TypeIncome2-5],
[IncomeAmount2-5], [TypeIncome3-5], [IncomeAmount3-5], [TypeIncome4-5],
[IncomeAmount4-5], " _
& "PerNoInc6, [TypeIncome1-6], [IncomeAmount1-6], [TypeIncome2-6],
[IncomeAmount2-6], [TypeIncome3-6], [IncomeAmount3-6], [TypeIncome4-6],
[IncomeAmount4-6], " _
& "PerNoInc7, [TypeIncome1-7], [IncomeAmount1-7], [TypeIncome2-7],
[IncomeAmount2-7], [TypeIncome3-7], [IncomeAmount3-7], [TypeIncome4-7],
[IncomeAmount4-7], " _
& "PerNoInc8, [TypeIncome1-8], [IncomeAmount1-8], [TypeIncome2-8],
[IncomeAmount2-8], [TypeIncome3-8], [IncomeAmount3-8], [TypeIncome4-8],
[IncomeAmount4-8], " _
& "PerNoInc9, [TypeIncome1-9], [IncomeAmount1-9], [TypeIncome2-9],
[IncomeAmount2-9], [TypeIncome3-9], [IncomeAmount3-9], [TypeIncome4-9],
[IncomeAmount4-9], " _
& "PerNoInc10, [TypeIncome1-10], [IncomeAmount1-10], [TypeIncome2-10],
[IncomeAmount2-10], [TypeIncome3-10], [IncomeAmount3-10],
[TypeIncome4-10],
[IncomeAmount4-10], [Timeliness] ) " _
& "SELECT tblPage4.ReviewNo, IIf([PerNoInc1]<1,Null,Right(00 &
[PerNoInc1],2)) AS PNI1, IIF([TypeIncome1-1]<1,Null, [TypeIncome1-1]) AS
[TInc1-1], IIf([IncomeAmount1-1]<1,Null,Right(0000 & [IncomeAmount1-1],4))
AS
[IncA1-1], IIF([TypeIncome2-1]<1,Null,[TypeIncome2-1]) AS [TInc2-1],
IIf([IncomeAmount2-1]<1,Null,Right(0000 & [IncomeAmount2-1],4)) AS
[IncA2-1],
IIF([TypeIncome3-1]<1,Null,[TypeIncome3-1]) AS [TInc3-1],
IIf([IncomeAmount3-1]<1,Null,Right(0000 & [IncomeAmount3-1],4)) AS
[IncA3-1],
IIF([TypeIncome4-1]<1,Null,[TypeIncome4-1]) AS [TInc4-1],
IIf([IncomeAmount4-1]<1,Null,Right(0000 & [IncomeAmount4-1],4)) AS
[IncA4-1],
" _
& "IIf([PerNoInc2]<1,Null,Right(00 & [PerNoInc2],2)) AS PNI2,
IIF([TypeIncome1-2]<1,Null, [TypeIncome1-2]) AS [TInc1-2],
IIf([IncomeAmount1-2]<1,Null,Right(0000 & [IncomeAmount1-2],4)) AS
[IncA1-2],
IIF([TypeIncome2-2]<1,Null,[TypeIncome2-2]) AS [TInc2-2],
IIf([IncomeAmount2-2]<1,Null,Right(0000 & [IncomeAmount2-2],4)) AS
[IncA2-2],
IIF([TypeIncome3-2]<1,Null,[TypeIncome3-2]) AS [TInc3-2],
IIf([IncomeAmount3-2]<1,Null,Right(0000 & [IncomeAmount3-2],4)) AS
[IncA3-2],
IIF([TypeIncome4-2]<1,Null,[TypeIncome4-2]) AS [TInc4-2],
IIf([IncomeAmount4-2]<1,Null,Right(0000 & [IncomeAmount4-2],4)) AS
[IncA4-2]
, " _
& "IIf([PerNoInc3]<1,Null,Right(00 & [PerNoInc3],2)) AS PNI3,
IIF([TypeIncome1-3]<1,Null, [TypeIncome1-3]) AS [TInc1-3],
IIf([IncomeAmount1-2]<1,Null,Right(0000 & [IncomeAmount1-3],4)) AS
[IncA1-3],
IIF([TypeIncome2-3]<1,Null,[TypeIncome2-3]) AS [TInc2-3],
IIf([IncomeAmount2-3]<1,Null,Right(0000 & [IncomeAmount2-3],4)) AS
[IncA2-3],
IIF([TypeIncome3-3]<1,Null,[TypeIncome3-3]) AS [TInc3-3],
IIf([IncomeAmount3-3]<1,Null,Right(0000 & [IncomeAmount3-3],4)) AS
[IncA3-3],
IIF([TypeIncome4-3]<1,Null,[TypeIncome4-3]) AS [TInc4-3],
IIf([IncomeAmount4-3]<1,Null,Right(0000 & [IncomeAmount4-3],4)) AS
[IncA4-3],
" _
& "IIf([PerNoInc4]<1,Null,Right(00 & [PerNoInc4],2)) AS PNI4,
IIF([TypeIncome1-4]<1,Null, [TypeIncome1-4]) AS [TInc1-4],
IIf([IncomeAmount1-4]<1,Null,Right(0000 & [IncomeAmount1-4],4)) AS
[IncA1-4],
IIF([TypeIncome2-4]<1,Null,[TypeIncome2-4]) AS [TInc2-4],
IIf([IncomeAmount2-4]<1,Null,Right(0000 & [IncomeAmount2-4],4)) AS
[IncA2-4],
IIF([TypeIncome3-4]<1,Null,[TypeIncome3-4]) AS [TInc3-4],
IIf([IncomeAmount3-4]<1,Null,Right(0000 & [IncomeAmount3-4],4)) AS
[IncA3-4],
IIF([TypeIncome4-4]<1,Null,[TypeIncome4-4]) AS [TInc4-4],
IIf([IncomeAmount4-4]<1,Null,Right(0000 & [IncomeAmount4-4],4)) AS
[IncA4-4],
" _
& "IIf([PerNoInc5]<1,Null,Right(00 & [PerNoInc5],2)) AS PNI5,
IIF([TypeIncome1-5]<1,Null, [TypeIncome1-5]) AS [TInc1-5],
IIf([IncomeAmount1-5]<1,Null,Right(0000 & [IncomeAmount1-5],4)) AS
[IncA1-5],
IIF([TypeIncome2-5]<1,Null,[TypeIncome2-5]) AS [TInc2-5],
IIf([IncomeAmount2-5]<1,Null,Right(0000 & [IncomeAmount2-5],4)) AS
[IncA2-5],
IIF([TypeIncome3-5]<1,Null,[TypeIncome3-5]) AS [TInc3-5],
IIf([IncomeAmount3-5]<1,Null,Right(0000 & [IncomeAmount3-5],4)) AS
[IncA3-5],
IIF([TypeIncome4-5]<1,Null,[TypeIncome4-5]) AS [TInc4-5],
IIf([IncomeAmount4-5]<1,Null,Right(0000 & [IncomeAmount4-5],4)) AS
[IncA4-5],
" _
& "IIf([PerNoInc6]<1,Null,Right(00 & [PerNoInc6],2)) AS PNI6,
IIF([TypeIncome1-6]<1,Null, [TypeIncome1-6]) AS [TInc1-6],
IIf([IncomeAmount1-6]<1,Null,Right(0000 & [IncomeAmount1-6],4)) AS
[IncA1-6],
IIF([TypeIncome2-6]<1,Null,[TypeIncome2-6]) AS [TInc2-6],
IIf([IncomeAmount2-6]<1,Null,Right(0000 & [IncomeAmount2-6],4)) AS
[IncA2-6],
IIF([TypeIncome3-6]<1,Null,[TypeIncome3-6]) AS [TInc3-6],
IIf([IncomeAmount3-6]<1,Null,Right(0000 & [IncomeAmount3-6],4)) AS
[IncA3-6],
IIF([TypeIncome4-6]<1,Null,[TypeIncome4-6]) AS [TInc4-6],
IIf([IncomeAmount4-6]<1,Null,Right(0000 & [IncomeAmount4-6],4)) AS
[IncA4-6],
" _
& "IIf([PerNoInc7]<1,Null,Right(00 & [PerNoInc7],2)) AS PNI7,
IIF([TypeIncome1-7]<1,Null, [TypeIncome1-7]) AS [TInc1-7],
IIf([IncomeAmount1-7]<1,Null,Right(0000 & [IncomeAmount1-7],4)) AS
[IncA1-7],
IIF([TypeIncome2-7]<1,Null,[TypeIncome2-7]) AS [TInc2-7],
IIf([IncomeAmount2-7]<1,Null,Right(0000 & [IncomeAmount2-7],4)) AS
[IncA2-7],
IIF([TypeIncome3-7]<1,Null,[TypeIncome3-7]) AS [TInc3-7],
IIf([IncomeAmount3-7]<1,Null,Right(0000 & [IncomeAmount3-7],4)) AS
[IncA3-7],
IIF([TypeIncome4-7]<1,Null,[TypeIncome4-7]) AS [TInc4-7],
IIf([IncomeAmount4-7]<1,Null,Right(0000 & [IncomeAmount4-7],4)) AS
[IncA4-7],
" _
& "IIf([PerNoInc8]<1,Null,Right(00 & [PerNoInc8],2)) AS PNI8,
IIF([TypeIncome1-8]<1,Null, [TypeIncome1-8]) AS [TInc1-8],
IIf([IncomeAmount1-8]<1,Null,Right(0000 & [IncomeAmount1-8],4)) AS
[IncA1-8],
IIF([TypeIncome2-8]<1,Null,[TypeIncome2-8]) AS [TInc2-8],
IIf([IncomeAmount2-8]<1,Null,Right(0000 & [IncomeAmount2-8],4)) AS
[IncA2-8],
IIF([TypeIncome3-8]<1,Null,[TypeIncome3-8]) AS [TInc3-8],
IIf([IncomeAmount3-8]<1,Null,Right(0000 & [IncomeAmount3-8],4)) AS
[IncA3-8],
IIF([TypeIncome4-8]<1,Null,[TypeIncome4-8]) AS [TInc4-8],
IIf([IncomeAmount4-8]<1,Null,Right(0000 & [IncomeAmount4-8],4)) AS
[IncA4-8],
" _
& "IIf([PerNoInc9]<1,Null,Right(00 & [PerNoInc9],2)) AS PNI9,
IIF([TypeIncome1-9]<1,Null, [TypeIncome1-9]) AS [TInc1-9],
IIf([IncomeAmount1-9]<1,Null,Right(0000 & [IncomeAmount1-9],4)) AS
[IncA1-9],
IIF([TypeIncome2-9]<1,Null,[TypeIncome2-9]) AS [TInc2-9],
IIf([IncomeAmount2-9]<1,Null,Right(0000 & [IncomeAmount2-9],4)) AS
[IncA2-9],
IIF([TypeIncome3-9]<1,Null,[TypeIncome3-9]) AS [TInc3-9],
IIf([IncomeAmount3-9]<1,Null,Right(0000 & [IncomeAmount3-9],4)) AS
[IncA3-9],
IIF([TypeIncome4-9]<1,Null,[TypeIncome4-9]) AS [TInc4-9],
IIf([IncomeAmount4-9]<1,Null,Right(0000 & [IncomeAmount4-9],4)) AS
[IncA4-9],
" _
& "IIf([PerNoInc10]<1,Null,Right(00 & [PerNoInc10],2)) AS PNI10,
IIF([TypeIncome1-10]<1,Null, [TypeIncome1-10]) AS [TInc1-10],
IIf([IncomeAmount1-10]<1,Null,Right(0000 & [IncomeAmount1-10],4)) AS
[IncA1-10], IIF([TypeIncome2-10]<1,Null,[TypeIncome2-10]) AS [TInc2-10],
IIf([IncomeAmount2-10]<1,Null,Right(0000 & [IncomeAmount2-10],4)) AS
[IncA2-10], IIF([TypeIncome3-10]<1,Null,[TypeIncome3-10]) AS [TInc3-10],
IIf([IncomeAmount3-10]<1,Null,Right(0000 & [IncomeAmount3-10],4)) AS
[IncA3-10], IIF([TypeIncome4-10]<1,Null,[TypeIncome4-10]) AS [TInc4-10],
IIf([IncomeAmount4-10]<1,Null,Right(0000 & [IncomeAmount4-10],4)) AS
[IncA4-10], [Timeliness] " _
& "FROM tblPage4" _
& "WHERE (CInt(Mid([ReviewNo],2,2))=[grpSampleMonth])"

CurrentDb.Execute strSQL, dbFailOnError

MsgBox "The page 4 data is loaded in the FNS database"

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure YourProcedureName..."
Resume ExitProc

End Sub

I’ve built a form with a control button to run the code and a object group
that has 12 values.
Click to expand...


Your concatenated SQL string doesn't have a space between the table name and
the WHERE keyword. Try this:

& "FROM tblPage4 " _
& "WHERE (CInt(Mid([ReviewNo],2,2))=[grpSampleMonth])"

By the way, the fact that you have numbered repeating fields suggests a very
unnormalized table structure. While there are sometimes good reasons for
such things, if this isn't one of those unusual cases you may want to read a
little bit about "database normalization".
 
The tables (4) being written to are owned by another organization and I can't
deal with the normalization issues, just have to write to them.

I added with the space and then added a more explicit definition-
& "FROM tblPage4 " _
& "WHERE (CInt(Mid([tblPage4].[ReviewNo],2,2))=
Forms!frmLoadToFNS!grpSampleMonth) "

And got another but different error message: "Error 3061: To few parameters.
Expected 1."

Thanks for the help, any suggestions for this?
 
Ralph said:
The tables (4) being written to are owned by another organization and I
can't
deal with the normalization issues, just have to write to them.
Click to expand...

You have my sympathy.
I added with the space and then added a more explicit definition-
& "FROM tblPage4 " _
& "WHERE (CInt(Mid([tblPage4].[ReviewNo],2,2))=
Forms!frmLoadToFNS!grpSampleMonth) "

And got another but different error message: "Error 3061: To few
parameters.
Expected 1."

Thanks for the help, any suggestions for this?
Click to expand...

The DAO Execute method doesn't recorgnize embedded references to forms and
controls, so it considers them to be parameters. Instead of having the SQL
statement refer to the form control, build the value of the control into the
SQL string as a literal. It looks like the value is a number, so do this:

& "WHERE CInt(Mid([tblPage4].[ReviewNo],2,2))=" & _
Forms!frmLoadToFNS!grpSampleMonth

Or, if the code is running on frmLoadToFNS, this is better:

& "WHERE CInt(Mid([tblPage4].[ReviewNo],2,2))=" & _
Me.grpSampleMonth
 
One more issue. I'd like to use the grpSampleMonth value to identify which
table in the INSERT INTO command.

Something like this:

Dim FNSmo As String

FNSmo = IIf(Me.grpSampleMonth = 1, "JAN", IIf(grpSampleMonth = 2, "FEB",
IIf(Me.grpSampleMonth = 3, "MAR", IIf(grpSampleMonth = 4, "APR",
IIf(Me.grpSampleMonth = 5, "MAY", IIf(grpSampleMonth = 6, "JUN",
IIf(Me.grpSampleMonth = 7, "JUL", IIf(grpSampleMonth = 8, "AUG",
IIf(Me.grpSampleMonth = 9, "SEP", IIf(grpSampleMonth = 10, "OCT",
IIf(Me.grpSampleMonth = 11, "NOV", "DEC")))))))))))


strSQL = "INSERT INTO tbl3FNS & FNSmo & 09 ( ReviewNo, PerNoPli1, FSAffil1,
Relationhh1, Age1, etc.

I declaired FNSmo and then tried to use it but as I've coded it I am
getting "Error 3134: Syntax error in INSERT INTO statement."
 
strSQL = "INSERT INTO tbl3FNS" & FNSmo & "09 ( ReviewNo, PerNoPli1, FSAffil1,
Relationhh1, Age1, etc.


You could also use the MonthName function to return the abbreviated month name
FNSMO=MonthName(Me.grpSampleMonth,True)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Ralph said:
One more issue. I'd like to use the grpSampleMonth value to identify
which
table in the INSERT INTO command.

Something like this:

Dim FNSmo As String

FNSmo = IIf(Me.grpSampleMonth = 1, "JAN", IIf(grpSampleMonth = 2, "FEB",
IIf(Me.grpSampleMonth = 3, "MAR", IIf(grpSampleMonth = 4, "APR",
IIf(Me.grpSampleMonth = 5, "MAY", IIf(grpSampleMonth = 6, "JUN",
IIf(Me.grpSampleMonth = 7, "JUL", IIf(grpSampleMonth = 8, "AUG",
IIf(Me.grpSampleMonth = 9, "SEP", IIf(grpSampleMonth = 10, "OCT",
IIf(Me.grpSampleMonth = 11, "NOV", "DEC")))))))))))


strSQL = "INSERT INTO tbl3FNS & FNSmo & 09 ( ReviewNo, PerNoPli1,
FSAffil1,
Relationhh1, Age1, etc.

I declaired FNSmo and then tried to use it but as I've coded it I am
getting "Error 3134: Syntax error in INSERT INTO statement."
Click to expand...


You need build the constructed table name into the string, like so:

strSQL = "INSERT INTO tbl3FNS" & FNSmo & "09 " & _
"( ReviewNo, PerNoPli1, FSAffil1, Relationhh1, Age1, etc."

Incidentally, you could get the month-name abbreviation more easily like
this:

FNSmo = Choose(Me.grpSampleMonth, _
"JAN", "FEB", "MAR", "APR", "MAY", "JUN", _
"JUL", "AUG", "SEP", "OCT", "NOV", "DEC")

Or even like this:

FNSmo = UCase(MonthName(Me.grpSampleMonth, True))
 
Back
Top