Build Dynamic Query from Form

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''.
0
Utf
1/29/2010 3:43:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

6 Replies
1367 Views

Similar Articles

[PageSpeed] 18

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’
> 

0
Utf
1/29/2010 5:49:01 PM
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’
> > 
> 
0
Utf
1/29/2010 6:14:01 PM
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’
>> >
>> 
0
Allen
1/30/2010 4:00:40 AM
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’
> >> >
> >> 
> .
> 
0
Utf
1/30/2010 7:14:01 PM
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’
>> >> >
>> >>
>> .
>> 
0
Allen
1/31/2010 12:02:22 AM
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’
> >> >> >
> >> >>
> >> .
> >> 
> .
> 
0
Utf
2/2/2010 4:31:01 AM
Reply:

Similar Artilces:

CRM 3.0
Hi All, I am writing a custom report for CRM 3.0 to basically copy the My Activities view but display the regarding and To contacts and associated phone numbers. The report is basically done except for a few small issues. I would like to set up the Dynamic Drill-Through so when the person clicks on the Activity Subject it will open the associated Activity record. Unfortunately I have been unable to find the information needed to use in the following code to set the Object Type Code (OTC) to the correct activity type: = Parameters!CRM_URL.Value & "?ID={"&Fields!Activityid...

Autofill In Linked Forms
I have created a couple of forms by selecting all the fileds in the form wizard from two tables: 1. Suppliers (Fields: Supplier, Address, Contact, etc.) 2. Products (Fields: Supplier <Lookedup from Supplier Table>, Product, Rate, etc.) Upon clicking the toggle button on Suppliers form I have the Products form opened. However, as I enter the data in it, I want the Supplier filed in the same be automatically be filled with the Supplier name as in the opened Supplier form so as to be filtered and displayed the next time I open the same instead of manually inserting the sa...

Can see form in design view but not in open or layout view
I have set up the tables, relationships and forms. When I first created the form I was able to view when clicking open. I made a couple of changes and now when I click on open a box pops up to enter perimeter value. Also, I can no longer see the form in opne or layout but if I click for design view, I can see my form and all the tabs. There is either something missing or somthing is named incorrectly. Had a similar problem recently where a new version was installed in the front end, but the table in the back end had not been updated properly (a new field) and the new version of the...

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

Outlook/Access 2003
I've been doing a lot of research and purchased (but haven't completely read) Sue Mosher's book. However, before I spend much more time I'd like to find out if my idea is even feasible. The basic idea is to create an appointment system for multiple therapists that can be used by a receptionist and other staff. Using Outlook and Exchange is very nice but the appointment data needs to end up in an Access table immediately. We can purchase SBS 2003 with Exchange 2003 or we can buy a third party tool for calendar sharing whichever would be the easiest to use. I currently...

By: deesom In: microsoft.public.dynamics.gp.developer
I do have an unusual situation - and I really do need your help, urgently: I have a gravel road infrasture asset (GRDS-0001 with suffix 1). It has a Net Book Value of $ 1,200,000. This gravel road was paved over at a cost of $ 750,000. The asset is no longer a gravel road, and the transformed asset needs to be classified as a new asset, for example , with an asset ID of PRDS-0001(suffix 1). How would I properly create and record this new asset, with the cost of $ 1,950,000, and still preserve the history of the old asset (GRDS-0001), that no longer exists. I did not feel comfortable ...

change exist chart to be dynamic
Hi all How can I change an existing chart to be dynamic? In series I saw a name in y axis only but no name of x axis. How can I create a series for x axis? Thanks in advance Daniel You need to understand how dynamic charts work. There are tutorials and links to more on this page: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:FF77A61A-58A9-4629-90E1-20742E6...

Keeping forms in order they are entered?
I am working with a database that was designed for us and have pretty much just used without having to change much in but have now run into a problem and hope someone can help. We track and number complaints we recieve by number using last digits of year recieved then next number, i.e. a complaint recived 12/31/09 might be 9234 and first one received this year would be 10001 and the next would be 10002. The problem is now when I open my forms and go to the last one it is the 9234 instead of the 10002 which I need. How do I keep that last form I entered last the next time I open the data...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

