I am trying to build a dynamic Query from a Form.

I keep getting an error that reads ‘Object qryFilter already exists’

I suspect it has something to do with the string of dates being passed to
the Query;
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"

I am trying to add a means for a user to Query by Customer and Trader AND
all records between two dates. This was working fine for Customer and
Trader; when I added in the code to filter by dates I started having
problems.

I know the SQL will be like this:
SELECT Trades.TDATE, *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));

That’s what I have to pass from the VBA; just not sure how to tell the VBA
to convert to that SQL.


Here is all my code:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdRun_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant

Dim strDateCondition As String
Dim strCust As String
Dim strTrader As String
Dim strCustCondition As String
Dim strTraderCondition As String
Dim strSQL As String

' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM Trades"
cat.Views.Append "qryFilter", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
'Build criteria for string Date
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"

' Build criteria string for Customer
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If

' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get Department condition
If Me.optAndTrader.Value = True Then
strCustCondition = " AND "
Else
strCustCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE strDateCondition And [Trades].[Cust] " & strCust & _
strCustCondition & "[Trades].[Trader] " & strTrader & _
strTraderCondition & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryFilter").Command
cmd.CommandText = strSQL
Set cat.Views("qryFilter").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "qryFilter"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

Private Sub optAndTrader_Click()
' Toggle option buttons
If Me.optAndTrader.Value = True Then
Me.optOrTrader.Value = False
Else
Me.optOrTrader.Value = True
End If
End Sub

Private Sub optOrTrader_Click()
' Toggle option buttons
If Me.optOrTrader.Value = True Then
Me.optAndTrader.Value = False
Else
Me.optAndTrader.Value = True
End If
End Sub

Thanks for any/all help with this!!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Do you have a saved query named "qryFilter"? If so, then it is probably this
line that is causing the problem.

cat.Views.Append "qryFilter", cmd

--
Lynn Trapp
MCP, MOS, MCAS


"ryguy7272" wrote:

> I am trying to build a dynamic Query from a Form.
>
> I keep getting an error that reads ‘Object qryFilter already exists’
>

Thanks Lynn! I commented out that one line, and now I get this Error:
Item cannot be found in the collection corresponding to the requested name
or ordinal.

I just found this site:
http://allenbrowne.com/ser-71.html

I ran the code and it gave me this:
strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
"FROM Trades " & vbCrLf & _
"WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));"

That certainly seems right, going from SQL to VBA. I popped this into my
VBA code, and it still didn’t work.

I undid Lynn’s recommendation, and I get the same old error,
‘Object ‘qryFilter’ already exists’

I think the strSql is right now, but somehow I think I have to Dim a string
for Dates and pass that to the strSql. In the Access-Queries group, Daryl
gave me this:
Dim strDateCondition As String
strDateCondition = " AND Trades.Tdate Between #" &
[forms]![SearchForm]![cboFrom] & "# AND #" & _
[forms]![SearchForm]![cboTo] & "# "

It may be something like that, but I'm not sure.

Still stuck. . .

Any ideas on how to resolve this?

Thanks!
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Lynn Trapp" wrote:

> Do you have a saved query named "qryFilter"? If so, then it is probably this
> line that is causing the problem.
>
> cat.Views.Append "qryFilter", cmd
>
> --
> Lynn Trapp
> MCP, MOS, MCAS
>
>
> "ryguy7272" wrote:
>
> > I am trying to build a dynamic Query from a Form.
> >
> > I keep getting an error that reads ‘Object qryFilter already exists’
> >
>

You do need to concatenate the date values into the string.

For an example of how to build the string, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

It illustrates how to:
a) test that there are date values in the text boxes
b) filter correctly if the user entered only a starting date, or only an
ending date.
c) format the filter string so it works correctly in any date format (not
just US.)

