Query to Include cboSymbols or ‘*’

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

ryguy7272

For the past four hours I’ve been at this; still no solution! Argh!! I now
yield to the SQL experts. I have two small loops that run through a ListBox
and do some nice string concatenations for me:
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) > 0 Then
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

That works fine! I have the following VBA, which also works fine:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

Now, I’m trying to add something to the VBA to allow me to get a value from
here:
[Forms]![QueryForm]![cboSymbol]

If I have IBM in my cboSymbol, I want to past it to my query. If nothing is
selected in cboSymbol, then I want to see all symbols in my query.

I thought it would be something like this, but no:
“(([Forms]![QueryForm]![cboSymbol] & "") Like '*') And “ & _

This causes and error too:
"[Forms]![QueryForm]![cboSymbol] & "" Like
Nz([Forms]![QueryForm]![CboSymbol]," * ") And " & _

I keep getting these run time errors, or type mismatch errors. Very
annoying!! If someone can please take a look at this and let me know what to
do, I’d be most appreciative!!

Thanks, as always,
Ryan---
 
I think I have suggested before to simply ignore the control if it doesn't
have a value you need to consider.

My code would look something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
' do something similar for all other criteria controls
strSQL = "SELECT .... FROM .... WHERE " & strWhere
 
thanks for the help here Duane. That pretty much makes sense; except for
this part:
strWhere = "1=1 "

Anyway, I popped this in:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If

I modified my strSQL ever so slightly:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ") & strWhere ;"

Now, whether I have anything in cboSymbols or not (just nothing, or IBM, or
SBUX, or whatever), I get prompted to add a parameter -- no idea why. If I
enter IBM, I get every single record returned. If I leave it blank and just
click through with no parameter entered, the result is some IBM records
returned, but NOT all, and a whole bunch of other symbols too. Did I miss
something here???

Thanks again,
Ryan---

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


Duane Hookom said:
I think I have suggested before to simply ignore the control if it doesn't
have a value you need to consider.

My code would look something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
' do something similar for all other criteria controls
strSQL = "SELECT .... FROM .... WHERE " & strWhere

--
Duane Hookom
Microsoft Access MVP


ryguy7272 said:
For the past four hours I’ve been at this; still no solution! Argh!! I now
yield to the SQL experts. I have two small loops that run through a ListBox
and do some nice string concatenations for me:
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) > 0 Then
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

That works fine! I have the following VBA, which also works fine:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

Now, I’m trying to add something to the VBA to allow me to get a value from
here:
[Forms]![QueryForm]![cboSymbol]

If I have IBM in my cboSymbol, I want to past it to my query. If nothing is
selected in cboSymbol, then I want to see all symbols in my query.

I thought it would be something like this, but no:
“(([Forms]![QueryForm]![cboSymbol] & "") Like '*') And “ & _

This causes and error too:
"[Forms]![QueryForm]![cboSymbol] & "" Like
Nz([Forms]![QueryForm]![CboSymbol]," * ") And " & _

I keep getting these run time errors, or type mismatch errors. Very
annoying!! If someone can please take a look at this and let me know what to
do, I’d be most appreciative!!

Thanks, as always,
Ryan---
Click to expand...
Click to expand...
 
Take out all of your stuff in your strSQL after the WHERE. Build the entire
where clause dynamically.
Begin with code like:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
If Not IsNull(Me.cboFrom) Then
strWhere = strWhere & " AND TDATE >=#" & _
Me.cboFrom & "# "
End If
If Not IsNull(Me.cboTo) Then
strWhere = strWhere & " AND TDATE <=#" & _
Me.cboTo & "# "
End If
strSQL = "SELECT * FROM Trades " & _
"WHERE " & strWhere

I'm not sure what your other code looks like but all of your where clause
should be concatenated as needed.

Do you understand the process?
Have you tried to troubleshoot by using a break point in your code?
Try this link http://www.tek-tips.com/faqs.cfm?fid=7148 to find out how to
go about fixing stuff or at least finding enough out about your issues to
ask how to fix a specific issue.

--
Duane Hookom
MS Access MVP


ryguy7272 said:
thanks for the help here Duane. That pretty much makes sense; except for
this part:
strWhere = "1=1 "

Anyway, I popped this in:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If

I modified my strSQL ever so slightly:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ") & strWhere ;"

Now, whether I have anything in cboSymbols or not (just nothing, or IBM,
or
SBUX, or whatever), I get prompted to add a parameter -- no idea why. If
I
enter IBM, I get every single record returned. If I leave it blank and
just
click through with no parameter entered, the result is some IBM records
returned, but NOT all, and a whole bunch of other symbols too. Did I miss
something here???

Thanks again,
Ryan---

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


