Parameter Query help needed

[Mikea8000]

I am passing several (2 to start) values from a form combo boxes.



I want to select where company name = Amazon, "and/or" where the value is $100 dollars. If one of the two fields is null for some reason the querty is not quite working. Can someone PLEASE help me re-write this query so if functions in all cases with multiple combo boxes I am a bit off.
I don't want to to be always an "AND" or always a "OR" just want the query to return the values in the populated combo boxes and if null skip the combo box.


SELECT MasterImport.Cost, MasterImport.Company_Name, *
FROM MasterImport
WHERE (((MasterImport.Cost)=IIf([Forms]![frmQuery]![cboCost] Is Not Null,[Forms]![frmQuery]![cboCost],Null)) AND ((MasterImport.Company_Name)=IIf([Forms]![frmQuery]![cboCompanyname] Is Not Null,[Forms]![frmQuery]![cboCompanyname],Null)));

[June7]

I don't use dynamic parameters in query object. I prefer VBA to build filter and apply to form or report. Review http://allenbrowne.com/ser-62.html

Cannot compare anything to NULL because there is nothing to compare. So = Null is bound to fail in any circumstance.

Try:

WHERE (((MasterImport.Cost) LIKE IIf([Forms]![frmQuery]![cboCost] Is Not Null, Val([Forms]![frmQuery]![cboCost]), "*")) 
AND ((MasterImport.Company_Name) LIKE IIf([Forms]![frmQuery]![cboCompanyname] Is Not Null, [Forms]![frmQuery]![cboCompanyname], "*")));

Or explore the query example at end of Allen Browne's article.

[Mikea8000]

I figured it out finally:

SELECT MasterImport.Cost, MasterImport.Company_Name, *
FROM MasterImport
WHERE (((MasterImport.Cost) Like "*" & [Forms]![frmQuery]![cboCost] & "*") AND ((MasterImport.Company_Name) Like "*" & Trim([Forms]![frmQuery]![cboCompanyname]) & "*"));

[Mikea8000]

Thank you for replying, much appreciated! This worked well! I see a lot other posters trying to do the same thing but do not know how to ask the question in a way that gets them the working results for NULL values. I have to save this for sure.

[June7]

Are you sure that gets you what you want? LIKE *10* will return everything with 10 in it: 100, 1000, 40.10, etc. LIKE 10 returns only 10, same as = sign.

Same would happen with company name if there are names that are similar enough. LIKE *Carson, Inc* would return Carson, Inc as well as Kit Carson, Inc.

[Minty]

The best route is to simply add the following Is Null to each combo reference:

ELECT MasterImport.Cost, MasterImport.Company_Name, *
FROM MasterImport
WHERE MasterImport.Cost  = ([Forms]![frmQuery]![cboCost]  OR [Forms]![frmQuery]![cboCost]  is Null)
AND MasterImport.Company_Name  = ( [Forms]![frmQuery]![cboCompanyname] OR [Forms]![frmQuery]![cboCompanyname] is Null)

[June7]

As shown in Allen Browne example.

Minty, think your opening parens are misplaced.

[Mikea8000]

Well I just realized it's close but it's returning not returning records where there are NULL values.

So I am writing update statements for all nulls since the table is pretty small. However, I would like to have it return nulls as part of my expression builder.
Suggestions. Also you right it returns extra stuff for the wildcards which matters (not exact) how to fix both?

[June7]

Minty's (also Allen Browne's) approach should work. Did you try?

[Mikea8000]

Yes I tried Minty's but it's not returning the records that have NULL(or blank) in any part of the query/fields. It looks like it should work in theory but is skipping the NULL records for some reason.

[June7]

The NULL records return only if there is no input in the control. Works for me. If you expect something else, please clarify.

I think Minty's shows misplaced opening parentheses.

Post your attempted SQL

[Minty]

I agree - the query won't return nulls if you have some criteria.
If you want it to you would need to add OR Is Null to the field criteria, not the combobox expression.

Post up the sql you have now and/or a sample database, also some demonstration of your expected results, e.g. Starting data, your criteria, and what you want as a output, you can mock it up in excel if necessary.

[mike60smart]

an you post a screenshot of the query in design view?