If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to Include cboSymbols or ‘*’
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 he [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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
Query to Include cboSymbols or ‘*’
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" wrote: 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 he [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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
|
|||
|
|||
Query to Include cboSymbols or ‘*’
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" wrote: 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" wrote: 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 he [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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
|
|||
|
|||
Query to Include cboSymbols or ‘*’
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" wrote in message ... 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" wrote: 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" wrote: 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 he [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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
|
|||
|
|||
Query to Include cboSymbols or ‘*’
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote: 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 he [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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Thread Tools | |
Display Modes | |
|
|