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" <ryguy7272@discussions.microsoft.com> wrote in message 
news:CB6A6CE6-6666-4669-A404-42EAD8E0C690@microsoft.com...
> 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> news:CB6A6CE6-6666-4669-A404-42EAD8E0C690@microsoft.com...
> > 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" <ryguy7272@discussions.microsoft.com> wrote in message 
news:3A5008D9-F712-4E9B-90A9-6396082E7B05@microsoft.com...
> 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" <ryguy7272@discussions.microsoft.com> wrote in message
>> news:CB6A6CE6-6666-4669-A404-42EAD8E0C690@microsoft.com...
>> > 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> news:3A5008D9-F712-4E9B-90A9-6396082E7B05@microsoft.com...
> > 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" <ryguy7272@discussions.microsoft.com> wrote in message
> >> news:CB6A6CE6-6666-4669-A404-42EAD8E0C690@microsoft.com...
> >> > 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’
> >> >> >
> >> >>
> >> .
> >> 
> .
>