Too few parameters error when exporting to text file

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

 

[John Nurick]

Hi Mike,

Sounds like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;269671

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

 

[MikeC]

Thanks John. KB 269671 certainly helps. I have followed the instructions,
but some details are not explained and I'm not sure where to find the rest
of the information I need.

Now I'm using "Select [Field List] Into
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\MyFolder;].[FileName#txt]" instead
of a regular Select statement. This work around successfully exports the
data to a text file without errors. However, I need to export the data to a
specific file format. I had previously defined the format in an export file
specification, but KB 269671 does not explain whether an existing export
file specification can be used or how to use an external schema file.
Normally, when a user exports a query to a text file, the export wizard
automatically displays and then the user can choose an export file
specification. The article offers no equivalent solution for the work
around, although it does prevent the error.

After some experimentation, I discovered that I can manipulate the
schema.ini file that is automatically placed in the destination folder.
This approach requires a little voodoo on my part because I have not yet
found sufficient reference information for the schema file arguments.
(Incidentally, I have two very thick reference books for Access and neither
book's index lists schema or export specification files! Maybe I'll search
GoogleGroups.)

The arguments in the schema file appear to supercede the export arguments
contained in the SQL Select statement. I have the file almost the way I
want it. I just need to find a way to *exclude* the slashes from my date
field (Col5). Is there way to specify this format as an argument in the
schema file or do I need to perform a concatenation in the SQL statement
instead?

Below is the content of my modified schema file:

[CUBS.txt]
ColNameHeader=False
CharacterSet=1252
Format=FixedLength
Col1=PaymentSlipNo Integer Width 6
Col2=PaymentSlipType Char Width 1
Col3=Filler1 Char Width 4
Col4=Name Char Width 26
Col5=PaymentReceivedDate Date Width 8
Col6=PaymentRecordID Integer Width 10
Col7=PaymentType Char Width 1
Col8=Filler2 Char Width 48
Col9=PaymentAmount Currency Width 26
Col10=AccountIDNumber Char Width 20

Hi Mike,

Sounds like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;269671

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

 

[MikeC]

....So, I go to GoogleGroups and search for information about how to specify
the date format in a schema.ini file and what do I find?

John has already answered my question! :-D

http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#a789b4cb061651ba

Thanks John. KB 269671 certainly helps. I have followed the
instructions, but some details are not explained and I'm not sure where to
find the rest of the information I need.

Now I'm using "Select [Field List] Into
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\MyFolder;].[FileName#txt]" instead
of a regular Select statement. This work around successfully exports the
data to a text file without errors. However, I need to export the data to
a specific file format. I had previously defined the format in an export
file specification, but KB 269671 does not explain whether an existing
export file specification can be used or how to use an external schema
file. Normally, when a user exports a query to a text file, the export
wizard automatically displays and then the user can choose an export file
specification. The article offers no equivalent solution for the work
around, although it does prevent the error.

After some experimentation, I discovered that I can manipulate the
schema.ini file that is automatically placed in the destination folder.
This approach requires a little voodoo on my part because I have not yet
found sufficient reference information for the schema file arguments.
(Incidentally, I have two very thick reference books for Access and
neither book's index lists schema or export specification files! Maybe
I'll search GoogleGroups.)

The arguments in the schema file appear to supercede the export arguments
contained in the SQL Select statement. I have the file almost the way I
want it. I just need to find a way to *exclude* the slashes from my date
field (Col5). Is there way to specify this format as an argument in the
schema file or do I need to perform a concatenation in the SQL statement
instead?

Below is the content of my modified schema file:

[CUBS.txt]
ColNameHeader=False
CharacterSet=1252
Format=FixedLength
Col1=PaymentSlipNo Integer Width 6
Col2=PaymentSlipType Char Width 1
Col3=Filler1 Char Width 4
Col4=Name Char Width 26
Col5=PaymentReceivedDate Date Width 8
Col6=PaymentRecordID Integer Width 10
Col7=PaymentType Char Width 1
Col8=Filler2 Char Width 48
Col9=PaymentAmount Currency Width 26
Col10=AccountIDNumber Char Width 20

Hi Mike,

Sounds like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;269671

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

 

[John Nurick]

We aim to please!

Also, look at the Help article "Initializing the Text Data Source
Driver"; if you scroll down far enough you get to schema.ini
documentation.