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
1410 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:

Merging List w/ a Form
Can anyone assist me with merging a form created in Excel with a database (or list) also created in Excel? I know how to merge an Excel worksheet to be used as a database for Word in the Mail Merge command, but my FORM, with which I am trying to merge my database, was created in Excel and needs to remain under its format (not be converted to Word). I am filtering the information I need from the database to be imported to the Excel Form. Any help would be appreciated. Thank-you. If you're trying to display information from one record in the database, there are sample files here...

Dynamics Security Console and Dynamics Web Services
Hi All I need help I have created a server with windows server 2008 32 bit and installed MOSS I have done all the standard setups and created all the websites ect I have installed Dynamics Webservice witth SP3 When I run Dynamics Security Console and select applications and then SecuritySerivices. I get the following error "Could not access the SecurityService service. Unable to connect to the remote server" Please help Regards Laurie ...

dynamic field content of parameter combobox
Greetings, I have to filter my Crystal 9.0 report with a parameter combobox field in Microsoft CRM. The data for this parameter field has to be loaded out of a database field dynamically. I have found the possibility to load database field values in a parameter field but this will not be dynamically. If there is a change in the database content the parameter field content will not be updated. I hope to create a combobox control is no problem. Is there a way to turn this into reality? Thanks for your help Thomas Ott (ITVT germany) ...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Filter Report by Form
I'm using the following code from the wonderful Allen Browne which works perfectly in an either or scenario (either by Start/End Date(s) OR filtering by client name; but I don't know what syntax to use to filter by Start/End date(s) AND anything typed in the client name field. I'm sure it's easy but I can't get my head around it. I appreciate any and all assistance!! Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working. 'Purpose: Filter a report to a date r...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Center Access2007 Form in the middle of access Main Window
Thank You Is there any easy way to center access 2007 forms in the middle of the screen (Monitor) or access main window. and give me good result Hello, I think there is no built in feature to achieve this goal. You may need to create a new form and configure it as the main window. You could enumerate forms and create buttons etc to open forms from this main form. For example, you could use hte following code enumerate forms in your database: Dim db As Database Set db = CurrentDb Set cntContainer = db.Containers("Forms") For Each doc In cntContainer.Documents Str...

Printout Method
In building a form to allow users to print a "blank form" I've constructed as an Access Report, I'd like the user to be able to select the number of copies to print. It looks like the PrintOut Method would be a good choice for this, but I'm not smart enough to know quite how to get the "Copies" argument to get its input from the textbox on my form, where the user will enter the number of copies they'd like to print. Suggestions? -- Thanks, Croy Does the report have a record source? -- Duane Hookom Microsoft Access MVP If I have helped you, please help...

email form script to show email adresses of all recipients
I see there is a field in the attributes called torecipients but I cannot add it to the email form. Is there a way I can script this into the email form to show the email addresses of all of the recipients. My idea involved creting a new attribute called all_recipients and adding it to the form then doing a onchange for each of the to, cc, and bcc fields to update all_recipients. SO far it doesnt work. All the field returns is "undefined". Any help is greatly appreciated!!! ...

Dynamically change pick lists?
Has anyone found a workaround for the following : dynamically change the choices in one pick list based on the selection in a second, associated pick list. I know this question has been asked before, just wondering if someone found a resolution for it. I haven't tried it yet, but it is my understanding that you can write JavaScript code in the OnChange event for the first picklist that will modify the valid values in the second picklist. HTH, Dave >-----Original Message----- >Has anyone found a workaround for the following : > >dynamically change the choices in one pick...

Fill Form And Get $5000 To Your Home.
Fill Form And Get $5000 To Your Home At http://ukcitygirls.co.cc Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque. please don't tell to anyone. ...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

Updated records in forms
Each record in a form is a work order. School, contractor, days of work, etc. I have a pop up calendar to select a week ending date for each record. Record 1's week ending date is Nov 13, 2009. Record 2's week ending date is Nov 20, 2009. When I change the date in record 2, it's changing the date in record 1. Each record's week ending date become all records' week ending date? Can I use a pop up calendar in a record and be able to retain the correct week ending date for each record. If so, how would I be able to accomplish this? Thanks for your inp...

requery subform from sub form
Thank you how can i requery subform from subform ? "a" <youb@hotmail.com> wrote in message news:%23R9c$8tSIHA.2000@TK2MSFTNGP05.phx.gbl... > Thank you > how can i requery subform from subform ? Me.Parent!Subform2ControlName.Form.ReQuery thank you the work correctly "Stuart McCall" <smccall@myunrealbox.com> wrote in message news:fl83eg$3o6$1$8300dec7@news.demon.co.uk... > "a" <youb@hotmail.com> wrote in message > news:%23R9c$8tSIHA.2000@TK2MSFTNGP05.phx.gbl... >> Thank you >> how can i requery subform from subfo...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