This article contains a free sample database that illustrates how to build
the filter string for a combination of date range, number fields, text
fields, and so on:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It's worth downloading and pulling apart. The code is designed so it's easy
to pull apart and add more filter items as needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"ryguy7272" <(E-Mail Removed)> wrote in message
news:CB6A6CE6-6666-4669-A404-(E-Mail Removed)...
> Thanks Lynn! I commented out that one line, and now I get this Error:
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
>
> I just found this site:
> http://allenbrowne.com/ser-71.html
>
> I ran the code and it gave me this:
> strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
> "FROM Trades " & vbCrLf & _
> "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> ((Trades.Trader) Like '*'));"
>
> That certainly seems right, going from SQL to VBA. I popped this into my
> VBA code, and it still didn’t work.
>
> I undid Lynn’s recommendation, and I get the same old error,
> ‘Object ‘qryFilter’ already exists’
>
> I think the strSql is right now, but somehow I think I have to Dim a
> string
> for Dates and pass that to the strSql. In the Access-Queries group, Daryl
> gave me this:
> Dim strDateCondition As String
> strDateCondition = " AND Trades.Tdate Between #" &
> [forms]![SearchForm]![cboFrom] & "# AND #" & _
> [forms]![SearchForm]![cboTo] & "# "
>
> It may be something like that, but I'm not sure.
>
> Still stuck. . .
>
> Any ideas on how to resolve this?
>
> Thanks!
> Ryan---
>
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Lynn Trapp" wrote:
>
>> Do you have a saved query named "qryFilter"? If so, then it is probably
>> this
>> line that is causing the problem.
>>
>> cat.Views.Append "qryFilter", cmd
>>
>> --
>> Lynn Trapp
>> MCP, MOS, MCAS
>>
>>
>> "ryguy7272" wrote:
>>
>> > I am trying to build a dynamic Query from a Form.
>> >
>> > I keep getting an error that reads ‘Object qryFilter already exists’
>> >
>>

Thanks Allen! I know you are the master with this stuff. I tried the code
from here:
http://allenbrowne.com/casu-08.html

I got it working in my Report, but all it does now is filter for dates, the
other two elements are not being filtered for. I’m not sure how to modify
your code to make the Report filter dynamically. Also, I really want this
code to work in a Query. I’m using another technique for the Report, and
that is almost done; I think I can figure it out pretty soon. What I really
need help with is this Query. I may keep the stored Query, or drop it and
create a new one with SQL. What do you think? Either way, I have to pass
VBA code to SQL.

This is the SQL that works:
SELECT Trades.TDATE, *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));

I just found this on your site:
http://allenbrowne.com/ser-71.html

I ran the code and it converted the SQL to VBA:
strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
"FROM Trades " & vbCrLf & _
"WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));"

Yeah!! That is really cool!!

That certainly seems right, going from SQL to VBA. So, I popped this into my
VBA code, but it still didn’t work.

I think the strSql is right now, but somehow I think I have to Dim a string
for Dates and pass that to the strSql.

I guess it should be something like this:
Dim strDateCondition As String

strDateCondition = " AND Trades.Tdate Between #" &
[forms]![SearchForm]![cboFrom] & "# AND #" & _
[forms]![SearchForm]![cboTo] & "# "

Or something like Me.cboFrom and Me.cboTo, which I know work in the code
behind the Form, but since these variables are being passed to a Query, which
is outside of the Form, I’m not sure if you can use the Me-technique.

Anyway, how can I do a Dimed date-range and pass that from VBA to SQL?

Thanks!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Allen Browne" wrote:

> You do need to concatenate the date values into the string.
>
> For an example of how to build the string, see Method 2 in this article:
> Limiting a Report to a Date Range
> at:
> http://allenbrowne.com/casu-08.html
>
> It illustrates how to:
> a) test that there are date values in the text boxes
> b) filter correctly if the user entered only a starting date, or only an
> ending date.
> c) format the filter string so it works correctly in any date format (not
> just US.)
>
> This article contains a free sample database that illustrates how to build
> the filter string for a combination of date range, number fields, text
> fields, and so on:
> Search form - Handle many optional criteria
> at:
> http://allenbrowne.com/ser-62.html
> It's worth downloading and pulling apart. The code is designed so it's easy
> to pull apart and add more filter items as needed.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:CB6A6CE6-6666-4669-A404-(E-Mail Removed)...
> > Thanks Lynn! I commented out that one line, and now I get this Error:
> > Item cannot be found in the collection corresponding to the requested name
> > or ordinal.
> >
> > I just found this site:
> > http://allenbrowne.com/ser-71.html
> >
> > I ran the code and it gave me this:
> > strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
> > "FROM Trades " & vbCrLf & _
> > "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> > [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> > ((Trades.Trader) Like '*'));"
> >
> > That certainly seems right, going from SQL to VBA. I popped this into my
> > VBA code, and it still didn’t work.
> >
> > I undid Lynn’s recommendation, and I get the same old error,
> > ‘Object ‘qryFilter’ already exists’
> >
> > I think the strSql is right now, but somehow I think I have to Dim a
> > string
> > for Dates and pass that to the strSql. In the Access-Queries group, Daryl
> > gave me this:
> > Dim strDateCondition As String
> > strDateCondition = " AND Trades.Tdate Between #" &
> > [forms]![SearchForm]![cboFrom] & "# AND #" & _
> > [forms]![SearchForm]![cboTo] & "# "
> >
> > It may be something like that, but I'm not sure.
> >
> > Still stuck. . .
> >
> > Any ideas on how to resolve this?
> >
> > Thanks!
> > Ryan---
> >
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Lynn Trapp" wrote:
> >
> >> Do you have a saved query named "qryFilter"? If so, then it is probably
> >> this
> >> line that is causing the problem.
> >>
> >> cat.Views.Append "qryFilter", cmd
> >>
> >> --
> >> Lynn Trapp
> >> MCP, MOS, MCAS
> >>
> >>
> >> "ryguy7272" wrote:
> >>
> >> > I am trying to build a dynamic Query from a Form.
> >> >
> >> > I keep getting an error that reads ‘Object qryFilter already exists’
> >> >
> >>
> .
>

