asked on

Query not returning blanks

Access - Query is not returning blanks when I use Not Like "Delete"

I should be getting lines with blanks and lines with other text in the field ACTIONTAKEN????????

SELECT tbl_MECHANICAL.TAG_NUMBER, Calendar.MDate, Calendar.MaintenanceID, tbl_MECHANICAL.New_Crafts, tbl_MECHANICAL.Unit, tbl_MECHANICAL.ASSETSDescription, tbl_MECHANICAL.Duration, tbl_MECHANICAL.TASKSDescriptionCorrection, tbl_MECHANICAL.New_Interval, tbl_MECHANICAL.Unit, tbl_MECHANICAL.System, tbl_MECHANICAL.Time, tbl_MECHANICAL.ActionTaken
FROM Calendar INNER JOIN tbl_MECHANICAL ON Calendar.MaintenanceID = tbl_MECHANICAL.ID;

Gustav Brock

If Not Like "Delete" will filter out all rows, nothing will be returned. That's by design.

ssblue

ASKER

In that field there is comments like: "Delete" , "No Action Taken", "ActionTaken
Frequency Revised" , "Change Craft" and then there are blank lines.

I need to see everything except those with "Delete"

How do I get the Blanks and all the other comments??

Mark Edwards

Remember, nulls are treated differently. Also realize that "Not Like "Delete" where you don't have any wildcard characters in you comparison string is the same as using <>.
Try something like:

WHERE TRIM(NZ([ACTIONTAKEN????????],""))<>"Delete"
Or
WHERE NOT TRIM(NZ([ACTIONTAKEN????????],"")) LIKE "Delete*" (if you want to filter out those that begin with "Delete".)

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rog D

Try using a left join to see all rows in both tables. Then you can work with a filter on the ActionTaken as some of those rows may be empty.

SELECT tbl_MECHANICAL.TAG_NUMBER, Calendar.MDate, Calendar.MaintenanceID, tbl_MECHANICAL.New_Crafts, tbl_MECHANICAL.Unit, tbl_MECHANICAL.ASSETSDescription, tbl_MECHANICAL.Duration, tbl_MECHANICAL.TASKSDescriptionCorrection, tbl_MECHANICAL.New_Interval, tbl_MECHANICAL.Unit, tbl_MECHANICAL.System, tbl_MECHANICAL.Time, tbl_MECHANICAL.ActionTaken
FROM Calendar left JOIN tbl_MECHANICAL ON Calendar.MaintenanceID = tbl_MECHANICAL.ID;

Roger

ssblue

ASKER

Thanks for the info

Gustav Brock

You are welcome!

Dale Fye

Gustav,

Why would you use:

Where tbl_MECHANICAL.ActionTaken Is Null Or Nz(tbl_MECHANICAL.ActionTaken) <> "Delete"

Open in new window

instead of:

Where Nz(tbl_MECHANICAL.ActionTaken, "") <> "Delete"

Open in new window

Mark Edwards

All the solutions are variations of:
WHERE TRIM(NZ([ACTIONTAKEN],""))<>"Delete"
I thought the field was [ActionTaken???????] from reading the authors post. I didn't pay close attention to the fields in the query.
Let the author pick which one makes sense to him and choose it as the solution, even if it won't compile.