increment through controls on form
I have a form that I've name the controls on it in such a way that I could determine through open args if it should be locked or not. Example any text box that begins with txtd would be unlocked with open args of 1. I was hoping there was a way I could just move through the controls in a loop and set that property with one function.....I'm not for sure how to accomplish this though. Does anyone have some sample code on how to "loop" through all the controls on a form and set the locked property? I can write the criteria portion if I could get a sample of how to ...

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

Attach Trigger to Save Function of Form
I am in the processes of trying to attach a trigger to the Save function of an already existing GP form using Dex. In one of the documents, it states that the CM_Checkbook_Maitenance has a SaveRecord function. I am trying to acces the function that is similar to this on the PM_Vendor_Maitenance form. I tried using SaveRecord and when I tried to compile it it says that it is an unknown reference. Is there some documentation with a list of the functions for each form? Mike, PM_Vendor_Maintenance is an older form and probably doesn't have a SaveRecord function. Try running t...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

upload form problem / server question
www.icingpictures.co.uk / win xp / frontpage 2003 Want to create a form with upload file form field - i read on this forum that the server type i use is important - it should be a windows type server with frontpage extensions - i currently use a linux server with front page extensions - do i need to change to a windows server? Thanks for taking a looka t my question. Joyce Yes, you must have a Windows Server, just like you stated you read. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "threademporium" <threademporiu...

Re-exporting functions from dynamically linked DLL
Hi, I have the following situation: I have a 3rd party DLL which does not include a LIB file, just a header file with typedef declarations for exported functions (It is intended for dynamic linking) I need to wrap the DLL to allow for callbacks to my own code (in Lisp), and to do this I need to load the DLL using LoadLibrary and access each function that I need using GetProcAddress. So for example: The third party header will say: typedef INT (WINAPI * lp_function)(); Then in my c wrapper file: #include "3rdparty.h" #ifdef _WIN32 #define DllExport __declspec(dllexport) #else #...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Is batch ID is required in payables transaction entry form?
Is batch ID is required in payables transaction entry form? How about if I just have 1 invoice and do not want to create a batch for it? can I still process the invoice? Hi Bab, A batch i.d. number is not required. You have to post your invoice both in the Payables module and also in the Financial module. I check weekly for any "series batches" that have not been posted in my A/P, A/R, P/R, and Financial modules. If they go unposted it can create havoc. I hope this has been helpful to you. -- Diana "Bab" wrote: > Is batch ID is required in payables transa...

Payroll Build Checks takes almost 4 hours
Sorry, I'm reposting this question because I didnt describe the subject very well... I hope someone has any tips of what to look for... I run Payroll Build Checks for 1369 employees, selecting biweekly and Salary, and it takes almost 4 hours to finish. At the end, only 543 employees fit the selection criteria. My server looks to have plenty of spare capacity when monitored, the store procedures were recompiled, the statistics were updated, check links ran, etc. What else can I check? Any help or tips are greatly appreciated. Details are below. When I click on <...

Fixing coloumn title in datasheet forms
I have a form which has a subform. Is there anyway to fix the length of the column heading in the datasheet view? I can expand it when viewing but when I close the form and open it again it has shrunk back to its smaller size. Thanks Tony Are you referring to the column width? If so check out columnwidth properties in help. Damon "Tony Williams" <tw@invalid.com> wrote in message news:%23RCxCN$4HHA.5844@TK2MSFTNGP02.phx.gbl... >I have a form which has a subform. Is there anyway to fix the length of the >column heading in the datasheet view? I can expand it when...

Dynamic Access Report from filtered Form
I need help. I have succuess fully applied the "how to create a dynamic crosstab report in access" from instructions off of microsofts web site to my own report and query. My problem is this. I have a form in datasheet view, based off a query. When the form is open I can hide / unhid columns as needed and apply filter by form. That all is great. But now I want to beable to send the filtered data on the datasheet form to the Dynamic Report I created showing only the filtered records and also only show the columns that I did not have hidden on my form. How do I do this? I have t...