Yet another error - Object doesn't support this property or method

[D Burke]

Hi,

Working hard tonight and came upon another error when trying to build a
dynamic SQL statement using BETWEEN 'startdate' AND 'enddate'. My
'startdate' is being entered by the user in a text box with a 'medium date'
format (which is what the field in the table is formatted to). I am
receiving an 'Object doesn't support this property or method' error on the
'startdate' line and assume it would be the same for the 'enddate' if I
could get there

Here is the line of code where it breaks:

If Forms!OpenCloseAccountFilter!StartDate.ItemsSelected.Count > 0 Then

Any thoughts or suggestions as to how to make this work?

Thanks!!!

 

[Duane Hookom]

A text box doesn't have ItemsSelected.Count. Try using:

If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then

 

[D Burke]

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) & ")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my application - but I'm
not 100% sure of how to make it work with just the text box. Could I just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!

 

[D Burke]

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) & ")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my application - but I'm
not 100% sure of how to make it work with just the text box. Could I just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!

 

[Duane Hookom]

I wouldn't ever delete the querydef. Just change its SQL property. Then set
the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you get an
error if you attempt to open it? Have you ever set a Break Point to stop the
code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

--
Duane Hookom
MS Access MVP

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

***

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

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

 

[D Burke]

Hi Duane,

I'm a complete amateur and am not sure how to do ANY of what you have noted
below (admitting to being an Access imbicile...).

I'm so confused as to why this isn't working as I have the same code in
another form and its working fine. I'm also confused as it worked once -
very peculiar!

1) How do I change the querydef's SQL property & set the qdf to nothing.

2) I'm not sure what it looks like in the design view as I'm not sure how to
get there.

I have been reviewing the SQL in a message box and it seems to look okay.
So - I'm really confused now!

I'm so sorry to be so thick, but this one is baffling me. I thought I had it
when I changed the BETWEEN portion of the statement and it worked that one
time.

Thanks!

I wouldn't ever delete the querydef. Just change its SQL property. Then set
the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you get an
error if you attempt to open it? Have you ever set a Break Point to stop the
code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

--
Duane Hookom
MS Access MVP

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

***

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


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

&

"

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)

&
")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my

application -
but

I'm
not 100% sure of how to make it work with just the text box. Could I
just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!


A text box doesn't have ItemsSelected.Count. Try using:

If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then

--
Duane Hookom
MS Access MVP


Hi,

Working hard tonight and came upon another error when trying to
build
a
dynamic SQL statement using BETWEEN 'startdate' AND 'enddate'. My
'startdate' is being entered by the user in a text box with a
'medium
date'
format (which is what the field in the table is formatted to).

I
am

receiving an 'Object doesn't support this property or method' error
on
the
'startdate' line and assume it would be the same for the 'enddate'
if
I
could get there

Here is the line of code where it breaks:

If Forms!OpenCloseAccountFilter!StartDate.ItemsSelected.Count

0

Then

Any thoughts or suggestions as to how to make this work?

Thanks!!!

 

[D Burke]

I checked the code in the Query SQL view and the SQL statement works fine.
Now I'm really baffled!

Hi Duane,

I'm a complete amateur and am not sure how to do ANY of what you have noted
below (admitting to being an Access imbicile...).

I'm so confused as to why this isn't working as I have the same code in
another form and its working fine. I'm also confused as it worked once -
very peculiar!

1) How do I change the querydef's SQL property & set the qdf to nothing.

2) I'm not sure what it looks like in the design view as I'm not sure how to
get there.

I have been reviewing the SQL in a message box and it seems to look okay.
So - I'm really confused now!

I'm so sorry to be so thick, but this one is baffling me. I thought I had it
when I changed the BETWEEN portion of the statement and it worked that one
time.

Thanks!

I wouldn't ever delete the querydef. Just change its SQL property. Then set
the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you get an
error if you attempt to open it? Have you ever set a Break Point to stop the
code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

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

***

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


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)

&
")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my application -
but
I'm
not 100% sure of how to make it work with just the text box.

Could

I 'enddate'.
My

to).

I Forms!OpenCloseAccountFilter!StartDate.ItemsSelected.Count

0

 

[D Burke]

Hi,

I can't imagine that this is THIS difficult - but I'm still having problems.
I did the following:

1) I commented out the following section of code:

and then I changed the following section from:

to the following:

