Hello John,
I really appreciate your help on this matter.
I'm not sure i know how to check the items in the first paragraph of your reply, but if you can direct me i certainly will check those items.
The tables for this project are created from a Pervasive SQL 2000i database using COBOL programs. The COBOL programs use the data in Btrieve indexed files. When i run a report on the same data through the COBOL systems programs, it runs for about 90 minutes and does give the expected results.
Here is the code behind the form that starts the whole thing by clicking OK. Thanks for any other help that you can provide.
Tony
***********************************************************
Option Compare Database
Option Explicit
'Name of query used for export/reporting.
Const strcQueryName = "qryExport"
'Name of report used for previewing/printing the export.
Const strcReportName = "rptExport"
'SQL statement (without the WHERE clause) for the export query.
Const strcStub = "SELECT Trim(cust.nam) AS TrimNam, " & _
"Trim(cust.adrs_1) AS TrimAdrs_1, " & _
"Trim(cust.adrs_2) AS TrimAdrs_2, " & _
"Trim(cust.city) AS TrimCity, " & _
"Trim(cust.state) AS TrimState, " & _
"Trim(cust.zip_cod) AS TrimZip_cod, " & _
"Trim(cust.email_adrs) AS TrimEmail_adrs, " & _
"Trim(cust.phone_no_1) AS TrimPhone_no_1, " & _
"CLng(sa_lin.item_no,) AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no "
Const strcTail = "GROUP BY Trim(cust.nam), " & _
"Trim(cust.adrs_1), " & _
"Trim(cust.adrs_2), " & _
"Trim(cust.city), " & _
"Trim(cust.state), " & _
"Trim(cust.zip_cod), " & _
"Trim(cust.email_adrs), " & _
"Trim(cust.phone_no_1), " & _
"CLng(sa_lin.item_no) " & _
"ORDER BY Trim(cust.nam);"
Function CustQuery() As Boolean
'On Error GoTo CustQueryError
'Purpose: Assign the SQL property of the query, based on the controls where user entered something.
' The query is then used for export and/or report in cmdOk_Click.
Dim strWhere As String
Dim lngLen As Long
Dim CusQry As String
'***********************************************
'Starting and ending ITEMS: sa_lin.item_no is Text, converted to Long.
'***********************************************
If Len(Nz(Me.StrItm, vbNullString)) = 0 Then
If Len(Nz(Me.EndItm, vbNullString)) > 0 Then 'End, no start
strWhere = strWhere & "(CLng(sa_lin.item_no) <= " & CLng(Me.EndItm) & "

AND "
End If
Else
If Len(Nz(Me.EndItm, vbNullString)) = 0 Then 'Start, no end
strWhere = strWhere & "(CLng(sa_lin.item_no) >= " & _
CLng(Me.StrItm) & "

AND "
Else 'Both start and end
strWhere = strWhere & "(CLng(sa_lin.item_no) Between " & _
CLng(Me.StrItm) & " And " & CLng(Me.EndItm) & "

AND "
End If
End If
'***********************************************
'Starting and ending DATES: sa_Hdr.post_dat is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "(sa_hdr.post_dat Between """ & Format(Me.StrDat, "yyyymmdd"

& """ And """ & Format(Me.EndDat, "yyyymmdd"

& """

AND "
Else 'Start, no end
strWhere = strWhere & "(sa_hdr.post_dat >= """ & Format(Me.StrDat, "yyyymmdd"

& """

AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "(sa_hdr.post_dat <= """ & Format(Me.EndDat, "yyyymmdd"

& """

AND "
End If
End If
'***********************************************
'ZIP code range: cust.zip_cod is Text.
'***********************************************
If Len(Nz(Me.StrZip, vbNullString)) = 0 Then
If Len(Nz(Me.EndZip, vbNullString)) > 0 Then 'End, no start
strWhere = strWhere & "(Trim(cust.zip_cod) <= """ & Me.EndZip & """

AND "
End If
Else
If Len(Nz(Me.EndZip, vbNullString)) = 0 Then 'Start, no end
strWhere = strWhere & "(Trim(cust.zip_cod) >= """ & Me.StrZip & """

AND "
Else 'Both start and end
strWhere = strWhere & "(Trim(cust.zip_cod) Between """ & Me.StrZip & """ And """ & Me.EndZip & """

AND "
End If
End If
'***********************************************
'SALE AMOUNT range: cust.bal is Decimal (16,2).
'***********************************************
If IsNull(Me.StrSalAmt) Then
If Not IsNull(Me.EndSalAmt) Then 'End, no start.
strWhere = strWhere & "(cust.bal <= " & Me.EndSalAmt & "

AND "
End If
Else
If IsNull(Me.EndSalAmt) Then 'Start, no end.
strWhere = strWhere & "(cust.bal >= " & Me.StrSalAmt & "

AND "
Else 'Both start and end.
strWhere = strWhere & "(cust.bal Between " & Me.StrSalAmt & " And " & Me.EndSalAmt & "

AND "
End If
End If
'***********************************************
'CATEGORY: cust.cat is Text.
'***********************************************
If Len(Nz(Me.CusCat, vbNullString)) > 0 Then
strWhere = strWhere & "(cust.cat = """ & Me.CusCat & """

AND "
End If
'***********************************************
'EMAIL: cust.email_adrs is Text.
'***********************************************
If Me.ExcCustChk.Value Then
strWhere = strWhere & " (NOT (cust.email_adrs Is Null OR cust.email_adrs = """"

) AND "
End If
'***********************************************
'Combine this WHERE clause with the stub and tail of the SQL statement, and assign to query.
'***********************************************
'Remove trailing " AND ", and add " WHERE "
lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen) & vbCrLf
End If
'Set the SQL property of the saved query.
DBEngine(0)(0).QueryDefs(strcQueryName).SQL = strcStub & strWhere & strcTail
CustQuery = True
Exit_Handler:
Exit Function
CustQueryError:
MsgBox "Error " & Err.Number & " (" & Err.Description & "

", vbCritical
Resume Exit_Handler
End Function
Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdOk_Click()
Dim strFile As String 'Name of outputfile
Dim bSuccess As Boolean
strFile = "c:\guystest" & Format(Now(), "mmddyyyyhhnn"

& ".txt"
'Write the SQL statement.
If CustQuery() Then
Select Case Me.grpOuputType
Case Me.optPrintReport.OptionValue
DoCmd.OpenReport strcReportName, acViewPreview
bSuccess = True
Case Me.optCreateFile.OptionValue
DoCmd.TransferText acExportDelim, , strcQueryName, strFile
MsgBox "File created: " & vbCrLf & strFile
bSuccess = True
Case Me.optBoth.OptionValue
DoCmd.OpenReport strcReportName, acViewPreview
DoCmd.TransferText acExportDelim, , strcQueryName, strFile
MsgBox "File created: " & vbCrLf & strFile
bSuccess = True
Case Else
MsgBox "Output type not recognised."
End Select
End If
If bSuccess Then
Call cmdCancel_Click
End If
End Sub
***********************************************************