How to pass empty criteria in a query (1 Viewer)

[smbrr]

Hello

I've got a query, it pulls sales data according to "cluster". Cluster can be A, B, C, D, or empty.

I want this query to be able to pull 2 values by being run 2 times:

- Sales of one cluster (empty ones are treated as one cluster).
- Sales of everything.

To do so, I use in my VBA module the QueryDef object and pass my parameters to them:

qd.Parameters![p_param1] = stuff
qd.Parameters![p_param2] = stuff2
qd.Parameters![p_cluster] = cluster

It works with a cluster being A, B, C and so on. It works with the cluster being "*". However it does not work with cluster being null, vbNullString or "".

I can't use an "OR IS NULL" in the query because then I'd have those nulls being added to all my values.

How am I supposed to get the sales of my empty cluster?

Thank you

 

[sneuberg]

I don't understand why IS NULL isn't working for you or perhaps I don't understand what you are trying to do. Could you post the query and the rest of the code and an example of the output you are trying to get?

 

[smbrr]

Because if I used WHERE param = "A" OR IS NULL, I'll get the values of the null cumulated with the values of A. That's no good. Whereas I want to be able to get ONLY values of A and ONLY values of "". As well as values of "*" to get the total.

Query is basically:

SELECT *
FROM tables and joins
WHERE table.cluster Like [p_cluster]

Is it plain impossible to pass an empty string in there?

 

[sneuberg]

It's still not clear to me what you want but if you want the query to act differently depending on the value of p_cluster then you could try using the IIF function to distinguish the cases. I'll give you some examples of this if you show me examples of what kind of output you want for the different cases.

 

[sneuberg]

Also are you using an empty string as a data value? If so why?

 

[static]

Like compares characters. Null and a zls aren't characters so convert them into searchable characters.

SELECT
IIf(IsNull([field1]) or trim([field1])='',"ISNULL",[field1]) AS fld1,
IIf(IsNull([field2]) or trim([field1])='',"ISNULL",[field2]) AS fld2
FROM Table1

If you are running 2 queries, you might be better off building 2 queries and UNIONing the results into 1.

Query1 UNION Query2

 

[smbrr]

That solution might do the trick. Transform the null values into something before running a WHERE LIKE on them.

Will report tomorrow when I get to implement it.
Thanks

 

[sneuberg]

I think this could be simplified by using the IIF function directly in the WHERE clause rather than using it to create a field.

 

[smbrr]

Yes, that worked fine. I used the iif function in the where clause and I pass "empty" instead of "" in my parameter.

Cheers.