Query to Filter by Dates, Using Form

I’m kind of stuck on a query!  This works fine, when I just filter for 
Customers and Traders.  Then, I went ahead and added in a couple of 
ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
query doesn’t work anymore…

This SQL Works fine for Cust and Trader, but not for dates:
SELECT *
FROM Trades
WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');

Below is my SQL for filtering by dates, but it DOES NOT WORK:
SELECT *
FROM Trades
WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
[forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
IN('');

The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?


Here is my VBA:
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 strCust As String
    Dim strTrader As String
    Dim strGender As String
    Dim strTraderCondition As String
    Dim strGenderCondition 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 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
        strTraderCondition = " AND "
    Else
        strTraderCondition = " OR "
    End If

' Build SQL statement
    strSQL = "SELECT * FROM Trades " & _
             "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
"Trades.[Trader] " & strTrader & ";"
' 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


I’d sincerely appreciate any/all help with this!!!

Thanks,
Ryan--


-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
0
Utf
1/28/2010 6:48:01 AM
access.queries 6343 articles. 1 followers. Follow

7 Replies
966 Views

Similar Articles

[PageSpeed] 50

Ryan -

I don't see the Trades.Tdate criteria being built in your code, but you need 
to include the date delimeter (#) before and after the dates from the form.  
When you are building your SQL dynamically, it would be something like this:

    Dim strDateCondition As String

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

Try that out.

-- 
Daryl S


"ryguy7272" wrote:

> I’m kind of stuck on a query!  This works fine, when I just filter for 
> Customers and Traders.  Then, I went ahead and added in a couple of 
> ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
> query doesn’t work anymore…
> 
> This SQL Works fine for Cust and Trader, but not for dates:
> SELECT *
> FROM Trades
> WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
> 
> Below is my SQL for filtering by dates, but it DOES NOT WORK:
> SELECT *
> FROM Trades
> WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
> IN('');
> 
> The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?
> 
> 
> Here is my VBA:
> 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 strCust As String
>     Dim strTrader As String
>     Dim strGender As String
>     Dim strTraderCondition As String
>     Dim strGenderCondition 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 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
>         strTraderCondition = " AND "
>     Else
>         strTraderCondition = " OR "
>     End If
> 
> ' Build SQL statement
>     strSQL = "SELECT * FROM Trades " & _
>              "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
> "Trades.[Trader] " & strTrader & ";"
> ' 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
> 
> 
> I’d sincerely appreciate any/all help with this!!!
> 
> Thanks,
> Ryan--
> 
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
0
Utf
1/28/2010 3:38:01 PM
It is possible that the query does not properly understand the type of the 
values in cboFrom and cboTo. You can make sure it does understand the type 
using the Parameters clause.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Or add this to the beginning of your query - which is what the above should do 
- watch out if you use the above method, sometimes Access will add extra 
brackets when using the Parameters dialog.

PARAMETERS forms]![SearchForm]![cboFrom] DateTime,
[forms]![SearchForm]![cboTo] DateTime;
SELECT *
FROM Trades
WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And
[forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader]
IN('');


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ryguy7272 wrote:
> I’m kind of stuck on a query!  This works fine, when I just filter for 
> Customers and Traders.  Then, I went ahead and added in a couple of 
> ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
> query doesn’t work anymore…
> 
> This SQL Works fine for Cust and Trader, but not for dates:
> SELECT *
> FROM Trades
> WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
> 
> Below is my SQL for filtering by dates, but it DOES NOT WORK:
> SELECT *
> FROM Trades
> WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
> IN('');
> 
> The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?
> 
> 
> Here is my VBA:
> 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 strCust As String
>     Dim strTrader As String
>     Dim strGender As String
>     Dim strTraderCondition As String
>     Dim strGenderCondition 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 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
>         strTraderCondition = " AND "
>     Else
>         strTraderCondition = " OR "
>     End If
> 
> ' Build SQL statement
>     strSQL = "SELECT * FROM Trades " & _
>              "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
> "Trades.[Trader] " & strTrader & ";"
> ' 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
> 
> 
> I’d sincerely appreciate any/all help with this!!!
> 
> Thanks,
> Ryan--
> 
> 
0
John
1/28/2010 3:58:48 PM
Thanks Daryl and thanks John!  Ya know, I've done this before, but it was a 
while ago, and forgot about the #-thing and the parameters-menu-thing.  I 
think you're both right!  I fiddled with both of your recommendations, 
seperate and together, and I'm still stuck, but pretty close I think.  This 
is what I'm working with now:

SQL:
PARAMETERS [forms]![SearchForm]![cboFrom] DateTime, 
[forms]![SearchForm]![cboTo] DateTime;
SELECT *
FROM Trades
WHERE (((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
[forms]![SearchForm]![cboTo]) AND ((Trades.Cust) In ('')) AND 
((Trades.Trader) In ('')));

That will compile in DisplayView, but when I try to run it from the Form I 
get an error that reads, ‘an unexpected error hass occurred’
Procedure cmdOK_Click
Error Number: -2147217816
Error Description: Object ‘qryFilter’ already exists.
Yeap, it is really spelled ‘hass’

I went back to the Query Design View and tried something like this:
Between # " &[forms]![SearchForm]![cboFrom]& " # And # " 
&[forms]![SearchForm]![cboTo]& " #

That won’t even compile!  Message reads, ‘The expression you entered has an 
invalid date value.’

As I know, you do have to have the #-signs around those dates, but I can’t 
seem to get any combination of quotes, ampersands, and number-signs working.

One other thing I was thinking of is that a working solution may require VBA 
and SQL.  Daryl was right, Trades.Tdate is NOT in my VBA code because I 
didn’t know how to get the string in there!  So, finally, I modified my VBA, 
as shown below:

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 strCust As String
    Dim strTrader As String
    Dim strGender As String
    Dim strTraderCondition As String
    Dim strGenderCondition As String
    Dim strSQL As String
    Dim strDateCondition 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 String for Dates
strDateCondition = " AND 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
        strTraderCondition = " AND "
    Else
        strTraderCondition = " OR "
    End If

' Build SQL statement
    strSQL = "SELECT * FROM Trades " & _
             "WHERE Trades.[TDate] " & strDateCondition & " Trades.[Cust] " 
& strCust & strTraderCondition & "Trades.[Trader] " & strTrader & ";"
' 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

When I run this, I get the same error message as above:
Error Description: Object ‘qryFilter’ already exists.


Again, I think I’m close to a solution, but not quite there yet.  Any 
thoughts on this?

Thanks so much!
Ryan---


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


"John Spencer" wrote:

> It is possible that the query does not properly understand the type of the 
> values in cboFrom and cboTo. You can make sure it does understand the type 
> using the Parameters clause.
> 
> Open the query in design mode
> Select Query: Parameters from the Menu
> Fill in the EXACT name of the parameter in column 1
> Select the data type of the parameter in column 2
> 
> Or add this to the beginning of your query - which is what the above should do 
> - watch out if you use the above method, sometimes Access will add extra 
> brackets when using the Parameters dialog.
> 
> PARAMETERS forms]![SearchForm]![cboFrom] DateTime,
> [forms]![SearchForm]![cboTo] DateTime;
> SELECT *
> FROM Trades
> WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And
> [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader]
> IN('');
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> ryguy7272 wrote:
> > I’m kind of stuck on a query!  This works fine, when I just filter for 
> > Customers and Traders.  Then, I went ahead and added in a couple of 
> > ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
> > query doesn’t work anymore…
> > 
> > This SQL Works fine for Cust and Trader, but not for dates:
> > SELECT *
> > FROM Trades
> > WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
> > 
> > Below is my SQL for filtering by dates, but it DOES NOT WORK:
> > SELECT *
> > FROM Trades
> > WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> > [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
> > IN('');
> > 
> > The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?
> > 
> > 
> > Here is my VBA:
> > 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 strCust As String
> >     Dim strTrader As String
> >     Dim strGender As String
> >     Dim strTraderCondition As String
> >     Dim strGenderCondition 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 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
> >         strTraderCondition = " AND "
> >     Else
> >         strTraderCondition = " OR "
> >     End If
> > 
> > ' Build SQL statement
> >     strSQL = "SELECT * FROM Trades " & _
> >              "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
> > "Trades.[Trader] " & strTrader & ";"
> > ' 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
> > 
> > 
> > I’d sincerely appreciate any/all help with this!!!
> > 
> > Thanks,
> > Ryan--
> > 
> > 
> .
> 
0
Utf
1/28/2010 5:51:01 PM
It looks as if I stepped in where I should not have.  I should have read your 
entire post and looked at your procedure.  I do almost NOTHING in ADODB so my 
advice is probably way off-base.

I think that in ADODB you need to delimit dates with ' marks and not # marks.

Also, the error says that qryFilter already exists - which should have little 
to do with whether or not SQL string's syntax is correct.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ryguy7272 wrote:
> Thanks Daryl and thanks John!  Ya know, I've done this before, but it was a 
> while ago, and forgot about the #-thing and the parameters-menu-thing.  I 
> think you're both right!  I fiddled with both of your recommendations, 
> seperate and together, and I'm still stuck, but pretty close I think.  This 
> is what I'm working with now:
> 
> SQL:
> PARAMETERS [forms]![SearchForm]![cboFrom] DateTime, 
> [forms]![SearchForm]![cboTo] DateTime;
> SELECT *
> FROM Trades
> WHERE (((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> [forms]![SearchForm]![cboTo]) AND ((Trades.Cust) In ('')) AND 
> ((Trades.Trader) In ('')));
> 
> That will compile in DisplayView, but when I try to run it from the Form I 
> get an error that reads, ‘an unexpected error hass occurred’
> Procedure cmdOK_Click
> Error Number: -2147217816
> Error Description: Object ‘qryFilter’ already exists.
> Yeap, it is really spelled ‘hass’
> 
> I went back to the Query Design View and tried something like this:
> Between # " &[forms]![SearchForm]![cboFrom]& " # And # " 
> &[forms]![SearchForm]![cboTo]& " #
> 
> That won’t even compile!  Message reads, ‘The expression you entered has an 
> invalid date value.’
> 
> As I know, you do have to have the #-signs around those dates, but I can’t 
> seem to get any combination of quotes, ampersands, and number-signs working.
> 
> One other thing I was thinking of is that a working solution may require VBA 
> and SQL.  Daryl was right, Trades.Tdate is NOT in my VBA code because I 
> didn’t know how to get the string in there!  So, finally, I modified my VBA, 
> as shown below:
> 
> 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 strCust As String
>     Dim strTrader As String
>     Dim strGender As String
>     Dim strTraderCondition As String
>     Dim strGenderCondition As String
>     Dim strSQL As String
>     Dim strDateCondition 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 String for Dates
> strDateCondition = " AND 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
>         strTraderCondition = " AND "
>     Else
>         strTraderCondition = " OR "
>     End If
> 
> ' Build SQL statement
>     strSQL = "SELECT * FROM Trades " & _
>              "WHERE Trades.[TDate] " & strDateCondition & " Trades.[Cust] " 
> & strCust & strTraderCondition & "Trades.[Trader] " & strTrader & ";"
> ' 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
> 
> When I run this, I get the same error message as above:
> Error Description: Object ‘qryFilter’ already exists.
> 
> 
> Again, I think I’m close to a solution, but not quite there yet.  Any 
> thoughts on this?
> 
> Thanks so much!
> Ryan---
> 
> 
0
John
1/28/2010 6:09:34 PM
Ryan -

What line is the error coming from?  It seems it should only come from the 
'create' statement, but I could be wrong.  Can you step through the code (add 
a breakpoint if needed) and see what line of code is producing the error?

Another thing to try - after building the SQL statement, maybe you should 
drop the stored query and create a new one with the SQL you have built?
-- 
Daryl S


"ryguy7272" wrote:

> Thanks Daryl and thanks John!  Ya know, I've done this before, but it was a 
> while ago, and forgot about the #-thing and the parameters-menu-thing.  I 
> think you're both right!  I fiddled with both of your recommendations, 
> seperate and together, and I'm still stuck, but pretty close I think.  This 
> is what I'm working with now:
> 
> SQL:
> PARAMETERS [forms]![SearchForm]![cboFrom] DateTime, 
> [forms]![SearchForm]![cboTo] DateTime;
> SELECT *
> FROM Trades
> WHERE (((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> [forms]![SearchForm]![cboTo]) AND ((Trades.Cust) In ('')) AND 
> ((Trades.Trader) In ('')));
> 
> That will compile in DisplayView, but when I try to run it from the Form I 
> get an error that reads, ‘an unexpected error hass occurred’
> Procedure cmdOK_Click
> Error Number: -2147217816
> Error Description: Object ‘qryFilter’ already exists.
> Yeap, it is really spelled ‘hass’
> 
> I went back to the Query Design View and tried something like this:
> Between # " &[forms]![SearchForm]![cboFrom]& " # And # " 
> &[forms]![SearchForm]![cboTo]& " #
> 
> That won’t even compile!  Message reads, ‘The expression you entered has an 
> invalid date value.’
> 
> As I know, you do have to have the #-signs around those dates, but I can’t 
> seem to get any combination of quotes, ampersands, and number-signs working.
> 
> One other thing I was thinking of is that a working solution may require VBA 
> and SQL.  Daryl was right, Trades.Tdate is NOT in my VBA code because I 
> didn’t know how to get the string in there!  So, finally, I modified my VBA, 
> as shown below:
> 
> 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 strCust As String
>     Dim strTrader As String
>     Dim strGender As String
>     Dim strTraderCondition As String
>     Dim strGenderCondition As String
>     Dim strSQL As String
>     Dim strDateCondition 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 String for Dates
> strDateCondition = " AND 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
>         strTraderCondition = " AND "
>     Else
>         strTraderCondition = " OR "
>     End If
> 
> ' Build SQL statement
>     strSQL = "SELECT * FROM Trades " & _
>              "WHERE Trades.[TDate] " & strDateCondition & " Trades.[Cust] " 
> & strCust & strTraderCondition & "Trades.[Trader] " & strTrader & ";"
> ' 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
> 
> When I run this, I get the same error message as above:
> Error Description: Object ‘qryFilter’ already exists.
> 
> 
> Again, I think I’m close to a solution, but not quite there yet.  Any 
> thoughts on this?
> 
> Thanks so much!
> Ryan---
> 
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "John Spencer" wrote:
> 
> > It is possible that the query does not properly understand the type of the 
> > values in cboFrom and cboTo. You can make sure it does understand the type 
> > using the Parameters clause.
> > 
> > Open the query in design mode
> > Select Query: Parameters from the Menu
> > Fill in the EXACT name of the parameter in column 1
> > Select the data type of the parameter in column 2
> > 
> > Or add this to the beginning of your query - which is what the above should do 
> > - watch out if you use the above method, sometimes Access will add extra 
> > brackets when using the Parameters dialog.
> > 
> > PARAMETERS forms]![SearchForm]![cboFrom] DateTime,
> > [forms]![SearchForm]![cboTo] DateTime;
> > SELECT *
> > FROM Trades
> > WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And
> > [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader]
> > IN('');
> > 
> > 
> > John Spencer
> > Access MVP 2002-2005, 2007-2010
> > The Hilltop Institute
> > University of Maryland Baltimore County
> > 
> > ryguy7272 wrote:
> > > I’m kind of stuck on a query!  This works fine, when I just filter for 
> > > Customers and Traders.  Then, I went ahead and added in a couple of 
> > > ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
> > > query doesn’t work anymore…
> > > 
> > > This SQL Works fine for Cust and Trader, but not for dates:
> > > SELECT *
> > > FROM Trades
> > > WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
> > > 
> > > Below is my SQL for filtering by dates, but it DOES NOT WORK:
> > > SELECT *
> > > FROM Trades
> > > WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> > > [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
> > > IN('');
> > > 
> > > The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?
> > > 
> > > 
> > > Here is my VBA:
> > > 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 strCust As String
> > >     Dim strTrader As String
> > >     Dim strGender As String
> > >     Dim strTraderCondition As String
> > >     Dim strGenderCondition 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 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
> > >         strTraderCondition = " AND "
> > >     Else
> > >         strTraderCondition = " OR "
> > >     End If
> > > 
> > > ' Build SQL statement
> > >     strSQL = "SELECT * FROM Trades " & _
> > >              "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
> > > "Trades.[Trader] " & strTrader & ";"
> > > ' 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
> > > 
0
Utf
1/29/2010 4:00:03 PM
Thanks for the follow up Daryl!  I agree, drop the stored query and create a 
new one with the SQL!!  I’m not sure how to do that though.

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 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 certainly seems right, going from SQL to VBA.  I popped this into my 
VBA code, and 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.  Daryl, you gave me some code for 
doing this, but I don’t really know how to adapt that in the rest of my VBA 
code

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

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

Still stuck. . .

Any ideas on how to resolve this?

Thanks!
Ryan---



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


"Daryl S" wrote:

> Ryan -
> 
> What line is the error coming from?  It seems it should only come from the 
> 'create' statement, but I could be wrong.  Can you step through the code (add 
> a breakpoint if needed) and see what line of code is producing the error?
> 
> Another thing to try - after building the SQL statement, maybe you should 
> drop the stored query and create a new one with the SQL you have built?
> -- 
> Daryl S
> 
> 
> "ryguy7272" wrote:
> 
> > Thanks Daryl and thanks John!  Ya know, I've done this before, but it was a 
> > while ago, and forgot about the #-thing and the parameters-menu-thing.  I 
> > think you're both right!  I fiddled with both of your recommendations, 
> > seperate and together, and I'm still stuck, but pretty close I think.  This 
> > is what I'm working with now:
> > 
> > SQL:
> > PARAMETERS [forms]![SearchForm]![cboFrom] DateTime, 
> > [forms]![SearchForm]![cboTo] DateTime;
> > SELECT *
> > FROM Trades
> > WHERE (((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> > [forms]![SearchForm]![cboTo]) AND ((Trades.Cust) In ('')) AND 
> > ((Trades.Trader) In ('')));
> > 
> > That will compile in DisplayView, but when I try to run it from the Form I 
> > get an error that reads, ‘an unexpected error hass occurred’
> > Procedure cmdOK_Click
> > Error Number: -2147217816
> > Error Description: Object ‘qryFilter’ already exists.
> > Yeap, it is really spelled ‘hass’
> > 
> > I went back to the Query Design View and tried something like this:
> > Between # " &[forms]![SearchForm]![cboFrom]& " # And # " 
> > &[forms]![SearchForm]![cboTo]& " #
> > 
> > That won’t even compile!  Message reads, ‘The expression you entered has an 
> > invalid date value.’
> > 
> > As I know, you do have to have the #-signs around those dates, but I can’t 
> > seem to get any combination of quotes, ampersands, and number-signs working.
> > 
> > One other thing I was thinking of is that a working solution may require VBA 
> > and SQL.  Daryl was right, Trades.Tdate is NOT in my VBA code because I 
> > didn’t know how to get the string in there!  So, finally, I modified my VBA, 
> > as shown below:
> > 
> > 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 strCust As String
> >     Dim strTrader As String
> >     Dim strGender As String
> >     Dim strTraderCondition As String
> >     Dim strGenderCondition As String
> >     Dim strSQL As String
> >     Dim strDateCondition 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 String for Dates
> > strDateCondition = " AND 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
> >         strTraderCondition = " AND "
> >     Else
> >         strTraderCondition = " OR "
> >     End If
> > 
> > ' Build SQL statement
> >     strSQL = "SELECT * FROM Trades " & _
> >              "WHERE Trades.[TDate] " & strDateCondition & " Trades.[Cust] " 
> > & strCust & strTraderCondition & "Trades.[Trader] " & strTrader & ";"
> > ' 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
> > 
> > When I run this, I get the same error message as above:
> > Error Description: Object ‘qryFilter’ already exists.
> > 
> > 
> > Again, I think I’m close to a solution, but not quite there yet.  Any 
> > thoughts on this?
> > 
> > Thanks so much!
> > Ryan---
> > 
> > 
> > -- 
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> > 
> > 
> > "John Spencer" wrote:
> > 
> > > It is possible that the query does not properly understand the type of the 
> > > values in cboFrom and cboTo. You can make sure it does understand the type 
> > > using the Parameters clause.
> > > 
> > > Open the query in design mode
> > > Select Query: Parameters from the Menu
> > > Fill in the EXACT name of the parameter in column 1
> > > Select the data type of the parameter in column 2
> > > 
> > > Or add this to the beginning of your query - which is what the above should do 
> > > - watch out if you use the above method, sometimes Access will add extra 
> > > brackets when using the Parameters dialog.
> > > 
> > > PARAMETERS forms]![SearchForm]![cboFrom] DateTime,
> > > [forms]![SearchForm]![cboTo] DateTime;
> > > SELECT *
> > > FROM Trades
> > > WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And
> > > [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader]
> > > IN('');
> > > 
> > > 
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2010
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > > 
> > > ryguy7272 wrote:
> > > > I’m kind of stuck on a query!  This works fine, when I just filter for 
> > > > Customers and Traders.  Then, I went ahead and added in a couple of 
> > > > ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic 
> > > > query doesn’t work anymore…
> > > > 
> > > > This SQL Works fine for Cust and Trader, but not for dates:
> > > > SELECT *
> > > > FROM Trades
> > > > WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
> > > > 
> > > > Below is my SQL for filtering by dates, but it DOES NOT WORK:
> > > > SELECT *
> > > > FROM Trades
> > > > WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And 
> > > > [forms]![SearchForm]![cboTo]) AND Trades.[Cust] IN('') AND Trades.[Trader] 
> > > > IN('');
> > > > 
> > > > The Form is called ‘SearchForm’.  Can anyone tell me what I’m doing wrong?
> > > > 
> > > > 
> > > > Here is my VBA:
> > > > 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 strCust As String
> > > >     Dim strTrader As String
> > > >     Dim strGender As String
> > > >     Dim strTraderCondition As String
> > > >     Dim strGenderCondition 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 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
> > > >         strTraderCondition = " AND "
> > > >     Else
> > > >         strTraderCondition = " OR "
> > > >     End If
> > > > 
> > > > ' Build SQL statement
> > > >     strSQL = "SELECT * FROM Trades " & _
> > > >              "WHERE Trades.[Cust] " & strCust & strTraderCondition & 
> > > > "Trades.[Trader] " & strTrader & ";"
> > > > ' 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
0
Utf
1/29/2010 6:22:09 PM
Ryan -

Yes, you need to adjust the VBA to a string, but add delimiters and values 
from the form, like this:

strSql = "SELECT Trades.TDATE, * " & _
"FROM Trades " & _
"WHERE (((Trades.TDATE) Between #" & [Forms]![SearchForm]![cboFrom]  & "# 
And #" & _
[Forms]![SearchForm]![cboTo]) "# AND ((Trades.Cust) Like '*') AND 
((Trades.Trader) Like '*'));"

-- 
Daryl S


"ryguy7272" wrote:

> Thanks for the follow up Daryl!  I agree, drop the stored query and create a 
> new one with the SQL!!  I’m not sure how to do that though.
> 
> 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 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 certainly seems right, going from SQL to VBA.  I popped this into my 
> VBA code, and 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.  Daryl, you gave me some code for 
> doing this, but I don’t really know how to adapt that in the rest of my VBA 
> code
> 

0
Utf
2/1/2010 6:20:01 PM
Reply:

Similar Artilces:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Using different form strings to filter
Hello. Is there a way to code a query to have it use a filter string if a form is open, or ignore it if the filter is not open? Example: Query Name: Query1 Form: Form1 and Form2 Query fields: Field1 and Field2 I have a string in field 1 and 2 Forms!Form1!Field1 in the first field of the query, and Forms!Form1! Field2 in the second field. Can I also have Forms!Form2!Field1 in the query as well? I have tried this, but a box pops up and asks for the information from form 2 when it is not open. Thank you. hi, nybaseball22@gmail.com wrote: > Hello. Is there a way to code a query to...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

Using Ghost as OS Backup
I attended a class a while back where the exchange admins said they used Ghost to clone the OS drive. I bought the product and it says that during the clone process it temporarily removes the server from the domain. Has anyone done this or had any problems? Thanks, Fred On Mon, 21 Nov 2005 05:26:28 -0600, "Fred T" <fredt2@mail.com> wrote: >I attended a class a while back where the exchange admins said they used >Ghost to clone the OS drive. I bought the product and it says that during >the clone process it temporarily removes the server from the domain. ...

using beforeprint
I am trying to get an application to append a file on a sheet printout. I am using Workbook_BeforePrint. It prints fine, but doesn't seem t execute the sub. What's wrong -- Message posted from http://www.ExcelForum.com Hi you may post your existing code :-) Also check that you have put this code in your workbook module ('ThisWorkbook') -- Regards Frank Kabel Frankfurt, Germany > I am trying to get an application to append a file on a sheet > printout. I am using Workbook_BeforePrint. It prints fine, but > doesn't seem to execute the sub. What's wrong?...

Connection filtering record
1. I have set up connection filtering. How can I see what mail is being rejected? 2. Since setting up connection filtering the email tracking shows all emails being delivered to the intended recipient AND the badmail folder. Could someone explain this? To answer your first question, take a look here: http://www.martijnjongen.com/eng/html/log_analyzer.htm Not sure about the second question. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Nate" <nmorse@aimcomm.com> wrote in message news:ewUBm6C3DHA.2056@TK2MSFTNGP10.phx.gbl... > ...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

MapQuest Buitton on Form
Arvin: Hope you see this post. Mapquest must have changed their code. My Mapquest button on the form does not work anymore. The reason is that in Canadian Postal Codes there is a space (ie. E3A 3N7). My data is stored this way. Mapquest used to accept this format but not anymore. How do I delete the space in the hyperlink created from the event on click (button on Form). Your help would be greatly appreciated again. Here is my code. Private Sub cmdHyperlink_Click() Dim strPath As String If Not IsNull(Me.Postal_Code) Then strPath = "http://www.mapquest.com/maps/" &a...

Forcing Input as Uppercase on User Form
Hi can any one advise me how to amend the following code to ensure :- (a) only two letters are input (b) they are converted to upper case (c) how do you centre the text in a message box header as I have use spaces to achieve this Code used inputVal = TextBox1 Do Until ActiveCell = inputVal ActiveCell.Offset(1, 0).Select If ActiveCell = "" Then MsgBox ("Sorry Not Found - Please Input the first two letter only e.g. RG ") ", vbInformation, " Warning !! " Unload UserForm1 UserForm1.Show End Else End If Loop Excel v...

Time Zone / Incorrect Dates Displayed to User
When a user in the Central Time zone closes an opportunity and someone in the Mountain Time zone views it, the Actual Close Date is 1 day before the date the person in the Central Time zone selected. For instance... If someone in the Central Time zone closes an opportunity and selects the Actual Close Date as 6/2/2007: -- Users in the Central Time zone see: 6/2/2007 -- But users in the Mountain Time zone see: 6/1/2007 This is a big issue when running monthly reports as it makes some opportunities show in the wrong month... and will put that revenue in the wrong month. It would make mo...

Close a form when it loses focus
Hello All Is it possible to make a form close as soon as the user clicks elsewhere on the screen? The form in question is a trivial popup form that is best shown in datasheet view, so I can't add a button. I know the user could close it with the 'X' in the top-right corner, but for other reasons it would be much better if I could force it to close as soon as they click elsewhere. I have tried adding the following OnLostFocus event to the form, but nothing happened! DoCmd.Close acForm, "[frm contacts]", acSaveYes Second question (or does this need to be in a...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...

Cannot stop using online services with First Tech Credit Union
My bank, First Tech Credit Union, recently upgraded their online banking services. This upgrade also included a change to the user name and password used for online banking in Money 2006. Their upgrade instructions (http://www.firsttechcu.com/help/help_guide_money.html) say to disable online services for your Money file as the third step to upgrading to the new services. The problem is that when I click on Stop Using Online Services in Money nothing happens. I click on OK and Money hangs for a bit then comes back with no change, clicking on OK again has the same effect, clicking on ...

Using CComboBox embedded in CToolBar
Hi, I've derived a class from CToolBar and overridden OnCreate to replace 2 placeholder buttons with 2 combo boxes. The two combo boxes are also derived from CComboBox, and populate themselves with appropriate strings. My problem is that I'm not sure of how to inform my application when the user has selected a new item in one of the combo boxes. I've added a handler for the CBN_SELCHANGE message to the CComboBox, but I don't know what to put in it. What is the best way to forward the message to the mainframe? Also, when I replace the placeholder buttons, can I reuse their ...

Subform doesn't work once added to main form
Hi, I've been searching around looking for some help and can't find anyone that has had quite the same problem. I have a main form called 'Sites' and have added a subform called 'Previous Year Flows' into the main form. I do not enter data into this subform. Instead, data on previous years is drawn through from a table called prev yr flows into this subform. When I open the subform on its own it draws through all the necessary data, but when it is embedded within the main form, none of the actual data is pulled through at all, although the linked fields, Site ID and A...

Query Problem in Test.
I keep getting errors on the WHERE part of my query. I'm sure it's something simple. I don't know if you will need the whole code to see what the problem is. It is lengthy, so I will start with just where the problem is. Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM (Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID = qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5 = T2.ZIPCode WHERE " & strWhere) ' See if found none If rs.RecordCount = 0 Then MsgBox "No Churches meet your crite...

Form with desktop shortcut
I created a form which displays one record. The record is chosen at random by a query. It works fine within Access 2003. However, when I create a shortcut to the form on my desktop and run it that way, it always gives me the same record. How can I get the random record I'm looking for? -- CindyLuWho Cindy, I'd say you're probably better off creating a macro that opens the form at the required record. Your desktop shortcut should call this macro, rather than the form. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia "CindyLu" <CindyLu@discus...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

is there a way of using a second monitor as a customer display
This is a multi-part message in MIME format. ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Does anyone know if rms works with 2 monitors. What I mean can you use a second monitor as customer display or something similar. A lot of pos systems out there use a second monitor as customer display, I m not sure if rms supports that option or not but any help would be appreciated. thanks ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

Re:Help with verifying the signature of enveloped -message using x509 certificates.
Re:Help with verifying the signature of enveloped -message using x509 certificates. Hey Friends, I have an xml given by our partner trying to integrating with us and i have their certificate with public key installed in both personal and trusted people folders. when i am using the following code to check the signature of message signed by my certificate i am able to verify the signature but when i am trying to check the signature of the message of our partner using the same following code i am not able to verify the message. the message sent by our partner is right below the code.I wou...