 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Select query populating list box
I have built a search form that has three unbound controls. On is a text control that the user can input the company name or contact and the other two are date controls where the user can put in a date range. Here is the sql SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate, tblInstitution1.txtInstitution, [txtFirstname] & " " & [txtlastname] AS Contact FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = tblInstitution1.txtRefNbr WHERE (((tbldocument.txtExpirydate) Between [forms]![frmsearch3]![txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND ((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")) OR (((tbldocument.txtExpirydate) Between [forms]![frmsearch3]![txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND ((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] & "*")); However I want the user to the ability to search without putting in a date range and changed the sql to this SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate, tblInstitution1.txtInstitution, [txtFirstname] & " " & [txtlastname] AS Contact FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = tblInstitution1.txtRefNbr WHERE (((tbldocument.txtExpirydate) Between [forms]![frmsearch3]![txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND ((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")) OR (((tbldocument.txtExpirydate) Between [forms]![frmsearch3]![txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND ((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] & "*")) OR (((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] & "*")) OR (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")); But this doesn't work it wont show any records with just the name text field showing. Anyone help? Thanks Tony Sorry I forgot to say why it didn't work. What happens is this: if I put in the date ranges then the query ignores the date range and I get all the records where the names match the text in the text box. Sorry Tony >I have built a search form that has three unbound controls. On is a text >control that the user can input the company name or contact and the other [quoted text clipped - 35 lines] > Thanks > Tony IF txtExpirydate ALWAYS has a date value (never Null) And txtLastname ALWAYS has a value (never Null). Then you might be able to use the following. Assumptions: TxtLastName is never Null TxtExpiryDate is never null and is within the date range Jan 1, 1900 to Jan 1, 9999 SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate, tblInstitution1.txtInstitution , [txtFirstname] & " " & [txtlastname] AS Contact FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = tblInstitution1.txtRefNbr WHERE tbldocument.txtExpirydate Between Nz([forms]![frmsearch3]![txtstartdate],#1/1/1900#) And NZ([forms]![frmsearch3]![txtenddate], #1/1/9999#) AND tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" If you are dealing with nulls then there are other ways to modify the where clause. The following should work for you. The Parens are important to make sure Access correctly understands the criteria. SELECT ... FROM ... WHERE ( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate] OR [forms]![frmsearch3]![txtstartdate] is Null ) AND ( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate] OR [forms]![frmsearch3]![txtenddate] Is Null ) AND tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" Access will rearrange the where criteria when you save the query. Adding several additional sets of criteria. > I have built a search form that has three unbound controls. On is a text > control that the user can input the company name or contact and the other [quoted text clipped - 35 lines] > Thanks > Tony Thanks John there should never be Nulls but I think the second version is probably the safest. However if you look at my original code the text control needs to search two fields txtlastname and txtinstitution (either field as an OR not both as an AND). This worked oK until I put the date parameters in. How do I add the extra choice to your statement? This is looking promisiming I've spent 2 days on this so far. Thanks again Tony > IF txtExpirydate ALWAYS has a date value (never Null) And txtLastname > ALWAYS has [quoted text clipped - 87 lines] >> Thanks >> Tony Add one more set of parentheses SELECT ... FROM ... WHERE ( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate] OR [forms]![frmsearch3]![txtstartdate] is Null ) AND ( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate] OR [forms]![frmsearch3]![txtenddate] Is Null ) AND ( tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" Or tblInstitution1.txtInstitution Like [Forms]![frmsearch3]![txtname] & "*") Additional assumption is that txtInstitution is never null. Alert!! Every time you add one more criteria to the where clause, Access will redo the criteria and at some point the query will become too complex to run. You will reach the point where you will have to build the SQL using vba (or at least the where clause.) > Thanks John there should never be Nulls but I think the second version is > probably the safest. [quoted text clipped - 96 lines] > >> Thanks > >> Tony Thanks John. However it is possible that txtInstitution could be null which is why the where needs to include both. It is unlikely (?) that the txtname will be null. Hoe does this change the where clause would it be Or tblInstitution1.txtInstitution Like [Forms]![frmsearch3]![txtname] & "*" Or tblInstitution1.txtInstitution [Forms]![frmsearch3]![txtname] is Null ) Is this getting complicated? I'm not too hot on VBA so I'm not sure how to code this in VBA Thanks again for your help Tony > Add one more set of parentheses > [quoted text clipped - 137 lines] >> >> Thanks >> >> Tony If you are searching against the same value in either txtLastName and txtInstitution (or both) and if at least one of those fields has a value then the where clause should work for you. Try it and see. If it gives you the "wrong" result, post back and explain what you got and what you wanted (expected). The following modification would let your query run and get results based on any combination of the three controls on the form being filled in, including NONE of them being filled in. -- No criteria entered = all records -- just start date = all records where expiry date is on or after the date -- just end date = all records where expiry date in on or before the date --Both dates = all records where expiry date occurs in the specified range -- just txtName = all records where txtLastName or txtInstitution start with txtName -- three other combinations: definition left as an exercise for the student SELECT ... FROM ... WHERE ( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate] OR [forms]![frmsearch3]![txtstartdate] is Null ) AND ( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate] OR [forms]![frmsearch3]![txtenddate] Is Null ) AND ( tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" Or tblInstitution1.txtInstitution Like [Forms]![frmsearch3]![txtname] & "*" Or [Forms]![frmsearch3]![txtname] is null) > Thanks John. However it is possible that txtInstitution could be null > which is why the where needs to include both. It is unlikely (?) that the [quoted text clipped - 157 lines] >>> >> Thanks >>> >> Tony John tried your code . It works if I put say H* in textbox, gives me all Txtnames and txtInstitution starting with H but when I put a date range in the two date boxes I get no records and I have chosen a date range where there should be. Any ideas? Tony > Add one more set of parentheses > [quoted text clipped - 137 lines] >> >> Thanks >> >> Tony Suggestions? Yeah, I should have more coffee. I reversed the greater than and less than signs. Which meant the code was trying to find an ExpiryDate that was before the start date and after the end date. An impossible condition unless you put the later date in the start and the earlier date in the end control. Here is the corrected version (I hope). Also I just posted a response to your earlier post with another modification to it and I copied and pasted, so it has the same error in the <= and >= signs. SELECT ... FROM ... WHERE ( tbldocument.txtExpirydate >=[forms]![frmsearch3]![txtstartdate] OR [forms]![frmsearch3]![txtstartdate] is Null ) AND ( tbldocument.txtExpirydate <= [forms]![frmsearch3]![txtenddate] OR [forms]![frmsearch3]![txtenddate] Is Null ) AND ( tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" Or tblInstitution1.txtInstitution Like [Forms]![frmsearch3]![txtname] & "*") > John tried your code . It works if I put say H* in textbox, gives me all > Txtnames and txtInstitution starting with H but when I put a date range in [quoted text clipped - 148 lines] >>> >> Thanks >>> >> Tony Thanks John worked just fine! Watch the coffee though, the caffeine can play havoc with your brain :-) Tony > Suggestions? > [quoted text clipped - 178 lines] >>>> >> Thanks >>>> >> Tony I've tried this but it doesn't work WHERE ( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate] OR [forms]![frmsearch3]![txtstartdate] is Null ) AND ( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate] OR [forms]![frmsearch3]![txtenddate] Is Null ) AND ( tblInstitution1.txtlastname Like [Forms]![frmsearch3]![txtname] & "*" OR tblInstitution1.txtInstitution Like [Forms]![frmsearch3]![txtname] & "*" ) I've added tblInstitution1.txtlastname to the SELECT statement Any ideas? Tony > IF txtExpirydate ALWAYS has a date value (never Null) And txtLastname > ALWAYS has [quoted text clipped - 87 lines] >> Thanks >> Tony |
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.