Null Values in Parameter Query

[davedynamic]

I have a table that lists stock. A lot of the stock names are repetitive such as Pipe. However, each pipe is unique in Size, Class, or Material.

The stock list also lists fittings. Some fittings have threads, some do not, some threads are BSP some are NPT and so on.
In addition the stock list includes other products like Pumps, which do not have Size, Class, or Threads.

I have made a form, which contains unbound combo boxes with the sources set to the appropriate column grouping descriptions (for example) together. This is supposed to facilitate rapid lookup of the stock number.
To lookup the stock number I am in the process of designing a query. However, for all my fiddling around with the criteria I cannot make the query behave correctly.

For example let’s say we have set our form to the following:

Description – Pipe
Size – 40
Material – ABS

The query will return stock number 483. This is correct and what is required.

However if we set the form as follows:

Description – 150 – 250 Pump
Size –
Material – CastIron Bronze Stainless Steel

The query returns nothing.

So far I have tried the following as criteria:

Like [Forms]![frmFindPart]![Size] & "*"

Or

IIf(IsNull([Forms]![frmFindPart]![Size]) Or [Forms]![frmFindPart]![Size]=""),"* ",[Forms]![frmFindPart]![Size])

The first criteria works provided a size is on file. The second returns an empty query. I am sure there is a way to tell the query that it can return all values (which could be handy), where the description matches.

 

[Liv Manto]

Let [Forms]![frmFindPart]![Size] be X


So your formula is like this:

iif (isnull(X) or X =""), "* ", X)

The second ) should be deleted.

 

[davedynamic]

Thanks for the comment. However, that did not return a record either.