Duane Hookom said:
I think I have suggested before to simply ignore the control if it
doesn't
have a value you need to consider.

My code would look something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
' do something similar for all other criteria controls
strSQL = "SELECT .... FROM .... WHERE " & strWhere

--
Duane Hookom
Microsoft Access MVP


ryguy7272 said:
For the past four hours I’ve been at this; still no solution! Argh!!
I now
yield to the SQL experts. I have two small loops that run through a
ListBox
and do some nice string concatenations for me:
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) > 0 Then
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

That works fine! I have the following VBA, which also works fine:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between
[Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

Now, I’m trying to add something to the VBA to allow me to get a value
from
here:
[Forms]![QueryForm]![cboSymbol]

If I have IBM in my cboSymbol, I want to past it to my query. If
nothing is
selected in cboSymbol, then I want to see all symbols in my query.

I thought it would be something like this, but no:
“(([Forms]![QueryForm]![cboSymbol] & "") Like '*') And “ & _

This causes and error too:
"[Forms]![QueryForm]![cboSymbol] & "" Like
Nz([Forms]![QueryForm]![CboSymbol]," * ") And " & _

I keep getting these run time errors, or type mismatch errors. Very
annoying!! If someone can please take a look at this and let me know
what to
do, I’d be most appreciative!!

Thanks, as always,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Build the entire where clause dynamically!! I love it!! Brilliant!! Thanks
for helping me with this. I dabbled a bit more with some Is Null stuff. It
gets a bit complex, and actually I don't think there should be any Nulls in
there anyway, so I'm testing for Nulls on the form where the user enters the
data and that should take care of that.

Thanks for showing me this dynamic-clause-technique Duane! I will
definitely be using this more in the future!


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


Duane Hookom said:
Take out all of your stuff in your strSQL after the WHERE. Build the entire
where clause dynamically.
Begin with code like:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
If Not IsNull(Me.cboFrom) Then
strWhere = strWhere & " AND TDATE >=#" & _
Me.cboFrom & "# "
End If
If Not IsNull(Me.cboTo) Then
strWhere = strWhere & " AND TDATE <=#" & _
Me.cboTo & "# "
End If
strSQL = "SELECT * FROM Trades " & _
"WHERE " & strWhere

I'm not sure what your other code looks like but all of your where clause
should be concatenated as needed.

Do you understand the process?
Have you tried to troubleshoot by using a break point in your code?
Try this link http://www.tek-tips.com/faqs.cfm?fid=7148 to find out how to
go about fixing stuff or at least finding enough out about your issues to
ask how to fix a specific issue.

--
Duane Hookom
MS Access MVP


ryguy7272 said:
thanks for the help here Duane. That pretty much makes sense; except for
this part:
strWhere = "1=1 "

Anyway, I popped this in:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If

I modified my strSQL ever so slightly:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ") & strWhere ;"

Now, whether I have anything in cboSymbols or not (just nothing, or IBM,
or
SBUX, or whatever), I get prompted to add a parameter -- no idea why. If
I
enter IBM, I get every single record returned. If I leave it blank and
just
click through with no parameter entered, the result is some IBM records
returned, but NOT all, and a whole bunch of other symbols too. Did I miss
something here???

Thanks again,
Ryan---

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


Duane Hookom said:
I think I have suggested before to simply ignore the control if it
doesn't
have a value you need to consider.

My code would look something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboSymbol) Then
strWhere = strWhere & " And Symbol = '" & Me.cboSymbol & "' "
End If
' do something similar for all other criteria controls
strSQL = "SELECT .... FROM .... WHERE " & strWhere

--
Duane Hookom
Microsoft Access MVP


:

For the past four hours I’ve been at this; still no solution! Argh!!
I now
yield to the SQL experts. I have two small loops that run through a
ListBox
and do some nice string concatenations for me:
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) > 0 Then
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

That works fine! I have the following VBA, which also works fine:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between
[Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And " & _
"(Trades.[Cust] " & strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

Now, I’m trying to add something to the VBA to allow me to get a value
from
here:
[Forms]![QueryForm]![cboSymbol]

If I have IBM in my cboSymbol, I want to past it to my query. If
nothing is
selected in cboSymbol, then I want to see all symbols in my query.

I thought it would be something like this, but no:
“(([Forms]![QueryForm]![cboSymbol] & "") Like '*') And “ & _

This causes and error too:
"[Forms]![QueryForm]![cboSymbol] & "" Like
Nz([Forms]![QueryForm]![CboSymbol]," * ") And " & _

I keep getting these run time errors, or type mismatch errors. Very
annoying!! If someone can please take a look at this and let me know
what to
do, I’d be most appreciative!!

Thanks, as always,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Back
Top