Hi Duane,
Thanks so much for all of your help, time and patience. I read your last
note and the light went on - so I changed the code to be what I have
included below. It worked beautifully the first time I tried it, but now I
seem to be getting other errors and I can't figure out how to resolve them.
The error message I'm getting now is 'You cancelled the previous operation'
and it stops at the following line:
If DCount("*", "OpenCloseAccountFilter") > 0 Then
Arghhh! So close, but yet soooo far!
***
Private Sub ExporttoFile_Click()
Dim frm As Form
Dim ctl As Control
Dim ctl2 As Control
Dim ctl3 As Control
Dim ctl4 As Control
Dim strSQL As String
Dim dbs As Database
Dim varItem As Variant
Dim qdf As QueryDef
' Return reference to current database.
Set dbs = CurrentDb
' Refresh QueryDefs collection.
dbs.QueryDefs.Refresh
' If OpenCloseExport query exists, delete it.
For Each qdf In dbs.QueryDefs
If qdf.Name = "OpenCloseAccountFilter" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf
'Create a SQL statement using a parameter
Set frm = Forms!OpenCloseAccountFilter
Set ctl = frm!OpenClosed
Set ctl2 = frm!StartDate
Set ctl3 = frm!EndDate
Set ctl4 = frm!AccountType
Set dbs = CurrentDb
Dim strWhereOpenClosed As String
Dim strWhereStartDate As String
Dim strWhereEndDate As String
Dim strWhereAccountType As String
Dim boolWhereInserted As Boolean
strSQL = "SELECT [Prospect Client Records].Title, [Prospect Client
Records].FirstName, [Prospect Client Records].MiddleInitial, [ Prospect
Client Records].LastName, [Address Information].CompanyName, [Address
Information].Address1, [Address Information].Address2, [Address
Information].City, [Address Information].State, [Address
Information].Postal, [Address Information].Country " & _
"FROM [Prospect Client Records] INNER JOIN [Address Information] " & _
"ON [Prospect Client Records].IDNumber = [Address Information].IDNumber "
boolWhereInserted = False
If Forms!OpenCloseAccountFilter!OpenClosed.ItemsSelected.Count > 0 Then
strWhereOpenClosed = "[Prospect Client Records].OpenClosed IN("
For Each varItem In
Forms!OpenCloseAccountFilter!OpenClosed.ItemsSelected
strWhereOpenClosed = strWhereOpenClosed & "'" &
ctl.ItemData(varItem) & "', "
Next varItem
'remove final comma/space and add ")"
strWhereOpenClosed = Left(strWhereOpenClosed, Len(strWhereOpenClosed) -
2) & ")"
Else
strWhereOpenClosed = "(1=1)"
End If
If Forms!OpenCloseAccountFilter!AccountType.ItemsSelected.Count > 0 Then
strWhereAccountType = "[Prospect Client Records].AccountType IN("
For Each varItem In
Forms!OpenCloseAccountFilter!AccountType.ItemsSelected
strWhereAccountType = strWhereAccountType & "'" &
ctl4.ItemData(varItem) & "', "
Next varItem
'remove final comma/space and add ")"
strWhereAccountType = Left(strWhereAccountType,
Len(strWhereAccountType) - 2) & ")"
Else
strWhereAccountType = "(1=1)"
End If
'then concatenate final SQL
strSQL = strSQL & "WHERE " & strWhereOpenClosed & " AND " & _
strWhereAccountType & " BETWEEN " & Me.StartDate & _
" AND " & Me.EndDate
MsgBox strSQL
Set qdf = dbs.CreateQueryDef("OpenCloseAccountFilter", strSQL)
If DCount("*", "OpenCloseAccountFilter") > 0 Then
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatRTF,
"OpenCloseExport.rtf", True
Else
MsgBox "No records match your selection(s)."
End If
End Sub
***
Duane Hookom said:
You are working way too hard on this. You should never need to use " IN( "
when dealing with a single value. For date values use "[field]=#" & ctrl &
"# "
Is this code running in the form OpenCloseAccountFilter? If so, replace all
the "Forms!OpenCloseAccountFilter!" with "Me."
Your code finds one date to match with DateOfEntry and one date to match
ACCloseDate. Is this really what you want or do you want to specify a range
of dates?
Duane Hookom
MS Access MVP
D Burke said:
Hi,
I played around and solved the problem I noted below, but am now
getting
an
error message with the query that is built. Here is the code I used to
resolve the issue below:
'do the same for other 2 WHERE vars
If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then
strWhereStartDate = "[Prospect Client Records].DateofEntry IN(" & _
"'" & ctl2 & "', "
'remove final comma/space and add ")"
strWhereStartDate = Left(strWhereStartDate,
Len(strWhereStartDate) -
2)
& ")"
Else
strWhereStartDate = "(1=1)"
End If
If Not IsNull(Forms!OpenCloseAccountFilter!EndDate) Then
strWhereEndDate = "[Prospect Client Records].ACCloseDate IN(" & _
"'" & ctl3 & "', "
'remove final comma/space and add ")"
strWhereEndDate = Left(strWhereEndDate, Len(strWhereEndDate) - 2)
&
")"
Else
strWhereEndDate = "(1=1)"
End If
This is giving me the correct parameters for the startdate and the enddate.
However, when I run it I am getting an error message indicating that:
'Between operator without And in query expression '(1=1) AND (1=1) BETWEEN
[Prospect Client Records].DateofEntry IN ('1/1/00') And [Prospect Client
Records].ACCloseDate IN('7/15/03')'.
Here is the section of the code used to build the final string:
strSQL = strSQL & "WHERE " & strWhereOpenClosed & " AND " _
& strWhereAccountType & " BETWEEN " & strWhereStartDate &
"
And
" & strWhereEndDate
I'm stuck - any help would be greatly appreciated!!!
Thanks!!!
Hi,
I changed the code per your comments below, but realized that there is
more
to this than just that... here is my code, you'll see what I mean:
If Not IsNull(Forms!OpenCloseAccountFilter!EndDate) Then
strWhereEndDate = "[Prospect Client Records].ACCloseDate IN("
For Each varItem In Forms!OpenCloseAccountFilter!EndDate.ItemsSelected
strWhereEndDate = strWhereEndDate & "'" &
ctl3.ItemData(varItem)
&
"', "
Next varItem
'remove final comma/space and add ")"
strWhereEndDate = Left(strWhereEndDate, Len(strWhereEndDate) -
2)
& error 'enddate'
if 0
Then
