M
MikeC
I'm getting the below error when I attempt to export query data to a text
file.
Message Title: "Export Text Wizard"
Message Text: "Too few parameters. Expected 2."
The error occurs after the following has occurred:
1) The query has successfully returned rows.
2) The "Export..." option has been selected from the File menu.
3) A valid file name has been entered.
4) "Text Files(*.txt;*.csv;*.tab;*.asc)" has been selected as the output
file type.
5) The "Export All" button has been clicked.
I'm using Access XP SP3 and my operating system is Windows XP Home Edition
SP2.
I tried several other file types and the error did *not* occur.
I need the "Text Files(*.txt;*.csv;*.tab;*.asc)" file type because I will be
using a file schema to define a specific output file format. This part will
be done in VBA using the TransferText method.
The export was working successfully until I introduced a two new parameters
that utilize a *VBA function*. The parameters work fine and the query
returns the desired data, but now AXP's native Export function does not seem
to like the VBA interaction.
Because the query otherwise appears to be functioning correctly and the
error occurs only if I attempt to export data to a specific file type, I do
not believe the problem is in the VBA code itself. However, mixing VBA and
SQL may be confusing AXP. I have included the SQL and VBA code below for
reference.
Does anyone know how to resolve this issue while retaining the VBA function?
P.S. I'm cross-posting to microsoft.public.access.modulesdaovba and
microsoft.public.access.queries.
SQL Code:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SELECT
tblPaymentSlip.PaymentSlipNo
, Switch([tblPaymentSlip].[AllBox]=1,"S",[tblPaymentSlip].[AllBox]=2,"D")
AS PaymentSlipType
, " " AS Filler1
, tblPaymentSlip.DebtorName AS Name
, tblPaymentSlip.ActualPmtDate AS PaymentReceivedDate
, tblPayment.PmtID AS PaymentRecordID
, tblPayment.PmtType AS PaymentType
, " " AS Filler2
, tblPaymentDetail.PartialPaymentAmt AS PaymentAmount
, tblPaymentDetail.CubsIDNum AS AccountIDNumber
FROM
tblPayment
INNER JOIN (tblPaymentSlip
INNER JOIN (tblPaymentDetail
INNER JOIN tblAutoApply
ON tblPaymentDetail.PmtDetailID = tblAutoApply.fkPmtDetailID)
ON tblPaymentSlip.PaymentSlipNo = tblPaymentDetail.fkPaymentSlipNo)
ON tblPayment.PmtID = tblAutoApply.fkPmtID
WHERE
(((tblPaymentSlip.ActualPmtDate)>=[Forms]![frmCUBSDataExport]![txtPmtRecFromDt] AND(tblPaymentSlip.ActualPmtDate)<=[Forms]![frmCUBSDataExport]![txtPmtRecToDt]) AND((fnParamFound([tblPaymentSlip].[AllBox],"frmCUBSDataExport","lstPmtSlipType"))=True) AND((fnParamFound([tblPayment].[PmtType],"frmCUBSDataExport","lstPmtType"))=True));<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<VBA Code:<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Public Function fnParamFound(varValue, strForm As String, strControl AsString) As BooleanOn Error GoTo Err_Handler 'This procedure returns True or False depending on whether a field value 'passed by the calling query is equal to a value contained in aspecified 'control. ' 'This procedure is intended for combo boxes, list boxes and text boxes.If 'the control is a list box, then the selected values are firstconcatenated 'into a comma-delimited string before the string is evaluated. Dim strValue As String Dim ctl As Control Dim strParam As Variant Dim intListItem As Integer Set ctl = Forms(strForm)(strControl) With ctl 'Check the control type. Select Case .Properties("ControlType") Case acComboBox, acTextBox 'Control is a combo box or text box. Pass the value to thevariable. strParam = Trim$(.Value) Case acListBox 'Control is a list box. Check how many items are selected. If .ItemsSelected.Count > 0 Then 'Build comma-delimited parameter string. For intListItem = 0 To .ListCount - 1 If Len(strParam) > 0 Then If .Selected(intListItem) Then strParam = strParam & "," & .Column(0,intListItem) End If Else If .Selected(intListItem) Then strParam = .Column(0, intListItem) End If End If Next intListItem End If End Select End With 'Change to uppercase to make case-insensitive for the query. varValue = UCase(varValue) strParam = UCase(strParam) 'Check each delimited value in strParam. Do While Len(strParam) > 0 strValue = fnGetValue(strParam, ",") If strValue = LTrim$(RTrim$(Nz(varValue, vbNullString))) Then fnParamFound = True Exit Function Else fnParamFound = False End If LoopExit_Procedure: On Error Resume Next Set ctl = Nothing strParam = vbNullString varValue = Null Exit FunctionErr_Handler: If Err.Number <> 2501 Then ErrorHandler Err.Number, Err.Description, "fnParamFound",mconModuleName End If Resume Exit_ProcedureEnd FunctionPrivate Function fnGetValue(varParam, strDelim As String) As StringOn Error GoTo Err_Handler 'This procedure returns a trimmed value from a comma-delimited stringand 'removes the first delimited value from the input variable. Dim intDelim As Integer Dim strValue As String intDelim = InStr(1, varParam, strDelim) If intDelim <> 0 Then 'Pass only the 1st substring. strValue = Trim$(Split(varParam, strDelim, , vbTextCompare)(0)) 'Remove the 1st delimited value (ByRef) from the input variable. varParam = Trim$(Mid$(varParam, intDelim + 1)) Else strValue = Trim$(varParam) varParam = vbNullString End If fnGetValue = strValueExit_Procedure: Exit FunctionErr_Handler: If Err.Number <> 2501 Then ErrorHandler Err.Number, Err.Description, "fnGetValue",mconModuleName End If Resume Exit_ProcedureEnd Function<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
file.
Message Title: "Export Text Wizard"
Message Text: "Too few parameters. Expected 2."
The error occurs after the following has occurred:
1) The query has successfully returned rows.
2) The "Export..." option has been selected from the File menu.
3) A valid file name has been entered.
4) "Text Files(*.txt;*.csv;*.tab;*.asc)" has been selected as the output
file type.
5) The "Export All" button has been clicked.
I'm using Access XP SP3 and my operating system is Windows XP Home Edition
SP2.
I tried several other file types and the error did *not* occur.
I need the "Text Files(*.txt;*.csv;*.tab;*.asc)" file type because I will be
using a file schema to define a specific output file format. This part will
be done in VBA using the TransferText method.
The export was working successfully until I introduced a two new parameters
that utilize a *VBA function*. The parameters work fine and the query
returns the desired data, but now AXP's native Export function does not seem
to like the VBA interaction.
Because the query otherwise appears to be functioning correctly and the
error occurs only if I attempt to export data to a specific file type, I do
not believe the problem is in the VBA code itself. However, mixing VBA and
SQL may be confusing AXP. I have included the SQL and VBA code below for
reference.
Does anyone know how to resolve this issue while retaining the VBA function?
P.S. I'm cross-posting to microsoft.public.access.modulesdaovba and
microsoft.public.access.queries.
SQL Code:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SELECT
tblPaymentSlip.PaymentSlipNo
, Switch([tblPaymentSlip].[AllBox]=1,"S",[tblPaymentSlip].[AllBox]=2,"D")
AS PaymentSlipType
, " " AS Filler1
, tblPaymentSlip.DebtorName AS Name
, tblPaymentSlip.ActualPmtDate AS PaymentReceivedDate
, tblPayment.PmtID AS PaymentRecordID
, tblPayment.PmtType AS PaymentType
, " " AS Filler2
, tblPaymentDetail.PartialPaymentAmt AS PaymentAmount
, tblPaymentDetail.CubsIDNum AS AccountIDNumber
FROM
tblPayment
INNER JOIN (tblPaymentSlip
INNER JOIN (tblPaymentDetail
INNER JOIN tblAutoApply
ON tblPaymentDetail.PmtDetailID = tblAutoApply.fkPmtDetailID)
ON tblPaymentSlip.PaymentSlipNo = tblPaymentDetail.fkPaymentSlipNo)
ON tblPayment.PmtID = tblAutoApply.fkPmtID
WHERE
(((tblPaymentSlip.ActualPmtDate)>=[Forms]![frmCUBSDataExport]![txtPmtRecFromDt] AND(tblPaymentSlip.ActualPmtDate)<=[Forms]![frmCUBSDataExport]![txtPmtRecToDt]) AND((fnParamFound([tblPaymentSlip].[AllBox],"frmCUBSDataExport","lstPmtSlipType"))=True) AND((fnParamFound([tblPayment].[PmtType],"frmCUBSDataExport","lstPmtType"))=True));<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<VBA Code:<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Public Function fnParamFound(varValue, strForm As String, strControl AsString) As BooleanOn Error GoTo Err_Handler 'This procedure returns True or False depending on whether a field value 'passed by the calling query is equal to a value contained in aspecified 'control. ' 'This procedure is intended for combo boxes, list boxes and text boxes.If 'the control is a list box, then the selected values are firstconcatenated 'into a comma-delimited string before the string is evaluated. Dim strValue As String Dim ctl As Control Dim strParam As Variant Dim intListItem As Integer Set ctl = Forms(strForm)(strControl) With ctl 'Check the control type. Select Case .Properties("ControlType") Case acComboBox, acTextBox 'Control is a combo box or text box. Pass the value to thevariable. strParam = Trim$(.Value) Case acListBox 'Control is a list box. Check how many items are selected. If .ItemsSelected.Count > 0 Then 'Build comma-delimited parameter string. For intListItem = 0 To .ListCount - 1 If Len(strParam) > 0 Then If .Selected(intListItem) Then strParam = strParam & "," & .Column(0,intListItem) End If Else If .Selected(intListItem) Then strParam = .Column(0, intListItem) End If End If Next intListItem End If End Select End With 'Change to uppercase to make case-insensitive for the query. varValue = UCase(varValue) strParam = UCase(strParam) 'Check each delimited value in strParam. Do While Len(strParam) > 0 strValue = fnGetValue(strParam, ",") If strValue = LTrim$(RTrim$(Nz(varValue, vbNullString))) Then fnParamFound = True Exit Function Else fnParamFound = False End If LoopExit_Procedure: On Error Resume Next Set ctl = Nothing strParam = vbNullString varValue = Null Exit FunctionErr_Handler: If Err.Number <> 2501 Then ErrorHandler Err.Number, Err.Description, "fnParamFound",mconModuleName End If Resume Exit_ProcedureEnd FunctionPrivate Function fnGetValue(varParam, strDelim As String) As StringOn Error GoTo Err_Handler 'This procedure returns a trimmed value from a comma-delimited stringand 'removes the first delimited value from the input variable. Dim intDelim As Integer Dim strValue As String intDelim = InStr(1, varParam, strDelim) If intDelim <> 0 Then 'Pass only the 1st substring. strValue = Trim$(Split(varParam, strDelim, , vbTextCompare)(0)) 'Remove the 1st delimited value (ByRef) from the input variable. varParam = Trim$(Mid$(varParam, intDelim + 1)) Else strValue = Trim$(varParam) varParam = vbNullString End If fnGetValue = strValueExit_Procedure: Exit FunctionErr_Handler: If Err.Number <> 2501 Then ErrorHandler Err.Number, Err.Description, "fnGetValue",mconModuleName End If Resume Exit_ProcedureEnd Function<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
