Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Field doesn't recognize text (A97) (Access 97)

    For some reason, this no longer works:

    SELECT tbl_Producttots.*, T_SalesMgr.ManagerName
    FROM tbl_Producttots INNER JOIN T_SalesMgr ON tbl_Producttots.srep = T_SalesMgr.Manager
    WHERE ((([tbl_Producttots].[okdisplay])<>"X"));

    okdisplay is a 1 character alphanumeric field

    What I'm trying to do is screen out items that the user doesn't want displayed on a form. It was working before today and I've checked my references and repaired the database. It would be fine if I could put in "where okdisplay is null", but in another situation, I have an "X" and a "Z" (X for mailed out and Z for remove from list). I guess I could change this to numeric (0,1,2), but I really don't want to go through all my code and redo everything.

    <img src=/S/help.gif border=0 alt=help width=23 height=15> - has anyone else come across this problem? Any suggestions?
    Carpy Diem, it&#39;s .

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Text Field doesn't recognize text (A97) (Access 97)

    I've been pondering this and think I need more details. If any records contain a null in field [okdisplay] they won't show up with your current SQL statement. Is that the problem, or are you seeing records that actually have an "X" in the field? Or are you getting some kind of a failure when you run the query? Also, what service packs have been applied to Access97?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Field doesn't recognize text (A97) (Access 97)

    okdisplay does have null values but I swear I was able to bring up everything without an "X" even though they were null. I'll try setting the field to " " (blank) instead of "" (null) and see if that helps. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Thanks!
    Carpy Diem, it&#39;s .

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Field doesn't recognize text (A97) (Access 97)

    Yep, after I updated the okdisplay to " ", and set the default on the field to " ", everything worked fine.

    If only I had taken my brain with me to work this morning, I would have had a better day....got to remember to do that tomorrow. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Carpy Diem, it&#39;s .

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Text Field doesn't recognize text (A97) (Access 97)

    You might want to change your WHERE clause to
    is null and NOT "X"
    I think Access may be happier with that syntax. Glad to be able to help.
    Wendell

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

    Re: Text Field doesn't recognize text (A97) (Access 97)

    A zero-length string ("") is *NOT* the same thing as a Null, even though it is often referred to as a "null" string. You could have accomplished the same thing using the Nz() function without changing your table or data in any way. Here's an example:

    WHERE (((Nz([tbl_Producttots].[okdisplay]),"")<>"X"));
    Charlotte

Posting Permissions

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