MsgBox strSQL
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = 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

Having said that, I am still getting the same 'You cancelled the previous
operation message' and the code stops on the following line:

If DCount("*", "OpenCloseAccountFilter") > 0 Then

I'm so confused as to why this code works on the other form I have, but
doesn't on this one. Arrrrggggghhhhhh!

Thanks Duane!

Cheers,
Darlene

1) you delete the querydef early in your code and then recreate it later. I
would not delete it
after you have your strSQL built, replace your CreateQueryDef.... with
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = strSQL
2) apparently you found your query and looked at the SQL.


--
Duane Hookom
MS Access MVP

Hi Duane,

I'm a complete amateur and am not sure how to do ANY of what you have noted
below (admitting to being an Access imbicile...).

I'm so confused as to why this isn't working as I have the same code in
another form and its working fine. I'm also confused as it worked once -
very peculiar!

1) How do I change the querydef's SQL property & set the qdf to nothing.

2) I'm not sure what it looks like in the design view as I'm not sure

how

to
get there.

I have been reviewing the SQL in a message box and it seems to look okay.
So - I'm really confused now!

I'm so sorry to be so thick, but this one is baffling me. I thought I

had

it
when I changed the BETWEEN portion of the statement and it worked that one
time.

Thanks!

I wouldn't ever delete the querydef. Just change its SQL property.

Then
set

the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you get an
error if you attempt to open it? Have you ever set a Break Point to

stop
the

code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

--
Duane Hookom
MS Access MVP


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

***

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


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)
&
")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my application -
but
I'm
not 100% sure of how to make it work with just the text box.

Could

I
just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!


A text box doesn't have ItemsSelected.Count. Try using:

If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then

--
Duane Hookom
MS Access MVP


Hi,

Working hard tonight and came upon another error when

trying
to

build
a
dynamic SQL statement using BETWEEN 'startdate' AND 'enddate'.
My
'startdate' is being entered by the user in a text box

with

a to).

 

[D Burke]

Hi,

I should also note that when I don't select anything in the 'OpenClosed' and
'AccountType' fields (i.e. when the start and end dates should be the only
criteria in which the results should be returned) I get a division by zero
error again breaking at the DCount line.

Cheers,
Darlene

Hi,

I can't imagine that this is THIS difficult - but I'm still having problems.
I did the following:

1) I commented out the following section of code:

and then I changed the following section from:

to the following:

MsgBox strSQL
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = 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

Having said that, I am still getting the same 'You cancelled the previous
operation message' and the code stops on the following line:

If DCount("*", "OpenCloseAccountFilter") > 0 Then

I'm so confused as to why this code works on the other form I have, but
doesn't on this one. Arrrrggggghhhhhh!

Thanks Duane!

Cheers,
Darlene

1) you delete the querydef early in your code and then recreate it

later.

I
would not delete it
after you have your strSQL built, replace your CreateQueryDef.... with
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = strSQL
2) apparently you found your query and looked at the SQL.


--
Duane Hookom
MS Access MVP

Hi Duane,

I'm a complete amateur and am not sure how to do ANY of what you have noted
below (admitting to being an Access imbicile...).

I'm so confused as to why this isn't working as I have the same code in
another form and its working fine. I'm also confused as it worked once -
very peculiar!

1) How do I change the querydef's SQL property & set the qdf to nothing.

2) I'm not sure what it looks like in the design view as I'm not sure

how

to
get there.

I have been reviewing the SQL in a message box and it seems to look okay.
So - I'm really confused now!

I'm so sorry to be so thick, but this one is baffling me. I thought I

had

it
when I changed the BETWEEN portion of the statement and it worked that one
time.

Thanks!

I wouldn't ever delete the querydef. Just change its SQL property. Then
set
the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you get an
error if you attempt to open it? Have you ever set a Break Point to stop
the
code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

--
Duane Hookom
MS Access MVP


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

***

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


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)
&
")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my
application -
but
I'm
not 100% sure of how to make it work with just the text box. Could
I
just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!


A text box doesn't have ItemsSelected.Count. Try using:

If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then

--
Duane Hookom
MS Access MVP


Hi,

Working hard tonight and came upon another error when trying
to
build
a
dynamic SQL statement using BETWEEN 'startdate' AND 'enddate'.
My
'startdate' is being entered by the user in a text box

with

a
'medium
date'
format (which is what the field in the table is

formatted
to).

