Query with Blanks and Non-Blanks

[getalinks]

Hi there,

Crash coursing in Access here and trying to learn on my own. I've got a majority of the basics down and have started building a query to populate in a list box below it. I have built a search form that allows the user to search based on a number of criteria, but not have to populate all of the fields to get results.

I can get the criterias to reflect correctly as long as all criteria are populated, but now I've gotten to field where there are some values that are blank and now I seem to be stuck.

The formula I was using for the first fields was: Like "*" & [Forms]![Comic Search]![ComicTitle] & "*"

When I try to add the same formula in the search query for the field containing blank values, it only shows data that contains values in the comments fields. If I add an or "Is Null" statement, then I get all data and the comments field doesn't filter based on the search criteria.



Any help would be most appreciated as I've been stuck on this one issue for almost 3 days now.

I have attached pics of the DB here.



Thanks in advance.

[snipe]

Like [Forms]![Comic Search]![Comments] & "*"

Ditch that first asterisk. Should work for ya.

Edit: Nevermind, that won't accomplish what you want. I do that with my "Store #" field, but you need to be able to type anything in, as opposed to knowing what it starts with. Ignore me. My bad.

[getalinks]

Ok, well then, anyone else?

[June7]

The Or Is Null should work.

Like "*" & [Forms]![Comic Search]![Comments] & "*" Or Is Null

Assumes blanks are actually nulls and not empty strings. I don't allow empty strings in text fields.

Is Comments a text or memo field (short text or long text)?

[getalinks]

It's a memo field. I tried it with the Or Is Null and it wouldn't give me the limited results if I had a criteria in the Comments field. It still showed all the blanks as well due to the Or Is Null option.

[June7]

I misunderstood.

I think you need to handle the nulls by calculating a field to replace with empty string. Use Nz([Comments],"") and then apply filter criteria to that constructed field.

I NEVER use dynamic parameterized queries. I prefer http://www.allenbrowne.com/ser-62.html

[getalinks]

June7, I changed it to a short text field, but it still didn't make a difference.

Ah, you updated your post.

Remember. crash coursing in Access. All I read when I read this:

I think you need to handle the nulls by calculating a field to replace with empty string. Use Nz([Comments],"") and then apply filter criteria to that constructed field.

is like the teacher from Charlie Brown. Can you explain in stupid people's terms?

[June7]

Try something like:

SELECT * FROM [Comic Books] WHERE Nz([Comments],"") Like [Forms]![Comic Search]![Comments] & "*";

[getalinks]

Ok, tried that and I got the error:

The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses.

[June7]

There is no subquery in my example. Post your attempted exact full SQL statement.

[getalinks]

Oh, I didn't do anything in SQL. I don't even know how to do that. Every time I've seen instructions saying to do something in SQL, I try to figure something else out as I have no idea where to start to learn that. I'm beginning to think this is above my abilities.

[June7]

Then you didn't try what I suggested. What did you try?

SQL is built by the Query Builder. Open query builder, build query in the designer, switch to SQL View to see the statement. This is basic Access functionality.

Can copy/paste the suggested SQL into SQL View.

[getalinks]

Still getting an error, but here is the SQL coding:

SELECT [Comic Books].[Comic Book Title], [Comic Books].[Comic Book Issue #], [Comic Books].Grade, [Comic Books].Copies, [Comic Books].[Storage Location], [Comic Books].Comments, [Comic Books].Publisher
FROM [Comic Books]
WHERE ((([Comic Books].[Comic Book Title]) Like "*" & IIf(IsNull([Forms]![Comic Search].[ComicTitle])=False,[Forms]![Comic Search].[ComicTitle],"*") & "*") AND (([Comic Books].[Comic Book Issue #]) Like IIf(IsNull([Forms]![Comic Search].[ComicIssue])=False,[Forms]![Comic Search].[ComicIssue],"*")) AND (([Comic Books].Grade) Like "*" & IIf(IsNull([Forms]![Comic Search].[ComicGrade])=False,[Forms]![Comic Search].[ComicGrade],"*") & "*") AND (([Comic Books].Copies) Like IIf(IsNull([Forms]![Comic Search].[CopiesOwned])=False,[Forms]![Comic Search].[CopiesOwned],"*")) AND (([Comic Books].[Storage Location]) Like IIf(IsNull([Forms]![Comic Search].[StorageLoc])=False,[Forms]![Comic Search].[StorageLoc],"*")) AND (([Comic Books].Comments) SELECT * FROM [Comic Books] WHERE Nz([Comments],"") Like [Forms]![Comic Search]![Comments] & "*" AND (([Comic Books].Publisher) Like "*" & IIf(IsNull([Forms]![Comic Search].[Publisher])=False,[Forms]![Comic Search].[Publisher],"*") & "*"))
ORDER BY [Comic Books].[Comic Book Title], [Comic Books].[Comic Book Issue #], [Comic Books].Grade;

[June7]

You have placed my suggested statement within another statement. Since what I offered was a complete SQL statement, your query is not what I intended. Paste it into the SQL View of a new blank query object. If you want to add more filter criteria do that in Design view. The IIf() is not needed because of the LIKE and wildcard structure. However, LIKE and wildcard is intended for use on text type fields. Does not work properly with number or date type. For instance, if Copies is a number field and the parameter is 7, will return records were values begin with 7, such as 7, 71, 72, 73, etc.

SELECT [Comic Books].*
FROM [Comic Books]
WHERE [Comic Book Title] Like "*" & [Forms]![Comic Search].[ComicTitle] & "*" AND [Comic Book Issue #] Like [Forms]![Comic Search].[ComicIssue] & "*" AND Grade Like [Forms]![Comic Search].[ComicGrade] & "*" AND Copies Like [Forms]![Comic Search].[CopiesOwned] & "*" AND [Storage Location] Like [Forms]![Comic Search].[StorageLoc] & "*" AND Nz([Comments],"") Like [Forms]![Comic Search]![Comments] & "*" AND Publisher Like "*" & [Forms]![Comic Search].[Publisher] & "*"
ORDER BY [Comic Book Title], [Comic Book Issue #], Grade;

Review: http://datapigtechnologies.com/flash...tomfilter.html

I NEVER use dynamic parameterized queries. Did you review the Allen Browne link posted earlier?

[getalinks]

That did it! Thank you for being so patient with me. I did check out the link, but it seemed way more than what I needed. I really appreciate all your help.