Results 1 to 12 of 12

Thread: Null problem

  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unhappy Unanswered: Null problem

    I have a form that is used as a search form. When used it opens a form and diplays the result in a filtered set of records. I set up a query to serve as the filter that is used. So far so good. Here is the problem part of the code I have in the "where" clause:

    Code:
    ((Main.[PRIMARY PHONE]) Like [forms]![frmfilterforrecord]![CW_PrimPhone] & "*")
    I have several unbound text boxes used on the search form. The idea was to set up a simple way to search using any or all textboxes filled in. The problem is that if say the [CW_PrimPhone] is left blank and the field is null in a record it won't return those particular records.
    Does anyone have any ideas how to alter my code so that it will only search using the textboxes filled in on the search form? Or maybe that it will return records with a null value. I know that the * is the problem, but don't know what I need to replace it.

    Please Help!!!

    Thanks to all
    sewmap

  2. #2
    Join Date
    Nov 2004
    Posts
    51
    just got it to work. Here is what I used:


    Code:
    (IIf(isnull(forms!frmfilterforrecord!CW_PrimPhone),"",(Main.[PRIMARY PHONE]) Like forms!frmfilterforrecord!CW_PrimPhone & "*")

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Good job! The reason you have to do it that way is because Null is NOT the same as zero, it is unkown. Unknown + anything else = unknown.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2004
    Posts
    51
    I'm now having a performance issue with this same code. It works, but is very slow. Part of the problem is that is searching about 600,000 records. The DB Backend is on SQL Server, otherwise the db probably wouldn't run at all

    Any ideas how to improve the speed?!?

    Thanks

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Part of the problem is that SQL Server does not recognize the IIf() formula, so it has to transfer all the records across the network so Access can do it. You might look at a stored procedure instead of this query (so SQL Server can do it all), or build SQL in code based on what the user has filled out or left blank, then make that SQL the source of the form/subform (so you don't have anything in the SQL that SQL Server can't process).

    This method may work better (no IIf formula), but gets messy with too many different controls, which it sounds like you have:

    http://www.mvps.org/access/queries/qry0001.htm
    Paul

  6. #6
    Join Date
    Nov 2004
    Posts
    51
    Quote Originally Posted by pbaldy
    This method may work better (no IIf formula), but gets messy with too many different controls, which it sounds like you have:

    http://www.mvps.org/access/queries/qry0001.htm
    Code:
    [Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null
    Thanks for the info. I wasn't aware of that(SQL not recognizing IIF)!

    So will this return all the records even if the searched field is not null but is zero length?
    Of course, I'm not sure that the code I have now will either

    I noticed another post where someone put a where statement in the VBA instead of using a query. Would this be a possible solution or would it still create the same problem?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well to clarify, Access (Jet) SQL does recognize IIf(), SQL Server (T-SQL) does not. Its equivalent on SQL Server is the CASE statement.

    When you start getting into a number of controls that may or may not be filled out (typical of search forms), I prefer to build SQL in code. It sounds like what you're talking about is the wherecondition argument of DoCmd.OpenForm. It could solve your problem. I typically have the search controls on a main form, and the results in a subform. When the user clicks a "search" button, I build SQL based on what they input, then set the subform's RecordSource to that SQL.
    Paul

  8. #8
    Join Date
    Nov 2004
    Posts
    51
    Quote Originally Posted by pbaldy
    I prefer to build SQL in code.
    It's getting a little more clear for me, but could you explain a little more. I haven't done much with SQL and am not sure exactly what you mean. Are saying build code in the access side or on the SQL side? If on the Access side, do you just substitute T-SQL code instead of what I used or .....

    You are EXTREMELY helpful!

    Thanks Again!!

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Building it on the Access side. Here's a tutorial along with a sample db that does what I'm talking about (hopefully nobody minds my posting a link to another site):

    http://www.accessvba.com/forum/showthread.php?t=4895
    Paul

  10. #10
    Join Date
    Nov 2004
    Posts
    51

    Smile

    THANKS!!

    That explains it all! I'll give a shot and see if I can get it to work!

  11. #11
    Join Date
    Nov 2004
    Posts
    51

    Talking

    I Tested a couple of fields on me search form and it works beautifully!!

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For the record, you could write this as a pass-through to sql server as:

    ISNULL(forms!frmfilterforrecord!CW_PrimPhone, '') & '%'
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •