Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank text field (97)

    I have a query where I want all the records where the text field SpecLastName is blank. How do I indicate this in a query. I tried putting "" (quotes) to search for zero length stings. I tried Is Null but nothing works. Anyone know what I am doing wrong. Thanks for your help.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blank text field (97)

    Is there nothing in the field, or is there a blank character in the field?
    Is Null in the Criteria of the SpecLAstName field should work.
    Post the SQL of the query and we can have a look.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank text field (97)

    Are you sure the fields are either Null or contain a zero length string? It's also possible that they contain spaces.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank text field (97)

    Here is the SQL using is NULL:
    SELECT Specialists.SpecialistLName, tblProductType.ProdType, tblReissue.RenewalDate, tblReissue.ClientNumber, tblReissue.GroupNumber, tblReissue.GroupName, tblReissue.DateAssigned, tblReissue.SpecCompleteDate, tblReissue.JobChecked, tblReissue.OutOfComp, tblReissue.PrintDate, tblReissue.contbkcb, tblReissue.Pending, tblReissue.Contractmaildate, tblReissue.Subs, tblReissue.GroupCount
    FROM Specialists INNER JOIN (tblProductType INNER JOIN tblReissue ON tblProductType.ProdTypeID = tblReissue.ProdTypeID) ON Specialists.SpecialistID = tblReissue.SpecialistID
    WHERE (((Specialists.SpecialistLName) Is Null) AND ((tblReissue.RenewalDate)=[Renewal Month and Year]));

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank text field (97)

    The field that I am trying to search on is in a table. I put the key to that table into the main table and create a lookup table from it. Now I want to isolate all the records that do not have a name selected from the drop down list - in other words some of the records do not have a name selected from the drop down list. I want to isolate those records. I have tried "Is Null" and "" (quotes). I can't get those records that have nothing in the "speclastname" field to come up. Even when I just run a list of every thing - I can't get the records that have nothing selected in that field. I am probably doing something wrong or breaking some rule but it seems like I should be able to do this.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank text field (97)

    Your SQL looks OK. Normally I hesitate to ask this, but would it be possible to post the Specialists table so that Loungers can test on their own system? If so, you can do the following:
    1. <LI>Create a new, blank database.
      <LI>Import the Specialists table into the new database.
      <LI>If the imported table contains sensitive information, delete or modify it.
      <LI>Compact the new database (Tools/Database Utilities/Compact Database).
      <LI>Create a WinZip file containing the new database.
      <LI>Attach the zip file to a reply. Note: if you preview your reply, the Attach-a-file box will be emptied; you must enter the file name anew.
    Thanks,
    Hans

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank text field (97)

    Having joined half way through this, could it possibly that the field has got a Default value set in it at Table level and therefore the is Null is not working for this reason?
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank text field (97)

    Originally I used a copy of another query (which was giving me trouble) - when I created a query from scratch - using Is null worked. I don't know why the query that I copied wasn't working. Thanks for your quick response and all your help.

Posting Permissions

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