query where one field has a product should be order Desc compared to second field

[megatronixs]

Hi all,



I need to adjust a query. In this query I get the oldest cases from a table and they are ordered the hightes days on top. Now I need to add the possibility that if one field [Products] contains a product, they should be on top and those with no products at the bottom.

Greetings.

[ranman256]

add an extra field (NOT CRITERIA): not IsNull([Products]) as HasProduct

sort on HasProduct, true sorts to the top

[megatronixs]

HI,

It gives me error with: as HasProduct

I inserted like this: Expr5: not IsNull([Products])

Where do I fit the HasProduct?

Greetings.

[nick404]

Right after, NOT IsNull([Products]) As HasProduct


Then refer to that expr as HasProduct for the rest of you query.

Then as ranman said sort on HasProduct.

[megatronixs]

Hi, still gives me error (on: As)
Expr5: not IsNull([Products]) as HasProduct

Maybe I'm just putting it in the wrong spot, but I put it in a empty field of the query.

"The expression you entered contains invalid syntax
you may have entererd an operand without an operator"

Greetings.

[June7]

Not IsNull([Products]) As HasProduct is what you will see in the query SQL View

In Design View grid do this:

HasProduct: Not IsNull([Products])

[megatronixs]

Hi June7,

Big thanks :-)
I have basic knowledge of SQL and I'm using the design view to create the queries and that is why I did not get it :-(

When I thought it was finished, they came again with a new criteria :-(
If the fiield Produts has a product called "New Product", it should be apearing at the end, but before where the product field is empty (the ones with empty products field still need to be in the list but at the very end).

Greetings.

[megatronixs]

Hi all,

Almost there :-)
I managed with the "New Product". I made the IFF that if the field has a New Product it should give the value False (shows up as 0) and then I could filter on it with order in ascending.
I still need to manage how to deal with the IsNull. So if the field [New Product] is null, it should give maybe also False.

Greetings.

[June7]

Maybe:

IIf([Products] Is Null, 0, IIf([Products]="New Product", 1, 2))

http://allenbrowne.com/QueryPerfIssue.html

[megatronixs]

Hi June7,

That did the trick :-)

Thanks a lot.

Greetings.