Need help on Between query using Forms (1 Viewer)

[Meehphill]

Hi, I'm new to Access,

I am currently working on building a form to with combo boxes, etc, for users to enter or pull down a number.

in my query, I'm not sure what to do, when a user does not or leave blank... here is what it looks like ...

Lets say I'm querying by State and net income..

if state is pick "CA"
and income is left out at zero, i want the results to still show.

Bewteen Nz([StartNo],0) And Nz([EndNumber],100000000)

keeps saying error !

can u guys help..thanks

 

[billmeye]

It's a bit confusing what you are actually doing. If you have a query and a field value may be null but you want a value by default, using Nz([StartNo],0) is good syntax. If you are setting a Criteria you should use the syntax Between 0 And 100000000 would be correct. I don't understand what you are actually attempting to perform with your statement Bewteen Nz([StartNo],0) And Nz([EndNumber],100000000). Is this a criteria for some calculated field? Let me know some more info and maybe I can help.

 

[Meehphill]

Thanks Bill,

I'm trying to have a user select a STARTNO and a ENDNUMBER, along with other criteria, but what if no startNo or ENDNUMBER is selected...I don't want it returning "0" results,

I want this query to return results even tho nothing is entered in the form.

Between [Forms]![F Searchall]![StartNo] And [Forms]![F Searchall]![EndNumber] And Is Null

it's not working for me

 

[billmeye]

You are on the right track using Nz([StartNo],0). Please post your query so I can better see what is going on.

 

[John Big Booty]

Try;

Between Nz([Forms]![F Searchall]![StartNo], 0) And Nz([Forms]![F Searchall]![EndNumber], 100000000)

 

[Meehphill]

Thanks all, problem solved.