Results 1 to 6 of 6

Thread: Query Questions

  1. #1
    Join Date
    May 2006
    Posts
    9

    Unanswered: Query Questions

    I have created a report base on a query. And on Query or report I want to remove all entry with “Set-Up Charge” under Comment field (Memo). I tried < >”Set-Up Charge” but it shows all the comments with the “Set-Up Charge”. Is there any other way to do it? I have 2 other criterias on two different fields (type the customer name) (Balance >0). Everything works except for the Memo Field.

    I heard about bad things about Memo Data Type that it cannot be sorted. Can I convert Memo to Text since max characters I may have on that field is 15 characters?

    How do you exclude multiple items in query under one field. Example to exclude "FAI", "Source Inspection" And "Expedite". I tried <>"FAI"- it works with single item but when I tried <> "FAI" And "Source Inspection" And "Expedite" it brings out the query with column header and no data. I tried it with Not like "FAI"..... and it does the same thing.
    HELP!

    Access 2000
    Vista
    Thanks,
    John

  2. #2
    Join Date
    Apr 2007
    Posts
    108
    How do you exclude multiple items in query under one field. Example to exclude "FAI", "Source Inspection" And "Expedite". I tried <>"FAI"- it works with single item but when I tried <> "FAI" And "Source Inspection" And "Expedite" it brings out the query with column header and no data. I tried it with Not like "FAI"..... and it does the same thing.
    HELP!
    Using <>"Something" is fine. You just put one criteria below other, and so on.. Switch to SQL view to see how it converts to SQL statement of your desires.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In terms of SQL
    Code:
    WHERE  someField <> "FAI"
    AND    someField <> "Source Inspection"
    AND    someField <> "Expedite"
    
    or...........
    
    WHERE  someField NOT IN ("FAI", "Source Inspection", "Expedite")
    George
    Home | Blog

  4. #4
    Join Date
    May 2006
    Posts
    9
    Thanks- it works for multiple items in the query with the Text Data Field. But it doesn't work for the Memo field. Any suggestions?
    Thanks again!

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can't really do searches on a memo field. One work-around I found was to copy the memo field contents into a string variable and then search through the string variable. You're limited though by the 255 characters so it may not be the total solution but you could possibly test for the length of the memo field and copy it into multiple string variables.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2006
    Posts
    9
    Can I change Memo to a Text data type since max characters I may have on this field is less that 30?

Posting Permissions

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