Is Between or is Null

[abbaddon223]

Hi,

I'm creating an advanced search form which is making use of Like queries and is Null. A working example is below:

Like "*" & [Forms]![Frm_Data_Mine]![Combo_Primary_SIC] & "*" Or Is Null

So this either returns matching values from the field, or basically ignores this if left blank. All good.

I have two other fields:

[Turnover_From]
[Turnover_To]

Both are numeric values. What I would like to achieve is the same as the above, so I can search between a from and to value, or if left null, ignore them. I had a go with the below but it's clearly not right, and I can work how where and how I insert the Like statement which makes the others work. Any help is greatly appreciated!!!

Between [forms]![Frm_Data_Mine]![Turnover_From] And [forms]![Frm_Data_Mine]![Turnover_From] Or Is Null

 

[pr2-eugin]

Maybe it is Not Null after all.. See if you have set the field's property "Allow Zero Length String", if it says Yes.. you might as well add..

Between [forms]![Frm_Data_Mine]![Turnover_From] And [forms]![Frm_Data_Mine]![Turnover_From] Or Is Null [COLOR=Red][B]Or ""[/B][/COLOR]

 

[abbaddon223]

Hi,

Thanks for the response - the field property in the query or on the form please?

Thanks!!!!

 

[pr2-eugin]

the field property in the query or on the form

Neither.. The property of the field in Question in the table..

 

[abbaddon223]

I think that property value only applies to text, memo and hyperlink fields....

 

[pr2-eugin]

OOPS moment !!

Sorry about that.. If you try just Is Null do you get the result that has empty fields?

 

[abbaddon223]

Hi, no it doesn't give a result when I do Is Null

I also tried Like ""

Thanks

 

[pr2-eugin]

So are you sure there are some Null data in the table? Is it possible for you to upload a stripped down version of your DB?

 

[abbaddon223]

Sorry - to clarify - if I do an Is Null as part of a larger search (IE the other like or is null queries) then I get 0 results

Just did an Is Null on that field as a standalone query and it returned the null values....