vba where Error 3131

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.
 
D

Dirk Goldgar

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.


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".
 
R

Ralph

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?
 
D

Dirk Goldgar

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.

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?

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
 
R

Ralph

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."
 
J

John Spencer

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
 
D

Dirk Goldgar

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."


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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 3075 4
VBA Coding Help - Need to edit field 1
Import Error 1
Overflow error 3
VBA code help 3
Combo Box Filter 2
VBA 2
Error inserting record 2

Top