 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Query based on Form Fields
I have a form where a user can search by Company and Plan Code, they can enter criteria in both or leave one blank. The problem I am having is that if a user searches only by Plan Code, they should see all items matching that Plan Code no matter what the Company is, even the items with a blank Company. However, if they enter criteria in the Plan Code and Company fields they should not see items with a blank Company in the results, only items matching that company. But the query I am useing to get my results is not working correctly. I am using this is my Company Field IIf([Forms]![frmSearch]![Company Code] Is Null,"*",Like [Forms]![frmSearch]![Company Code] & "*") I think there is a problem with the true segment because if I leave Company blank on the form I get zero results. I have also tried IIf([Forms]![frmSearch]![Company Code] Is Null,[Company Code],Like [Forms]![frmSearch]![Company Code] & "*") If Company is left blank I get all records that don't have a Null Company however, I do not get records with a Null Company. Basically what I want is if you enter criteria you will only get results matching that criteria, however, if you leave a criteria field blank you will receive all records with something in that fields as well as all records that are null. Thanks in advance. I did something similar. I don't know how this will work with the Null's, but it did work for me, but I don't remember if I had Nulls in the field Like Nz([Forms]![frmSearch]![Company Code],"*"))) > I have a form where a user can search by Company and Plan Code, they can > enter criteria in both or leave one blank. The problem I am having is that [quoted text clipped - 20 lines] > are null. > Thanks in advance. Thank You but still haveing problems. I have not used the Nz function very often but I was unable to get it to work in this situation. It could be something I am doing wrong but I am not sure. > I did something similar. I don't know how this will work with the Null's, > but it did work for me, but I don't remember if I had Nulls in the field [quoted text clipped - 24 lines] > > are null. > > Thanks in advance. a common method is like: WHERE ([Company] LIKE [Forms]![frmSearch]![Company Code] & "*" OR [Forms]![frmSearch]![Company Code] IS NULL) AND ([Plan Code] LIKE [Forms]![frmSearch]![Plan Code] & "*" OR [Forms]![frmSearch]![Plan Code] IS NULL) > Thank You but still haveing problems. > I have not used the Nz function very often but I was unable to get it to [quoted text clipped - 43 lines] >> > are null. >> > Thanks in advance. I have used this method in the past but it is not working for me in this instnace. I think it is because some items do not have a company code at all. It looks like that is causing the below method to fail. Any experience with this? > a common method is like: > [quoted text clipped - 56 lines] > >> > are null. > >> > Thanks in advance. maybe I did not fully understand your logic? some simple data might help... Company Code Plan Code A 1 2 B 1 C 2 1 D 3 if [frmSearch]![Company Code] is blank and [frmSearch]![Plan Code] = 1, you want to return? Company Code Plan Code A 1 B 1 1 if that is so, then method should have worked unless 1) logic bracketing got screwed up when Access saved query.... check SQL View and verify (in fact, copy and paste here) 2) [frmSearch]![Company Code] has a space so looks Null but isn't WHERE ( [Company] LIKE [Forms]![frmSearch]![Company Code] & "*" OR Len(Trim([Forms]![frmSearch]![Company Code]))=0 ) AND ( [Plan Code] LIKE [Forms]![frmSearch]![Plan Code] & "*" OR Len(Trim([Forms]![frmSearch]![Plan Code]))=0 ) >I have used this method in the past but it is not working for me in this > instnace. I think it is because some items do not have a company code at [quoted text clipped - 67 lines] >> >> > are null. >> >> > Thanks in advance. WHERE ( [Company] LIKE [Forms]![frmSearch]![Company Code] & "*" OR Len(Trim([Forms]![frmSearch]![Company Code] & ""))=0 ) AND ( [Plan Code] LIKE [Forms]![frmSearch]![Plan Code] & "*" OR Len(Trim([Forms]![frmSearch]![Plan Code] & ""))=0 ) > maybe I did not fully understand > your logic? [quoted text clipped - 122 lines] >>> >> > are null. >>> >> > Thanks in advance. one other thought (I wish I understood what "fail" means specifically...) you are only looking at "right side" with wildcard.... is it possible you meant? WHERE ( [Company] LIKE "*" & [Forms]![frmSearch]![Company Code] & "*" OR [Forms]![frmSearch]![Company Code] IS NULL ) AND ( [Plan Code] LIKE "*" & [Forms]![frmSearch]![Plan Code] & "*" OR [Forms]![frmSearch]![Plan Code] IS NULL ) >I have used this method in the past but it is not working for me in this > instnace. I think it is because some items do not have a company code at [quoted text clipped - 67 lines] >> >> > are null. >> >> > Thanks in advance. |
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.