Results 1 to 8 of 8
  1. #1
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34

    Criteria not equal to and null

    Click image for larger version. 

Name:	query.jpg 
Views:	6 
Size:	32.9 KB 
ID:	10091

    Need to create a report that will list all request that are OPEN based on “Marker Status”

    User creates a request for a marker to be created.
    When a marker is created the status will then be marked as one of the following: In Progress, HOLD, Completed.

    Problem is that markers may be created days later.


    Can the criteria be set to include null values. If Marker Status is null and <>”completed” then the request is still open???

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Can you tell us more about the application?
    It seems that your tables may need some redesign.
    Marker Info and Requests have the same PK???

  3. #3
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    I'm a begginer, so any suggestion is greatly appreciated
    I've attached my db, but unfortunately it is unable to save to previous version.
    User creates a marker request and another set of users will create markers for that request.
    2 Main Forms: New Requests and Marker Info
    Attached Files Attached Files

  4. #4
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    If you amend your query to read as follows then you will get records that have null values in the marker status

    Code:
    SELECT Requests.*, [Marker Info].[Marker Status], [Marker Info].[Date Created], [Marker Info].[Hold Date]
    FROM Requests RIGHT JOIN [Marker Info] ON Requests.[Style ID] = [Marker Info].[Style ID]
    WHERE ((([Marker Info].[Marker Status])<>"Completed")) OR ((([Marker Info].[Marker Status]) Is Null));
    BTW, I did note that you are using lookup tables in your data tables. This is a developer No No. While Access allow this to happen it is not a good practice. See the attached and I suggest you amend your db.

    http://access.mvps.org/access/lookupfields.htm

    I concur with Orange's statement about PK's.

    Alan

  5. #5
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    Sorry, can you point out on my db where I use the lookup and suggest a workaround. I'm just a begginer, and a bit overwhelmed.

    Thanks!!!!

  6. #6
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    You should be doing your lookups in forms. When I opened your table that contained the marker status, it was a lookup field in your table.

    Create a new table with your options. RecordID, RecordDescription. On your form, create a combo box, have its record source the table you created. Go to this site and scroll down to the section on combo boxes for further information.

    http://www.datapigtechnologies.com/AccessMain.htm

  7. #7
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    I changed my table design as suggested by orange.
    Alan, thank you very much for above suggestions. All lookups are now done within the forms and not within the tables.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How To force a Null if Criteria doesn't exisit
    By Jerseynjphillypa in forum Queries
    Replies: 2
    Last Post: 07-19-2012, 01:18 PM
  2. Query criteria with values and null
    By robsworld78 in forum Queries
    Replies: 13
    Last Post: 03-27-2012, 05:44 AM
  3. Replies: 1
    Last Post: 02-23-2012, 02:27 PM
  4. Replies: 3
    Last Post: 01-15-2012, 11:05 PM
  5. Replies: 12
    Last Post: 12-11-2011, 05:04 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums