Criteria not equal to and null

[andy-29]

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???

[orange]

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???

[andy-29]

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

[alansidman]

If you amend your query to read as follows then you will get records that have null values in the marker status

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

[andy-29]

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!!!!

[alansidman]

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

[andy-29]

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.

[orange]

Glad you have it working!!