union queries
I need to build a query which combine records from a number of similar tables. Building a union query works great with "normal" records. Problem is, these tables contain attachments in some fields and MS ACCESS 2007 treat these fields as multi-value fields and does not allow building APPEND or UNION queries with multi-value fields. I am so frustrated with this, because I would like to base my searches and some other queries on this. PLEEEESe help Cheers Dawie Theron On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron <DawieTheron@discussions.microsoft.com&g...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...

Date field behavior differs between forms
I have two forms with seemingly identical date fields. In one, if I place my cursor in the middle of it, the first number I type gets put in the far left of the field. In the field on the other form, if I place the cursor in the middle, it starts typing right where I am. Any ideas? I have thoroughly looked through the properties of each field and each form and I cannot find what is causing this behavior. I would love to get both of them to start placing typed characters at the far left instead of where the cursor is. Thanks! Check the Text Alignment property of both controls (not ...

Conditional Formatting In a Datasheet Form
Hi All, I am trying to “Condition Format” a column on my form so it will highlight the individual cell if another cell in the same column has the same value. The form is in Datasheet view and it can not be changed from that due to the way I built the database. I have tried to build the condition format like so, Condition1 “Field Value Is” “equal to” [Column Name] and all it dose is it highlight the cell in the column when I input a record into it, regardless if it is a duplicate or not. Is there a way to accomplish this? Thank you for your time, Cage On Thu, 1 Mar 2007 10:34:06 -...

2 queries, a date range and a report?!
I'm pretty much a novice access user, and am designing my DB with the frequent assistance of these forums. However I'm now trying to do something that I can't find any reference to - Can anyone advise? I have a report that is to be printed from a form using a print control button. The report consists of 2 queries which look up 2 different types of "item" by date range. I want to be able to prompt the user once for the start and end date. If I put a start/end request on both of the queries, then the user has to enter the dates twice, and when you choose...

relative references when copying Sheets containing form controls
I've been given a spreadsheet that I need to take copy various sheets from, the sheets contain form controls, when I copy the sheet to a new workbook, named ranges and so on come across fine, but the references to the input ranges of for example the drop down box controls become hard coded to the original sheet, I don't want this.. Is there anyway to alter the way excel copies so that the references remain relative rather than absoloute???? What did you include in the addresses for those references? I put a listbox from the forms toolbar on a worksheet. If I used a range like: $b$...

Wretched Queries!!!!!!
Hi,In my database each entry has several scores assigned to it (fordifferent attributes). Each score is in the 1 -4 range.I would like to construct a query that will select those entries thathave even so much as a single attribute that scores below a 4 and, ifpossible, only return the attributes that fell below standard (4).The database will have each employee (entry) assessed on a dozen or sodifferent attributes, each of which will be rated with a score of 1 -4. I would like to be able to find out which employees had a score inany attribute below 4 and which attributes those were, and only th...

Some queries of User Interface Thread
Hello All Recently I started working on User Interface thread , But certain things are quite confusing. Hope I will get some clarification from all of you. 1: The thread does not receive user message sent by PostThreadMessage when I am doing some lengthy operation in Run() method (overrided Run() function ) , Can you tell me why it is so ? I have written a handler for user defined message, But it's of no use when I am doing lengthy operation in Run(). 2: I read that the Run() function provides a default message loop for user interface thread. How Run() function works internally ? How it...

Data Not Displayed in Query
I have a ODBC query built to pull data from a table in Access and linked to another database table. The query seems to work fine to compare data from the two tables, however there are a few blank fields displayed in the query. I cannot figure out why these fields are blank as there is data in the second table. If I try to enter data it dings and wont let me. It is obviously there, but it not being displayed in the query. Any ideas on a fix would be greatlu appreciated. What is the data type of the blank fields? There's some things that Access can't handle. Is...

Help needed with IIF in my query
I have several fields in my database that are date fields. The data type in my table for these is date/time and formatted as short date. In my query (I'll use one field as my example here). The result I want is if there is a date in the field then say Y for yes, or N for no. Pretty simple. I don't know why I keep getting a data type mismatch in criteria expression error. The fields in my table are date fields and formatted as short date. Is there something wrong with my IIF or can you suggest a better syntax to use. Please let me know. Thanks. Here is my formula: IIf([M...