I
am
receiving an 'Object doesn't support this property or method'
error
on
the
'startdate' line and assume it would be the same for the
'enddate'
if
I
could get there

Here is the line of code where it breaks:

If Forms!OpenCloseAccountFilter!StartDate.ItemsSelected.Count

0
Then

Any thoughts or suggestions as to how to make this work?

Thanks!!!

 

[Duane Hookom]

Going way back to your code:
strSQL = strSQL & "WHERE " & strWhereOpenClosed & " AND " & _
strWhereAccountType & " BETWEEN " & Me.StartDate & _
" AND " & Me.EndDate
1) aren't you missing a date field name
2) always place #s in your criteria around dates

strSQL = strSQL & "WHERE " & strWhereOpenClosed & " AND " & _
strWhereAccountType & " [DateField] BETWEEN #" & Me.StartDate &
_
"# AND #" & Me.EndDate & "#"
Use Debug.Print strSQL so that you can find the proper syntax.

--
Duane Hookom
MS Access MVP

Hi,

I should also note that when I don't select anything in the 'OpenClosed' and
'AccountType' fields (i.e. when the start and end dates should be the only
criteria in which the results should be returned) I get a division by zero
error again breaking at the DCount line.

Cheers,
Darlene

Hi,

I can't imagine that this is THIS difficult - but I'm still having problems.
I did the following:

1) I commented out the following section of code:

' 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

and then I changed the following section from:

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

to the following:

MsgBox strSQL
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = 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

Having said that, I am still getting the same 'You cancelled the previous
operation message' and the code stops on the following line:

If DCount("*", "OpenCloseAccountFilter") > 0 Then

I'm so confused as to why this code works on the other form I have, but
doesn't on this one. Arrrrggggghhhhhh!

Thanks Duane!

Cheers,
Darlene

1) you delete the querydef early in your code and then recreate it

later.

I
would not delete it
after you have your strSQL built, replace your CreateQueryDef.... with
Set qdf = dbs.QueryDefs("OpenCloseAccountFilter")
qdf.SQL = strSQL
2) apparently you found your query and looked at the SQL.


--
Duane Hookom
MS Access MVP


Hi Duane,

I'm a complete amateur and am not sure how to do ANY of what you have
noted
below (admitting to being an Access imbicile...).

I'm so confused as to why this isn't working as I have the same code in
another form and its working fine. I'm also confused as it worked once -
very peculiar!

1) How do I change the querydef's SQL property & set the qdf to nothing.

2) I'm not sure what it looks like in the design view as I'm not

sure
how

to
get there.

I have been reviewing the SQL in a message box and it seems to look okay.
So - I'm really confused now!

I'm so sorry to be so thick, but this one is baffling me. I thought

I
had

it
when I changed the BETWEEN portion of the statement and it worked

that
one

time.

Thanks!

I wouldn't ever delete the querydef. Just change its SQL property. Then
set
the qdf to Nothing. Only after that, try the DCount( ). After finishing
your code, what does your query look like in design view? Do you

get
an

error if you attempt to open it? Have you ever set a Break Point

to
stop

the
code so that you could capture the SQL in the immediate window and then
paste it into the sql view of a new, blank query?

--
Duane Hookom
MS Access MVP


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

***

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


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)
&
")"
Else
strWhereEndDate = "(1=1)"
End If

You see, I copied the code from another section of my
application -
but
I'm
not 100% sure of how to make it work with just the text box.
Could
I
just
make strWhereEndDate = Forms!OpenCloseAccountFilter!EndDate?

Thanks!


A text box doesn't have ItemsSelected.Count. Try using:

If Not IsNull(Forms!OpenCloseAccountFilter!StartDate) Then

--
Duane Hookom
MS Access MVP


Hi,

Working hard tonight and came upon another error when trying
to
build
a
dynamic SQL statement using BETWEEN 'startdate' AND
'enddate'.
My
'startdate' is being entered by the user in a text box with
a
'medium
date'
format (which is what the field in the table is formatted
to).
I
am
receiving an 'Object doesn't support this property or
method'
error
on
the
'startdate' line and assume it would be the same for the
'enddate'
if
I
could get there

Here is the line of code where it breaks:

If
Forms!OpenCloseAccountFilter!StartDate.ItemsSelected.Count

0
Then

Any thoughts or suggestions as to how to make this work?

Thanks!!!