Query By Form Null Value

Bill · External Since: Mar 06, 2006 Posts: 91

Hi,
I have a Query By Form with several User Input Boxes. One is for a number,
that I enter into the box. What I would like to do is, when I leave the input
box blank for that column, I want to skip that input box and go to the next
one. What can I to do?
I am using the following Critera: [Forms]![frmQBF]![txtFirstNumber]
I have tried using Blank=All, but it just give an error. Any help to point
me in the right direction will be greatly appreciated.
Thanks,
Bill

Dirk Goldgar · External Since: Oct 05, 2006 Posts: 65

"Bill" <Bill.RemoveThis@discussions.microsoft.com> wrote in message
news:80C76CF4-7523-479A-A95A-88519F4B2F78@microsoft.com...
> Hi,
> I have a Query By Form with several User Input Boxes. One is for a number,
> that I enter into the box. What I would like to do is, when I leave the
> input
> box blank for that column, I want to skip that input box and go to the
> next
> one. What can I to do?
> I am using the following Critera: [Forms]![frmQBF]![txtFirstNumber]
> I have tried using Blank=All, but it just give an error. Any help to point
> me in the right direction will be greatly appreciated.

If I understand you, you want to be able to leave one of your text boxes
blank and have it ignored in the query criteria. In principle, you do this
by having your query criteria accept the alternative that the form control
is Null. Like this SQL:

SELECT <desired fields> FROM YourTable
WHERE
((SomeField = Forms!frmQBF!txtFirstNumber)
OR (Forms!frmQBF!txtFirstNumber Is Null))

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Bill · External Since: Mar 06, 2006 Posts: 91

Hi,
Let me try to explain more so you can understand what I am attempting. I
have a Query that works fine. Except I want to use that Query Properties in
my Query By Form I designed. I will list the properties of both Queries to
see how I am using them.
MY QUERY:
Field: dwFirstNumber
Table: QBF_tblNumbers
Sort:
Show: Is Checked
Criteria:
Or:
In the next column I have this:
Field: InStr([dwFirstNumber,Blank=All],[dwFirstNumber])
Table:
Sort:
Show: Is Unchecked
Criteria: >0Or Is Null
Or:

Now my Query By Form has these Properties:
Field: dwFirstNumber
Table: QBF_tblNumbers
Sort:
Show: Is Checked
Criteria: [Forms]![frmQBF]![txtFirstNumber]
Or:

That is it. Now what do I need to correct to make it work in my QBF.
Thanks for any help. I have tried a lot of things but keep trying. Hope
someone can help.
Thanks again,
Bill

"Bill" wrote:

> Hi,
> I have a Query By Form with several User Input Boxes. One is for a number,
> that I enter into the box. What I would like to do is, when I leave the input
> box blank for that column, I want to skip that input box and go to the next
> one. What can I to do?
> I am using the following Critera: [Forms]![frmQBF]![txtFirstNumber]
> I have tried using Blank=All, but it just give an error. Any help to point
> me in the right direction will be greatly appreciated.
> Thanks,
> Bill
>

Dirk Goldgar · External Since: Oct 05, 2006 Posts: 65

"Bill" <Bill.RemoveThis@discussions.microsoft.com> wrote in message
news:A9BBC5C3-A331-4F26-97ED-21B4D9B3EB8F@microsoft.com...
> Hi,
> Let me try to explain more so you can understand what I am attempting. I
> have a Query that works fine. Except I want to use that Query Properties
> in
> my Query By Form I designed. I will list the properties of both Queries to
> see how I am using them.
> MY QUERY:
> Field: dwFirstNumber
> Table: QBF_tblNumbers
> Sort:
> Show: Is Checked
> Criteria:
> Or:
> In the next column I have this:
> Field: InStr([dwFirstNumber,Blank=All],[dwFirstNumber])
> Table:
> Sort:
> Show: Is Unchecked
> Criteria: >0Or Is Null
> Or:
>
> Now my Query By Form has these Properties:
> Field: dwFirstNumber
> Table: QBF_tblNumbers
> Sort:
> Show: Is Checked
> Criteria: [Forms]![frmQBF]![txtFirstNumber]
> Or:
>
> That is it. Now what do I need to correct to make it work in my QBF.
> Thanks for any help. I have tried a lot of things but keep trying. Hope
> someone can help.

If dwFirstNumber is the only field that has any criterion applied to it,
then you could change the criterion to:

[Forms]![frmQBF]![txtFirstNumber] Or [Forms]![frmQBF]![txtFirstNumber]
Is Null

.... and I think that would work. But if you have other fields with criteria
applied, it can get complicated.

This sort of thing is a lot easier to get right in SQL view. If the above
simple change doesn't work for you, post the SQL view of your QBF query, and
I'll reply with a modified version that you can paste back into the SQL view
of the query.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)