if combobox is empty, select all records 1

[flaviooooo]

Hi,

I have a little problem that I just can't figure out. I have a form, with several textfields and a combobox. In this combobox you can select a customer. The report that is produced from this form, is being fed by that cbobox. So it only shows the customer that is selected there.
But now i'd like to show all customers if the combobox is blank.
I've gotten as far as (in the query criteria of the report):

iif([forms]![formname]![cboCust] like "*"; [forms]![formname]![cboCust];"*&quot

but the star-thingy at the end doesn't work. I there a way to pull this off?

Thanks!

 

[DanChard]

You can probably update the recordsource in your Report to make this happen. As it is, it will probably be something like "select * from customers where customer_name = [Forms]![formname]![cboCust]". Try changing it to "select * from customers where customer_name like nz([Forms]![formname]![cboCust],"*&quot"

The nz function will replace the value from [Forms]![formname]![cboCust] where it is null with a '*' and using like instead of = (which you may have been doing already) will match all customers to *.

Hope this makes sense and is of some use. Pleae post again if it doesn't work as expected...

Good luck!

 

[flaviooooo]

this works like a charm, thanks!

I've never heard of that nz function before


greetz and have a star on me!

 

[DanChard]

Thanks.

nz is really handy as it is used to replace null with anything you want and it's very straightforward to use.

Good luck with the rest of your project!

 

[acjim]

Sorry to hijack this thread - it looks as though it applies directly to my problem, DanChard - can you help?

I've got a similar situation to flaviooo but need to get either a chosen criteria (from a cbobox) or all records, including NULL.

This is where the:

select * from customers where customer_name like nz([Forms]![formname]![cboCust],"*&quot

doesn't seem to work, I get all the results that are not null, but the null's do not appear.

The only way I've been able to get Nulls and Others is by using:

select * from customers where customer_name like "*" or is null

please help if you can,

Many Thanks
acjim

 

[DanChard]

Hi there.

I think you probably need an NZ function around the customer_name bit too. Try this:

select * from customers where nz(customer_name,' ') like nz([Forms]![formname]![cboCust],"*&quot

Let me know if this solution gives you any grief (or if it doesn't)...

Cheers,
Dan

 

[acjim]

Thanks for your quick response, I tried that and it worked for all records but will not limit records with criteria.

I actually found a simple answer after posting my question.

It suggested using an in-query update, changing the field from customer_name to "" & [customer_name].
This temporarily modifies the null values so my normal:

(like "*" & [form].....etc) criteria works.

Thanks again for your reply
acjim