 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Using Like "*" for a combo box search
I have a combo box that has data for a field pulled from the table. If I leave the combo box blank then it pulls all the records. But the user isn't going to know this. I try to put a * in the combo box and it doesn't like it because it says the text I entered isn't an item in the list, which makes sense. Is there a way I can have the user put in a * and then it retrieves all the records? I don't want to have to put a record in the table with a * because that would work but thats ugly :) Thanks in advance for your help! If I understand you correctly one of these articles may point you in the right direction: http://www.mvps.org/access/forms/frm0043.htm http://www.fabalou.com/Access/Forms/comboboxextrarow.asp I think both articles use "All" for retrieving all records, but you can use the asterisk if you (and the users) prefer. I think "All" is clearer, but it's your call. >I have a combo box that has data for a field pulled from the table. If I > leave the combo box blank then it pulls all the records. But the user [quoted text clipped - 10 lines] > work but thats ugly :) > Thanks in advance for your help! Thanks, that worked great, one more minor point, how can I get the word All to be the first on my list in my combo box? Thanks! >If I understand you correctly one of these articles may point you in the >right direction: [quoted text clipped - 10 lines] >> work but thats ugly :) >> Thanks in advance for your help! If you use '(All)' rather than 'All' and sort alphabetically, (All) should float to the top. If some other field in the query is determining sort order, you need to include an appropriate value for that field along with (All) in your UNION.  SignatureHTH, George > Thanks, that worked great, one more minor point, how can I get the word > All [quoted text clipped - 16 lines] >>> work but thats ugly :) >>> Thanks in advance for your help! Thanks, worked perfect! Can you lend some advice on ym post from yesterday, no one has responded and I'm getting worried its to complex. I don't know whats better, to drill down, or open a new form, but then how do I get the same recordset from the previous filtered form into the new one?? >If you use '(All)' rather than 'All' and sort alphabetically, (All) should >float to the top. [quoted text clipped - 6 lines] >>>> work but thats ugly :) >>>> Thanks in advance for your help! Worked but what if the datatype is a number, then it doesn't like ALL showing up in the combo box, is there a work around for that? Thanks! >If you use '(All)' rather than 'All' and sort alphabetically, (All) should >float to the top. [quoted text clipped - 6 lines] >>>> work but thats ugly :) >>>> Thanks in advance for your help! Try having two columns in the combo box: one that's visible and one that's not, with the not visible column being the bound one. Have the not visible column show the same as the visible column in all cases except the "All" case, where you want it to have Null. SELECT Field1, Field1 FROM MyTable UNION SELECT Null, "(All)" FROM MyTable ORDER BY 2 SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Worked but what if the datatype is a number, then it doesn't like ALL > showing [quoted text clipped - 12 lines] >>>>> work but thats ugly :) >>>>> Thanks in advance for your help! I see what you are saying, but when ALL is chosen I need it to choose all the records in the table for that field. Do you know what I mean? How do you hide the one column? Thanks for your help! >Try having two columns in the combo box: one that's visible and one that's >not, with the not visible column being the bound one. [quoted text clipped - 14 lines] >>>>>> work but thats ugly :) >>>>>> Thanks in advance for your help! Your WHERE clause needs to be WHERE (MyNumericField = Forms!NameOfForm!NameOfComboBox OR Forms!NameOfForm!NameOfComboBox IS NULL) Note that you can use that same approach with text fields too. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I see what you are saying, but when ALL is chosen I need it to choose all >the [quoted text clipped - 22 lines] >>>>>>> work but thats ugly :) >>>>>>> Thanks in advance for your help! Its just not working. I'm getting errors in my code when I choose ALL. Isn't there an easy way, through a combo box, instead of choosing a value in the combo box, that you can choose all the values so the query brings back all the results from with that field in it? I can't find it anywhere but there must be a way this can be done. This adding ALL to my combo box works great for my text fields but doesn't work for my numeric field. Thanks! >Your WHERE clause needs to be > [quoted text clipped - 8 lines] >>>>>>>> work but thats ugly :) >>>>>>>> Thanks in advance for your help! Not sure what you mean by "in my code" Exactly what are you doing? Realistically, there is no "easy way". You need to ensure that you're either comparing to the value in the combo box, or doing something specific if the combo box has a certain value. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Its just not working. I'm getting errors in my code when I choose ALL. > Isn't there an easy way, through a combo box, instead of choosing a value [quoted text clipped - 18 lines] >>>>>>>>> work but thats ugly :) >>>>>>>>> Thanks in advance for your help! Sorry Mr. Steele, I'll explain a little better. I have a form with this combo box and it filters results to my subform. Forget the code right now, I can trap that, but right now I have this statement in the Row Source of my combo box: SELECT DISTINCT dbo_DDXLMO.HoleSz FROM dbo_DDXLMO UNION Select '(All)' AS HoleSz FROM dbo_DDXLMO; When I choose ALL I get an error from microsoft Access, so I can't trap it, as its not my code, and it says the text I entered isn't valid, and thats because I'm choosing a text field, 'ALL', when HoleSz is a numeric value. I need to be able to choose ALL and it bring back all the Hole Sizes for my particular query. Please let me know if I provided enough info and thank you in advance for your patience and response. >Not sure what you mean by "in my code" > [quoted text clipped - 9 lines] >>>>>>>>>> work but thats ugly :) >>>>>>>>>> Thanks in advance for your help! As I said already, for a numeric field, you're best off using SELECT DISTINCT dbo_DDXLMO.HoleSz, dbo_DDXLMO.HoleSz FROM dbo_DDXLMO UNION Select Null, '(All)' AS HoleSz FROM dbo_DDXLMO; as the RowSource, and binding to the first column (while hiding it). The criteria for your query would then be WHERE (HoleSz = Forms!NameOfForm!NameOfComboBox) OR (Forms!NameOfForm!NameOfComboBox IS NULL) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Sorry Mr. Steele, I'll explain a little better. > I have a form with this combo box and it filters results to my subform. [quoted text clipped - 30 lines] >>>>>>>>>>> work but thats ugly :) >>>>>>>>>>> Thanks in advance for your help! I did that but the All is not showing, just 1 column with the hole sizes and 1 blank, I am assuming is the Null. I don't know how to show the All. The gist of it is there though so when I click on the blank it should still work and I get a run time error in my AfterUpdate Event code. After updating of the combo box I have a LSQL select statement to populate the subform and the error takes me right to the subform part: Me("frm_OffsetWells_sub2").Form.RecordSource = LSQL Here is the code: Dim LSQL As String LSQL = "select * from dbo_DDXLMO" LSQL = LSQL & " where WellType = '" & cmbWellType& "'" LSQL = LSQL & " and BitType = '" & cmbBitType& "'" LSQL = LSQL & " and HoleSz = " & cmbHoleSz Me("frm_OffsetWells_sub2").Form.RecordSource = LSQL What do you think? Do the BitType and WellType combo boxes also need similar RowSource statements. I just have this for their Row Source: SELECT BitType FROM dbo_DDXLMO UNION Select '(All)' AS BitType FROM dbo_DDXLMO; The reason these don't error is they are strings so they like the text word 'All' The WellType and BitType and HoleSz are all Nulls when I step through my code on the error. So is this where I would put the criteria for my HoleSz that you suggested? Is that what I'm not understanding? Thanks! >As I said already, for a numeric field, you're best off using > [quoted text clipped - 13 lines] >>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>> Thanks in advance for your help! The SQL Douglas provided included: Select Null, '(All)' AS HoleSz You have not included Null in your SQL that I can see. As I see this you have just one column, so hiding the first column will hide the entire row source. I have not been following the discussion in detail, so I do not know exactly how you have arrived at this point, but I did have that observation about the apparent absence of Null in your row source SQL. >I did that but the All is not showing, just 1 column with the hole sizes >and [quoted text clipped - 49 lines] >>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>> Thanks in advance for your help! When I use the statement suggested I get a syntax error in my AfterUpdate event for the HoleSz combo box. Its a syntax error (missing operator) in query expression and HoleSz = For my Text box combo boxes that work when I choose (All), in the query expression it actually has a " For example, the same query expression where I'm getting an error it says and BitType = " and HoleSz = If I take out BitType so I just want to see where its erroring exactly, it gives the same error on HoleSz as I'm guessing it doesn't like HoleSz = nothing. Please advise. Thanks! >The SQL Douglas provided included: >Select Null, '(All)' AS HoleSz [quoted text clipped - 10 lines] >>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>> Thanks in advance for your help! Since all your previous postings have been removed from this reply, please show the code in your AfterUpdate event again. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > When I use the statement suggested I get a syntax error in my AfterUpdate > event for the HoleSz combo box. Its a syntax error (missing operator) in [quoted text clipped - 26 lines] >>>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>>> Thanks in advance for your help! My reply was intended to point out that you were using different SQL from what Douglas suggested. You wrote: "When I use the statement suggested...". What is the exact statement you are using? Where are you using it? What code is running in the HoleSz combo box After Update event? If it is the Record Source SQL you posted, it will not work unless you select a value for all of the controls referenced in the SQL, or you have made allowances for some of the values to be Null. What is the exact row source you are using for a combo box in which you want All to appear. Try putting the Record Source code into a command button Click event, and click the button only when all necessary controls have a value that can be passed to the Record Source SQL code. Also, try a Debug.Print after the LSQL has been completed, to see if you are getting a usable string. It may help to set a break point in the code, perhaps at the first LSQL line, to see just where the code fails. > When I use the statement suggested I get a syntax error in my AfterUpdate > event for the HoleSz combo box. Its a syntax error (missing operator) in [quoted text clipped - 26 lines] >>>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>>> Thanks in advance for your help! After updating of the HoleSz combo box I have a LSQL select statement to populate the subform and the error takes me right to the subform part: Me("frm_OffsetWells_sub2").Form.RecordSource = LSQL Here is the code in my AfterUpdate Event on the HoleSz combo box:: Dim LSQL As String LSQL = "select * from dbo_DDXLMO" LSQL = LSQL & " where WellType = '" & cmbWellType& "'" LSQL = LSQL & " and BitType = '" & cmbBitType& "'" LSQL = LSQL & " and HoleSz = " & cmbHoleSz Me("frm_OffsetWells_sub2").Form.RecordSource = LSQL the RowSource of my HoleSz combo box is: SELECT DISTINCT dbo_DDXLMO.HoleSz, dbo_DDXLMO.HoleSz FROM dbo_DDXLMO UNION SELECT Null, '(All)' As HoleSz FROM dbo_DDXLMO; >My reply was intended to point out that you were using different SQL from >what Douglas suggested. [quoted text clipped - 22 lines] >>>>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>>>> Thanks in advance for your help! The format to reference a subform from the parent form is something like: Me.SubformControlName.Form.RecordSource or Forms!MainFormName!SubformControlName.Form.RecordSource Is LSQL a valid SQL string? I suggested a couple of ways of testing. If you are unclear what I mean I can explain in more detail. > After updating of the HoleSz combo box I have a LSQL select statement to > populate the subform and [quoted text clipped - 45 lines] >>>>>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>>>>> Thanks in advance for your help! Since you're building the SQL in code, you can easily check whether or not you even need to include the field in the Where clause! Dim LSQL As String Dim strCriteria As String LSQL = "select * from dbo_DDXLMO" If Nz(Me.cmbWellType, "(All)") <> "(All)" Then strCriteria = strCriteria & " WellType = '" & Me.cmbWellType& "' AND " End If If Nz(Me.cmbBitType, "(All)") <> "(All)" Then strCriteria = strCriteria & " BitType = '" & Me.cmbBitType& "' AND " End If If Nz(Me.cmbHoleSz, "(All)") <> "(All)" Then strCriteria = strCriteria & " HoleSz = " & Me.cmbHoleSz & " AND " End If If Len(strCriteria) > 0 Then LSQL = LSQL & " WHERE " & Left$(strCriteria, Len(strCriteria) - 5) End If If you weren't building the SQL in code, so that your SQL was something like: SELECT * from dbo_DDXLMO WHERE WellType = Forms![NameOfForm]![cmbWellType] AND BitType = Forms![NameOfForm]![cmbBitType] AND HoleSz = Forms![NameOfForm]![cmbHoleSz] you'd change your SQL (as I suggested quite some time ago) to SELECT * from dbo_DDXLMO WHERE (WellType = Forms![NameOfForm]![cmbWellType] OR Forms![NameOfForm]![cmbWellType] IS NULL) AND (BitType = Forms![NameOfForm]![cmbBitType] OR Forms![NameOfForm]![cmbBitType] IS NULL) AND (HoleSz = Forms![NameOfForm]![cmbHoleSz] OR Forms![NameOfForm]![cmbHoleSz] IS NULL) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > After updating of the HoleSz combo box I have a LSQL select statement to > populate the subform and [quoted text clipped - 45 lines] >>>>>>>>>>>>>>>>> work but thats ugly :) >>>>>>>>>>>>>>>>> Thanks in advance for your help!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.