Urgent Help! Problem on passing field value (Null) to query

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi folks,

I need an urgent help for a problem on a form.

I have a table called tblTest that has the following value:

ID Field1
1 Tim
2
3 Tom

And I have a query called qryTest:

SELECT tblTest.ID, tblTest.Field1
FROM tblTest
WHERE (((tblTest.Field1)=IIf([forms]![frmTest]![text0]="",
([tblTest].[Field1]) Is Null,[forms]![frmTest]![text0])));

If the user input a value in [forms]![frmTest]![text0],
then the query worked fine. If the user didn’t input any
value, the query returned nothing which was not I
expected. I expected the query would return ID=2 and
Field1= .

Could anyone help me to solve the problem?

Thanks in advance.

Tim.
 
ID Field1
1 Tim
2
3 Tom
Click to expand...

Is Field1 allowed to be Null (in the table definition)? In that case,
you cannot find the second record except by an explicit test for Null in
Field1.
And I have a query called qryTest:

SELECT tblTest.ID, tblTest.Field1
FROM tblTest
WHERE (((tblTest.Field1)=IIf([forms]![frmTest]![text0]="",
([tblTest].[Field1]) Is Null,[forms]![frmTest]![text0])));
Click to expand...

I'd rephrase this as follows (air sql code):
WHERE (isnull(forms!frmTest!text0) AND Field1 IS NULL) OR
(Field1=forms!frmTest!text0)

Bear in mind that Null means "I don't know" which explains why a test
like Field1=Null fails.
Does it explain that?
Yes it does.

Consider this example. What color is my car? You don't know. What color
is my house? You don't know. Are these values the same? You don't know!
 
Oh, and, consider leaving out the expression 'urgent' from your
subsequent post subjects. We'll help you anyway.
 
Bas,

Thanks for your help.

Tim.
-----Original Message-----
ID Field1
1 Tim
2
3 Tom
Click to expand...

Is Field1 allowed to be Null (in the table definition)? In that case,
you cannot find the second record except by an explicit test for Null in
Field1.
And I have a query called qryTest:

SELECT tblTest.ID, tblTest.Field1
FROM tblTest
WHERE (((tblTest.Field1)=IIf([forms]![frmTest]![text0] ="",
([tblTest].[Field1]) Is Null,[forms]![frmTest]!
Click to expand...
[text0])));

I'd rephrase this as follows (air sql code):
WHERE (isnull(forms!frmTest!text0) AND Field1 IS NULL) OR
(Field1=forms!frmTest!text0)

Bear in mind that Null means "I don't know" which explains why a test
like Field1=Null fails.
Does it explain that?
Yes it does.

Consider this example. What color is my car? You don't know. What color
is my house? You don't know. Are these values the same? You don't know!
Click to expand...
 
Back
Top