I didn't follow the bit about why you "really need to this in the query"
*instead* of in VBA code.

In any case, if you want to do that, make sure you:
1. Declare the parameters in your query (Parameters on the toolbar in query
design), so JET knows their data type.

2. If the combo is unbound, set its Format property to General Date or
similar, so Access knows its data type.+

IMHO, this still gets too messy when you try to combine other values.

I take it you did not try the example at:
http://allenbrowne.com/ser-62.html
that shows how to work with a date range plus a combo plus other filters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"ryguy7272" <(E-Mail Removed)> wrote in message
news:3A5008D9-F712-4E9B-90A9-(E-Mail Removed)...
> Thanks Allen! I know you are the master with this stuff. I tried the
> code
> from here:
> http://allenbrowne.com/casu-08.html
>
> I got it working in my Report, but all it does now is filter for dates,
> the
> other two elements are not being filtered for. I’m not sure how to modify
> your code to make the Report filter dynamically. Also, I really want this
> code to work in a Query. I’m using another technique for the Report, and
> that is almost done; I think I can figure it out pretty soon. What I
> really
> need help with is this Query. I may keep the stored Query, or drop it and
> create a new one with SQL. What do you think? Either way, I have to pass
> VBA code to SQL.
>
> This is the SQL that works:
> SELECT Trades.TDATE, *
> FROM Trades
> WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> ((Trades.Trader) Like '*'));
>
> I just found this on your site:
> http://allenbrowne.com/ser-71.html
>
> I ran the code and it converted the SQL to VBA:
> strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
> "FROM Trades " & vbCrLf & _
> "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> ((Trades.Trader) Like '*'));"
>
> Yeah!! That is really cool!!
>
> That certainly seems right, going from SQL to VBA. So, I popped this into
> my
> VBA code, but it still didn’t work.
>
> I think the strSql is right now, but somehow I think I have to Dim a
> string
> for Dates and pass that to the strSql.
>
> I guess it should be something like this:
> Dim strDateCondition As String
>
> strDateCondition = " AND Trades.Tdate Between #" &
> [forms]![SearchForm]![cboFrom] & "# AND #" & _
> [forms]![SearchForm]![cboTo] & "# "
>
> Or something like Me.cboFrom and Me.cboTo, which I know work in the code
> behind the Form, but since these variables are being passed to a Query,
> which
> is outside of the Form, I’m not sure if you can use the Me-technique.
>
> Anyway, how can I do a Dimed date-range and pass that from VBA to SQL?
>
> Thanks!
> Ryan--
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Allen Browne" wrote:
>
>> You do need to concatenate the date values into the string.
>>
>> For an example of how to build the string, see Method 2 in this article:
>> Limiting a Report to a Date Range
>> at:
>> http://allenbrowne.com/casu-08.html
>>
>> It illustrates how to:
>> a) test that there are date values in the text boxes
>> b) filter correctly if the user entered only a starting date, or only an
>> ending date.
>> c) format the filter string so it works correctly in any date format (not
>> just US.)
>>
>> This article contains a free sample database that illustrates how to
>> build
>> the filter string for a combination of date range, number fields, text
>> fields, and so on:
>> Search form - Handle many optional criteria
>> at:
>> http://allenbrowne.com/ser-62.html
>> It's worth downloading and pulling apart. The code is designed so it's
>> easy
>> to pull apart and add more filter items as needed.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "ryguy7272" <(E-Mail Removed)> wrote in message
>> news:CB6A6CE6-6666-4669-A404-(E-Mail Removed)...
>> > Thanks Lynn! I commented out that one line, and now I get this Error:
>> > Item cannot be found in the collection corresponding to the requested
>> > name
>> > or ordinal.
>> >
>> > I just found this site:
>> > http://allenbrowne.com/ser-71.html
>> >
>> > I ran the code and it gave me this:
>> > strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
>> > "FROM Trades " & vbCrLf & _
>> > "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
>> > [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
>> > ((Trades.Trader) Like '*'));"
>> >
>> > That certainly seems right, going from SQL to VBA. I popped this into
>> > my
>> > VBA code, and it still didn’t work.
>> >
>> > I undid Lynn’s recommendation, and I get the same old error,
>> > ‘Object ‘qryFilter’ already exists’
>> >
>> > I think the strSql is right now, but somehow I think I have to Dim a
>> > string
>> > for Dates and pass that to the strSql. In the Access-Queries group,
>> > Daryl
>> > gave me this:
>> > Dim strDateCondition As String
>> > strDateCondition = " AND Trades.Tdate Between #" &
>> > [forms]![SearchForm]![cboFrom] & "# AND #" & _
>> > [forms]![SearchForm]![cboTo] & "# "
>> >
>> > It may be something like that, but I'm not sure.
>> >
>> > Still stuck. . .
>> >
>> > Any ideas on how to resolve this?
>> >
>> > Thanks!
>> > Ryan---
>> >
>> >
>> >
>> > --
>> > Ryan---
>> > If this information was helpful, please indicate this by clicking
>> > ''Yes''.
>> >
>> >
>> > "Lynn Trapp" wrote:
>> >
>> >> Do you have a saved query named "qryFilter"? If so, then it is
>> >> probably
>> >> this
>> >> line that is causing the problem.
>> >>
>> >> cat.Views.Append "qryFilter", cmd
>> >>
>> >> --
>> >> Lynn Trapp
>> >> MCP, MOS, MCAS
>> >>
>> >>
>> >> "ryguy7272" wrote:
>> >>
>> >> > I am trying to build a dynamic Query from a Form.
>> >> >
>> >> > I keep getting an error that reads ‘Object qryFilter already exists’
>> >> >
>> >>
>> .
>>

