Query to Filter by Dates, Using Form

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

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 -

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 said:
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--
Click to expand...
 
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
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--
Click to expand...
 
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 said:
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
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--
Click to expand...
.
Click to expand...
 
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
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---
Click to expand...
 
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 said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 '*'));"
 
Back
Top