Nz problem

I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so far
so good,... however, if the "Project number" in table my table is blank the
then the nz does not appear to work and no record will display. My records
will always have a street number and name but not a project number. My code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And [Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*" And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

Not sure if this is what's affecting your situation, but "No" is probably a
reserved word in Access (think "yes"/"no"). What happens if you change the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*" And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably a
reserved word in Access (think "yes"/"no"). What happens if you change the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*" And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

Try changing the SQL statement to the following.

SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

John now works just as expected. Thank you for your help. You would use VBA
to build the SQL statement on the fly and control how exactly does this
works? You are probably right because in opening the form when the connection
is slow it takes forever ... and I guess because the query try to run on
opening the form already... where I only need to retrieve records once the
criteris has been set. Wold you mind to guide me in this process since it is
new to me?

the query that way.

"John Spencer" wrote:

Try changing the SQL statement to the following.

SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

In a button click event, you would need code something like the following.

Private Sub btnRunSQL_Click()
Dim strSQL as String
Dim strWhere as String

StrSQL = "SELECT * FROM tblData"

IF Not IsNull([Forms]![FrmSearchproj]![TxStreet]) THEN
strWHERE = strWhere & " AND Street = """ & _
[Forms]![FrmSearchproj]![TxStreet] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxProj]) THEN
strWHERE = strWhere & " AND ProjectNo = """ & _
[Forms]![FrmSearchproj]![TxProj] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxNo]) THEN
strWHERE = strWhere & " AND [No]= """ & _
[Forms]![FrmSearchproj]![TxNo] & """"
End if

StrSQL = StrSQL & " WHERE " & MID(5,strWhere)
Me.RecordSource = strSQL

End Sub

Also, change the Source query for the form to something that will return no
records
SELECT * FROM tblData WHERE 1 = 2
or
SELECT * FROM tblData WHERE False
Or
SELECT * FROM tblData WHERE txNo = "AZZISSZZZ"

Allen Browne may have an example on his site that you can look at.
AllenBrowne.com

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
John now works just as expected. Thank you for your help. You would use VBA
to build the SQL statement on the fly and control how exactly does this
works? You are probably right because in opening the form when the connection
is slow it takes forever ... and I guess because the query try to run on
opening the form already... where I only need to retrieve records once the
criteris has been set. Wold you mind to guide me in this process since it is
new to me?

the query that way.

"John Spencer" wrote:

Try changing the SQL statement to the following.

SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

John, my Main (unbounded) form has the 3 search controls and the subform,
witch is based on my original query I had problem with. What I don't
understand is… how to populate the subform once I remove the original query
using only the VB SQL statement. I am required to remove the subform and base
the main form on the table directly? The query normally works as data source
and my filter for may report or forms. I am not sure how to handle this with
a SQL statement in VB without a query. Thanks for the BIG help!!



"John Spencer" wrote:

In a button click event, you would need code something like the following.

Private Sub btnRunSQL_Click()
Dim strSQL as String
Dim strWhere as String

StrSQL = "SELECT * FROM tblData"

IF Not IsNull([Forms]![FrmSearchproj]![TxStreet]) THEN
strWHERE = strWhere & " AND Street = """ & _
[Forms]![FrmSearchproj]![TxStreet] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxProj]) THEN
strWHERE = strWhere & " AND ProjectNo = """ & _
[Forms]![FrmSearchproj]![TxProj] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxNo]) THEN
strWHERE = strWhere & " AND [No]= """ & _
[Forms]![FrmSearchproj]![TxNo] & """"
End if

StrSQL = StrSQL & " WHERE " & MID(5,strWhere)
Me.RecordSource = strSQL

End Sub

Also, change the Source query for the form to something that will return no
records
SELECT * FROM tblData WHERE 1 = 2
or
SELECT * FROM tblData WHERE False
Or
SELECT * FROM tblData WHERE txNo = "AZZISSZZZ"

Allen Browne may have an example on his site that you can look at.
AllenBrowne.com

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
John now works just as expected. Thank you for your help. You would use VBA
to build the SQL statement on the fly and control how exactly does this
works? You are probably right because in opening the form when the connection
is slow it takes forever ... and I guess because the query try to run on
opening the form already... where I only need to retrieve records once the
criteris has been set. Wold you mind to guide me in this process since it is
new to me?

the query that way.

"John Spencer" wrote:

Try changing the SQL statement to the following.

SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!

Sorry, did not realize you were using a sub-form. Set the record source of
the subform

Me.[Name of Subform Control].Form.RecordSource = strSQL

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
John, my Main (unbounded) form has the 3 search controls and the subform,
witch is based on my original query I had problem with. What I don't
understand is… how to populate the subform once I remove the original query
using only the VB SQL statement. I am required to remove the subform and base
the main form on the table directly? The query normally works as data source
and my filter for may report or forms. I am not sure how to handle this with
a SQL statement in VB without a query. Thanks for the BIG help!!



"John Spencer" wrote:

In a button click event, you would need code something like the following.

Private Sub btnRunSQL_Click()
Dim strSQL as String
Dim strWhere as String

StrSQL = "SELECT * FROM tblData"

IF Not IsNull([Forms]![FrmSearchproj]![TxStreet]) THEN
strWHERE = strWhere & " AND Street = """ & _
[Forms]![FrmSearchproj]![TxStreet] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxProj]) THEN
strWHERE = strWhere & " AND ProjectNo = """ & _
[Forms]![FrmSearchproj]![TxProj] & """"
End if

IF Not IsNull([Forms]![FrmSearchproj]![TxNo]) THEN
strWHERE = strWhere & " AND [No]= """ & _
[Forms]![FrmSearchproj]![TxNo] & """"
End if

StrSQL = StrSQL & " WHERE " & MID(5,strWhere)
Me.RecordSource = strSQL

End Sub

Also, change the Source query for the form to something that will return no
records
SELECT * FROM tblData WHERE 1 = 2
or
SELECT * FROM tblData WHERE False
Or
SELECT * FROM tblData WHERE txNo = "AZZISSZZZ"

Allen Browne may have an example on his site that you can look at.
AllenBrowne.com

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
John now works just as expected. Thank you for your help. You would use VBA
to build the SQL statement on the fly and control how exactly does this
works? You are probably right because in opening the form when the connection
is slow it takes forever ... and I guess because the query try to run on
opening the form already... where I only need to retrieve records once the
criteris has been set. Wold you mind to guide me in this process since it is
new to me?

the query that way.
"John Spencer" wrote:

Try changing the SQL statement to the following.

SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which a

Streen number
Street Name
Project number

All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.


Thank you for your help on this


"Jeff Boyce" wrote:

Are you checking for "null" AND checking for "zero-length string" ("")?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.

"Jeff Boyce" wrote:

Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Silvio" wrote in message
...
I have 3 fields in my search form

1. No
2. Street
3. Project

If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?


SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))False));

Thank you folks!