Thanks Lynn and Allen! I did try this: http://allenbrowne.com/ser-62.html
It worked great, but I didn't know how to modify the code to work in my
project. This is a great resource too: http://allenbrowne.com/ser-71.html
I love that little tool!!

Here is the final version of code:
Private Sub cmdRun_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFilter")

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get condition
If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And (Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ");"

' Open the Query
qdf.SQL = strSQL
DoCmd.OpenQuery "qryFilter"
Set qdf = Nothing
Set db = Nothing
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Allen Browne" wrote:

> I didn't follow the bit about why you "really need to this in the query"
> *instead* of in VBA code.
>
> In any case, if you want to do that, make sure you:
> 1. Declare the parameters in your query (Parameters on the toolbar in query
> design), so JET knows their data type.
>
> 2. If the combo is unbound, set its Format property to General Date or
> similar, so Access knows its data type.+
>
> IMHO, this still gets too messy when you try to combine other values.
>
> I take it you did not try the example at:
> http://allenbrowne.com/ser-62.html
> that shows how to work with a date range plus a combo plus other filters.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:3A5008D9-F712-4E9B-90A9-(E-Mail Removed)...
> > Thanks Allen! I know you are the master with this stuff. I tried the
> > code
> > from here:
> > http://allenbrowne.com/casu-08.html
> >
> > I got it working in my Report, but all it does now is filter for dates,
> > the
> > other two elements are not being filtered for. I’m not sure how to modify
> > your code to make the Report filter dynamically. Also, I really want this
> > code to work in a Query. I’m using another technique for the Report, and
> > that is almost done; I think I can figure it out pretty soon. What I
> > really
> > need help with is this Query. I may keep the stored Query, or drop it and
> > create a new one with SQL. What do you think? Either way, I have to pass
> > VBA code to SQL.
> >
> > This is the SQL that works:
> > SELECT Trades.TDATE, *
> > FROM Trades
> > WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> > [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> > ((Trades.Trader) Like '*'));
> >
> > I just found this on your site:
> > http://allenbrowne.com/ser-71.html
> >
> > I ran the code and it converted the SQL to VBA:
> > strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
> > "FROM Trades " & vbCrLf & _
> > "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> > [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> > ((Trades.Trader) Like '*'));"
> >
> > Yeah!! That is really cool!!
> >
> > That certainly seems right, going from SQL to VBA. So, I popped this into
> > my
> > VBA code, but it still didn’t work.
> >
> > I think the strSql is right now, but somehow I think I have to Dim a
> > string
> > for Dates and pass that to the strSql.
> >
> > I guess it should be something like this:
> > Dim strDateCondition As String
> >
> > strDateCondition = " AND Trades.Tdate Between #" &
> > [forms]![SearchForm]![cboFrom] & "# AND #" & _
> > [forms]![SearchForm]![cboTo] & "# "
> >
> > Or something like Me.cboFrom and Me.cboTo, which I know work in the code
> > behind the Form, but since these variables are being passed to a Query,
> > which
> > is outside of the Form, I’m not sure if you can use the Me-technique.
> >
> > Anyway, how can I do a Dimed date-range and pass that from VBA to SQL?
> >
> > Thanks!
> > Ryan--
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Allen Browne" wrote:
> >
> >> You do need to concatenate the date values into the string.
> >>
> >> For an example of how to build the string, see Method 2 in this article:
> >> Limiting a Report to a Date Range
> >> at:
> >> http://allenbrowne.com/casu-08.html
> >>
> >> It illustrates how to:
> >> a) test that there are date values in the text boxes
> >> b) filter correctly if the user entered only a starting date, or only an
> >> ending date.
> >> c) format the filter string so it works correctly in any date format (not
> >> just US.)
> >>
> >> This article contains a free sample database that illustrates how to
> >> build
> >> the filter string for a combination of date range, number fields, text
> >> fields, and so on:
> >> Search form - Handle many optional criteria
> >> at:
> >> http://allenbrowne.com/ser-62.html
> >> It's worth downloading and pulling apart. The code is designed so it's
> >> easy
> >> to pull apart and add more filter items as needed.
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >>
> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> news:CB6A6CE6-6666-4669-A404-(E-Mail Removed)...
> >> > Thanks Lynn! I commented out that one line, and now I get this Error:
> >> > Item cannot be found in the collection corresponding to the requested
> >> > name
> >> > or ordinal.
> >> >
> >> > I just found this site:
> >> > http://allenbrowne.com/ser-71.html
> >> >
> >> > I ran the code and it gave me this:
> >> > strSql = "SELECT Trades.TDATE, * " & vbCrLf & _
> >> > "FROM Trades " & vbCrLf & _
> >> > "WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
> >> > [Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
> >> > ((Trades.Trader) Like '*'));"
> >> >
> >> > That certainly seems right, going from SQL to VBA. I popped this into
> >> > my
> >> > VBA code, and it still didn’t work.
> >> >
> >> > I undid Lynn’s recommendation, and I get the same old error,
> >> > ‘Object ‘qryFilter’ already exists’
> >> >
> >> > I think the strSql is right now, but somehow I think I have to Dim a
> >> > string
> >> > for Dates and pass that to the strSql. In the Access-Queries group,
> >> > Daryl
> >> > gave me this:
> >> > Dim strDateCondition As String
> >> > strDateCondition = " AND Trades.Tdate Between #" &
> >> > [forms]![SearchForm]![cboFrom] & "# AND #" & _
> >> > [forms]![SearchForm]![cboTo] & "# "
> >> >
> >> > It may be something like that, but I'm not sure.
> >> >
> >> > Still stuck. . .
> >> >
> >> > Any ideas on how to resolve this?
> >> >
> >> > Thanks!
> >> > Ryan---
> >> >
> >> >
> >> >
> >> > --
> >> > Ryan---
> >> > If this information was helpful, please indicate this by clicking
> >> > ''Yes''.
> >> >
> >> >
> >> > "Lynn Trapp" wrote:
> >> >
> >> >> Do you have a saved query named "qryFilter"? If so, then it is
> >> >> probably
> >> >> this
> >> >> line that is causing the problem.
> >> >>
> >> >> cat.Views.Append "qryFilter", cmd
> >> >>
> >> >> --
> >> >> Lynn Trapp
> >> >> MCP, MOS, MCAS
> >> >>
> >> >>
> >> >> "ryguy7272" wrote:
> >> >>
> >> >> > I am trying to build a dynamic Query from a Form.
> >> >> >
> >> >> > I keep getting an error that reads ‘Object qryFilter already exists’
> >> >> >
> >> >>
> >> .
> >>
> .
>