Query By Form Null Value

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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
 
Bill said:
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.
Click to expand...


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))
 
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 said:
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.
Click to expand...


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.
 
Back
Top