Query criterium on a number, using Like..& "*" does not work...

[Pierkes]

Hi,

I have a split form "frm_trajecten" with a few comboboxes to select the records which should be displayed in the lower part of the plit form.
One of the combo's is "cmb_prodjr"
It listst the years in the field [prodjaar] in the table "tbl_trajecten" trough a query called "Q_prodjaar" with unique values.
It lists;

empty
2012
2013
2014
2015

In the table "tbl_trajecten" the field [prodjaar] can be empty or filled with a number (year i.e. 2012).

Now, in the Query "Q_trajectne" (which is the bases for the form "frm_trajecten" i put in all fields of the table "tbl_trajecten". In the criteria part of the query, at the field "prodjaar" i have put in;

Like [Forms]![frm_trajecten]![cmb_prodjr] & "*"

This does not work properly.
When i select a year in the combobox [cmb_prodjr] it works fine.

However, when i leave the combobox empty, it does not do anything. I would then like to have it list all records.

Apparantly the & "*" part of the criteria does not work.

What should i put there instead ?

I hope to hear from you.
Pierre

[Estuardo]

G'd Morning Pierkes,
If your combobox list the option "Empty" then your query is working properly, because what your sql query gets it's something like this:
Select a,b,c from tbl Where b Like "Empty*".
If b contains nothing but numbers the query should return just one row where the "empty" value was found.
If it doesn't, test your query by manually supplying the parameters if it works then double check the parameters you're sending via your combo.
G'd Luck

Estuardo
http://www.accessextended.com

[Pierkes]

Hi Estuardo,

Thanks for your reply. however, i don't seem able to figure it out.

when i leave out the & "*" part of the formula in the criterium part of the query and only have

Like [Forms]![frm_trajecten] ![cmb_prodjr]

it works fine if i select a number from the combobox (a year).

However, when the split form is displayd on opening of the form, i only see the records that contain a number in the field [tr_prod_jaar]. This is strange to me because i did not put in an on_open event.

So my guess is the query does not list the right records to begin with.

Any ideas ?
regards,

Pierre

[Rabbit]

The problem is that when you leave it empty, the value is null, not a blank string. And a null operated on in any way with another operand results in a null. So what you really end up with is like null. Use the Nz function to convert your null into a blank string.

[Pierkes]

Hi,

I tested the following in the criterium part

Like [Forms]![frm_trajecten]![cmb_prodjr] & "*"

this returns all records where there is a number in the field [tr_prod_jaar] or, if i choose a year from the combobox [cmb_prodjr], it returns only the records of that year.

this is perfect.

However, if the combobox is empty, i would like the query to return ALL records, regardsless of something is filled in the field [tr_prod_jaar].

Then i tested;

Like [Forms]![frm_trajecten]![cmb_prodjr] OR Is Null 

If i choose a year from the combobox [cmb_prodjr], it returns the records of that year AND the records where nothing is filled in the field [tr_prod_jaar].

So this does not work either...

How do i do this ?
Please help, it's frustrating !

Thanks,
Pierre

[Pierkes]

Hi,

I found a solution;

In the query i made an extra field using the expression;

productiejr: IIf(IsNull([tr_prod_jaar]);"empty";[tr_prod_jaar])

This makes sure that all the records have something in the field "productiejr"

Then i can search for the item that is selected in the combobox by using;

Like [Forms]![frm_trajecten]![cmb_prodjr] & "*"

I now can:

- choose a item from the combobox and it will list only those records or
- choose the blank (or click a button that uses "*" to filter the records) to not use a filter

Best regards,
Pierre

P.S. If you know a more easy way, please let me know !

It works like a charm !

[Rabbit]

You don't need to make an extra field, you can just use:

Like Nz([Forms]![frm_trajecten]![cmb_prodjr], "") & "*"